Quick Tip For Creating and Testing Database Backups

It’s a gut wrenching feeling to find you’ve deleted a database and your backup is mashed-up because your server timed-out during the export or that your export completed properly but it’s too big to import into your database because your server’s max file upload size is too small.

Whenever you create a MySQL database backup make sure you grab the database in chunks that are the right size to import and make sure the total size of the exported database is roughly the same size as the database on your server.

It’s an obvious tip but we all get caught short every once in a while and usually by WordPress automatic database backup plugins.

This is the method I use to successfully back up a database. Let’s assume we all use PhpMyAdmin:

1. Log into your website.
2. Clear data from plugins that’s no longer required e.g clear redirection logs, ping logs and the logs of stat gathering plugins.
3. Log into PhpMyAdmin.
4. Click the name of the table that needs to be exported.
5. Click the Structure tab.
6. Scroll to the bottom of the table and click Select All.
7. Use the drop-down box that reads with selected to select Check table.
8. Repeat steps 5, 6 and 7 twice but these times use the drop-down box to select Repair table then Optimize table.
9. Click the Import tab.
10. Look at the file maximum upload size written next to the Browse button beside the Browse your computer field.
11. Click the Structure tab.
12. Browse to the bottom of the table and read the table size. If it’s larger than your max upload size you might need to download your database in segments.
13. Click the Export tab.
14. If you use the latest version of PhpMyAdmin you will see a section titled Export Method. Select Custom.
15. Select all table rows.
16. Find where it says Compression, and select one of the available options e.g Zipped.
17. Find where it says Data Dump Options and select Replace.
18. Click Go.
19. When the database has downloaded, unzip it and confirm the file size is similar to the size of the database as noted in step 12. If it’s greatly smaller, you will need repeat steps 13 to 18 but instead of choosing to export all table rows, choose to export a few of the tables at a time. Use the table structure view (under the Structure tab) to check table row sizes and export the larger rows separately.
20. Lastly, check the downloaded file size/s is/are smaller than the maximum upload size discovered in steps 9 and and 10.

You could test the exported database by creating a new database on your server and importing the exported tables into it. Then change your application’s configuration file to point it to the new database. for example, for WordPress you would edit wp-config.php to change the values of DB_USER, DB_NAME and DB_PASSWORD. If the application works with the new database then the database has probably exported properly.

If you have a local development server (LAMP, MAMP, WAMP,  XAMP or Turnkey etc…), you can “fix” a database file that’s too large to import by using the local PhpMyAdmin to import the database then re-export it in chunks small enough to upload to your remote database. Other tools exist for this but I find this convenient.

Sharing is caring!