If you’re like most people, you probably have a MySQL database sitting on your computer. You can access it using any web browser or program, and even if you’re not a mysql user, you can still use it to store data. However, if something goes wrong and your database is lost or corrupted, you’ll be in trouble. There are a few things you can do to protect your database if it’s lost or corrupted:

  1. Back up your entire database. This will include all of your tables and all of the data in them. This is important because if something goes wrong and your table is lost, there’s no way to recover it.
  2. Backup only the tables that are important to you. If you don’t backup any other tables, someone else may end up with all of the data in your database if something goes wrong.
  3. Use a secure storage method for your MySQL database. If someone gets their hands on your MySQL database while it’s encrypted with a strong password, they may be able to access it without having to enter any information.

At first, running mysqldump only took a few seconds, so it wasn’t a big deal that everything locked up, but over time our backups got so big the entire site would go down for half an hour if we ran a backup. Even with lots of caching that always meant that somebody was getting an error page every single day. The problem is that mysqldump locks the database tables before it exports so that new things aren’t being inserted while you’re in the middle of exporting.

The solution is to use the –single-transaction argument, which will give you a consistent backup without locking up everything. What actually happen is that mysqldump will start a new SQL transaction, flushing all pending writes, and then complete the backup as part of a transaction that doesn’t block other updates from happening.

Note: the only caveat is that your database tables should be using InnoDB rather than MyISAM. Since that’s been the default for a while, you should probably be fine. 

So now that we’ve got that figured out, just add the argument to your normal backup routine, like this:

And now your backups will be much more reliable.