codingChris

WPMU Database Backup

While in the process of backing up a WPMU installation for upgrade testing I ran into a few problems with my database backups.  Seems that using the MySQL Administrator tool for backups hasn’t been working as well as I had hoped.  Restoring these backups would freeze halfway through.  So I figured it was time to resort to the old trusty mysqldump…

Unfortunately, running and testing backups during work hours is not so good.  The server handles the load fine, but locking the tables isn’t nice to the users.  Luckily for the testing db I don’t need full integrity so running the db backup without locking the tables is as simple as the following:

mysqldump -u username –password=supersecretpass –skip-lock-tables db_name > output.sql

However, this still doesn’t address the original issue of the MySQL Administrator program not giving me good dumps.  So we’re back to mysqldump:

mysqldump -u username –password=supersecretpass –opt db_name > output.sql

Now we’re in business…except for the following error:

Got error: 29: File ‘./wpmu/wp_1028_comments.MYD’ not found (Errcode: 24) when using LOCK TABLES

Well, it looked good anyway.  After doing some Googling I ran across this article detailing a couple of ways to fix it.  In my case, this error was only being seen during the dump of the database.  Simply adding –single-transaction to the command fixed the problem.

mysqldump -u username –password=supersecretpass –opt –single-transaction db_name > output.sql

If you do see this error during other times, try increasing the open_files_limit in the MySQL config file.  The Racker Hacker article goes more in depth if you want to check it out.