Back Up A Large MySQL Database

by Curtiss   |   last updated April 28, 2009   |   PHP Tutorials · Software ·

Anyone that’s dealt with a Web application using a MySQL backend has probably dealt with incomplete backups. Those of you that use phpBB have almost certainly encountered this issue. Unfortunately, within phpBB and quite a few other PHP/MySQL-based applications, the backup scripts fail miserably with any reasonably large amount of data. Most of them fail because of either PHP time outs or file size limits. This article will show you two different ways to successfully back up that data. At least one of these two methods should be available in almost every remote hosting package that uses MySQL and PHP.

phpMyAdmin

Your first option is to use phpMyAdmin, which is usually the tool Web hosts provide to manage MySQL databases. This process can be extremely slow and tedious, but it is almost universally available.

Once you login to phpMyAdmin, make sure that the database you want to back up is identified in the left navigation menu (in the screen shot, the database is called “blog_wp_090109”). If you see a list of your databases, instead, click on the name of the database you want to back up.

Next, click the “Export” tab. By default, phpMyAdmin generally has all of the tables in the database selected in the select list. In order to avoid a timeout, you want to click on a single table. If you know that you have a few smaller tables, you can select all of those at once. Then, click the “Go” button to export the SQL information.

If you come to a table that causes a timeout, click on the table name in the left navigation menu and then click the “Export” tab. There, you will have the option to dump a specific number of rows at a time. If you click the “Browse” button, you will see a yellow box near the top of the screen that tells you how many total rows are included in the table (it will say something like “Showing rows 0-29 (983 total, Query took 0.0452 seconds)”). Now, go back to the “Export” screen. In the area where it says “Dump _____ row(s) starting at record #___”, enter a round number in the first text box. For instance, if there are 1,000 rows in your table, start by trying to dump half of them at a time. In that case, you would enter “500” in the first text box and “0” in the second. If that works successfully, go back to that screen and enter “500” in the first text box again, then “500” in the second.

When you export using phpMyAdmin, it will default to simply printing the information in a textarea for you to copy and paste. That won’t do you much good if you are actually trying to create a back up. Instead, you want to tell phpMyAdmin to “Save as File.” That will prompt you to save an SQL file when it is done dumping the information. Save each of these files in a safe place, as you may need to use them to restore your database in the future. When you need to import that information back into a database, click the “Import” tab and follow the instructions there.

If you are just exporting the information so that you can import it into a new database, it’s generally acceptable just to have phpMyAdmin show the information in the textarea. You can then copy that information and paste it into the “SQL” tab of the phpMyAdmin interface for your new database.

MySQLDump

To use MySQLDump, you will need SSH access to your server. Quite a few hosts offer this nowadays. If you are on Windows, you can use PuTTY to SSH into your server. If you are using Linux (or Mac, I believe), you can simply open a terminal and type “ssh username@hostname” to SSH into your server. If you don’t know whether or not your host offers SSH, give it a try. Use the same login information you use to FTP into your server, but make sure that you are logging into port 22 instead of port 21. If it works, then you have SSH.

When you SSH into your server the first time, you will be asked to accept the security certificate. Go ahead and do so.

Next, you want to see if mysqldump is installed on your server. You can do so by simply typing “mysqldump” at the command line. If you then see information about using mysqldump, it is installed and usable.

The next step is to actually dump the information. To do so, type the following command:

mysqldump -usqluser -p --opt databasename >/home/username/databasename.backup.sql

Replace “sqluser” (making sure to leave the -u in front of it) with the username you use to access the database. Replace “databasename” with the actual name of your database. Finally, replace “/home/username/databasename.backup.sql” with the full path to the location on the server in which you want to create the new back up file.

When you hit enter, you will be prompted for your password. Type in MySQL password used to access the database and press Enter again. Then, depending on how large your database is, it can take anywhere from a few seconds to a few minutes (or even a few hours, if you have an extremely large database – one of my databases dumps to a file approximately one gigabyte, and it takes about fifteen minutes to complete) to finish. However, once it returns to the command line again, you will have a complete backup of your database stored in a plain text file on your server. To download it, simply login to your FTP account, locate the file and copy it to your computer.

If you need to restore your database, you can do so using the “Import” tab within phpMyAdmin or you can use the MySQL commandline. To use the MySQL commandline, simply SSH to your server again and type the following command:

mysql -usqluser -p databasename </home/username/databasename.backup.sql

Again, replace “sqluser” with the username you use to access your MySQL database, replace “databasename” with the name of your database and replace “/home/username/databasename.backup.sql” with the full path to the back up file on your server. That will then insert all of the information back into your database. It will completely overwrite the old database, so be aware of that when restoring. It will not attempt to merge the information.

Good luck. I hope that helps some people.