Mysql – Backing up a MySQL database – while it is still in use

backupMySQL

I currently backup my MySQL database using mysqldump, for example:

mysqldump --user USERX --password=PWDX DBNAMEX > dbbackup__`date '+%m-%d-%Y'`.sql

When I perform the backup (initiated via a cron job) I set a .htaccess rule on my site to redirect any requests to a static "down for backup, back shortly" page. Once the backup completes I remove the rule and the site reopens.

All well and good, it has been like this for years and as you can imagine this works perfectly well and I haven't had any issues.

However…

I would like to not have to close the site during the backup, so the question is can I do this with mysqldump? Is the tool capable of handling conditions where data changes after the backup initiates? If it does live backups can I guarantee that I won't get a corrupt backup? Is there a better way of doing a live backup?

Best Answer

  • If you use InnoDB, you can concurrently read and write. You probably want to use InnoDB if you aren't already.
  • Using InnoDB allows you to run a backup with the --single-transaction flag. This will keep the database in a consistent state and dump live while allowing other transactions to process.
  • If you have a sufficiently large database (it sounds like you might get by without this), then you need something else. There is an official for-pay InnoDB hot backup solution, but I shy away from such an idea. If you're in the spot of needing that, look at http://www.percona.com/docs/wiki/percona-xtrabackup:start
Related Topic