Backing up a MySQL Database – What You Need To Know

mysql(Ping! Zine Issue 63) – Ask any Webmaster, and they will tell you that one of their worst nightmares and biggest fears is the loss of their most important information. All it takes is one malicious or unfortunate incident, such as the infiltration of a hacker or the crashing of a server, to wipe out every piece of vital information that is stored in your MySQL database. It’s always been said that you should back up any essential data, and for Webmasters, that piece of advice is especially true. Fortunately, there are many ways that a Webmaster can back up their MySQL database, thus making it very possible to prevent the ultimate nightmare scenario.

While many Webmasters consider the fear of a negative incident, such as a hacker or server crash, to be the primary, and most obvious reason for performing regular backups of their MySQL database, it is far more common for a backup of the database to be used during a transfer from one web host to another, or from one database server to another. With your MySQL database completely backed up, you’ll be able to seamlessly perform these transitions in a stress-free manner.

When it comes to backing up a MySQL database, you have a multitude of options depending on your skill set, experience level, and shell or telnet accessibility. Two of the most common ways to perform a MySQL database backup are through the mysqldump command, and by using the PHPMyAdmin interface to perform the action. There are also other alternatives to be discussed and considered. Regardless of the method that you choose, you’ll replace any feelings of uncertainty with serenity knowing that your vital data has been backed up in the case of an unfortunate event or a hosting change.

Backing up your MySQL database with mysqldump
In order to use the mysqldump command, you’ll need shell or telnet access to your database server. The command will serve to create a backup by routing your database contents to any location that you specify. The beauty of the mysqldump command is that it offers a variety of different ways to back up your vital data. For example, you’ll be able to back up your entire database, strictly the database structure, or if preferred, only specified tables within the database. Although it is not as simple as MyPHPAdmin, the command mysqldump gives Webmasters the flexibility desired to meet every possible individual need by providing for these backup options.

To perform a simple database backup using the mysqldump command, you’ll use a basic syntax that requires the database username, password, and name, as well as the file that will receive the backup data. As an example, if your database username was admin, your password was live845, the name of your database was Subscribers, and the file receiving the information was subsbackup.sql, you could use the following command to back up the entire database:

mysqldump –u admin –p live845 Subscribers > subsbackup.sql

If backing up the entire database seems too cumbersome, and you feel it would be in your best interest to, instead, make a backup of only specific tables, then you can modify the above code to meet your needs. For instance, the Subscribers database may have a plethora of information, though only specific elements of that data are of any importance. In such a case, it would be a waste of resources to make a backup of every piece of information. Rather, it would make much more sense to back up the most pertinent information through a modification of the aforementioned code. As an example, within the Subscribers database, you would like to back up the tables named subscribers_names and subscribers_info, you would be able to do that by using the following syntax:

mysqldump –add-drop-table –u admin –p live845 Subscribers subscribers_names subscribers_info > subsbackup.sql

Lastly, in such cases when you don’t care about the actual data within a database, but rather the structures, you can modify the syntax by use of a special parameter that specifies that no data be passed on to the backup file. In order to perform such a back up, you would use this syntax:

mysqldump –no-data –databases Subscribers > subsbackup.sql

Using PHPMyAdmin to back up your MySQL database
Whether you lack the access to telnet, or you get a headache from simply looking at mysqldump syntax, then you will most likely end up using PHPMyAdmin to handle all of your MySQL database backup needs. PHPMyAdmin is a web interface designed to make controlling MySQL databases a simplified task. In fact, with PHPMyAdmin, the process of backing up your MySQL database literally takes only a few clicks of your mouse. All you’ll have to do is go to the export tab, and then follow the basic directions to download the SQL queries to represent your database. To put it bluntly, backing up your MySQL database has never been easier.

Alternative Methods
If neither mysqldump nor PHPMyAdmin seem to work for you, then you need not worry, as there are still alternative means of backing up your precious data. These options are generally offered by third party sources or software, so as long as you trust others with the backup process, they may work wonderfully for you. Amazon S3 has become increasingly popular to back up MySQL databases. In order to more efficiently back up your data to Amazon S3, various scripts have been developed and made available. Another alternative to make your life easier is to download a script that will automatically perform the mysqldump command for you. Thus, you don’t have to worry about manual inputting the necessary syntax for backing up your database.

Regardless of the means in which you do it, it is highly recommended that you perform a backup of your MySQL database. It may seem like a tedious task at first, but in the event of either a crisis, or a need for change, you will feel relieved knowing that you still have all of your vital data. Whether you choose to use MyPHPAdmin, or to either input the mysqldump syntax yourself, or have a script do it for you, it is important to know that you have various methods at your disposal to get the job done.