Backing Up a MySQL Database (do NOT use phpMyAdmin)

Posted by Hans de Ruiter

I have always regularly backed up this website, including its database. However, I have discovered that the database backups have been either corrupt or incomplete. This came to my attention when I looked back at the database backups and noticed that they were all less than a few MiB in size (compressed) and the size remained constant. The database itself, however, is approximately 27 MiB. Even taking the compression into account, these files were too small; a test uncompressed  backup (i.e., raw *.sql file) came to 5 MiB. Clearly, something was wrong.

I had been using phpMyAdmin, which the web-host provided. It turns out that phpMyAdmin has problems with large databases (see the "MySQL Backup without phpMyAdmin" section on this page). What I find disturbing is that phpMyAdmin will not warn you if a backup is incomplete; a failed backup looks just like a successful one. This is incredibly poor programing. For any software developers reading this: any critical failure in a software program MUST be reported to the user; silently failing and giving the impression of success is completely unacceptable. PhpMyAdmin has clearly failed in this regard. Moreover, given the above link, this is a known issue.

Given the fact that phpMyAdmin cannot backup large databases and will not even warn the user of a failure, it is clear that this is not a reliable method of maintaining backups. This is particularly disconcerting for me since this website's host will not guarantee that backups will be available, and also charges a premium for restoring a website from backups. Fortunately, there are other tools available that will do the task properly. Some are listed here. Since I mostly update this website using a Windows machine, I opted for MySQL Administrator, which is part of the MySQL GUI Tools that are available on the MySQL website. This provides an easy to use GUI for performing backups and other tasks. Linux users may opt to set up mysqldump in cron, so that it automatically backs up the database periodically.

I am fortunate that I discovered this issue before needing to restore from a backup. Silverstripe stores the page contents in a database, so the loss of the database would be rather catastrophic. My advice to others would be to use tools other than phpMyAdmin to perform database backups. 

EDIT: (2008/01/08) I have since found a backup utility called SiteVault that backs up both the main site, and the database. See this page for details.

Comments (0) | 3 December 2008