7

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?

Improve this question

4 Answers 4

Reset to default
4
  • 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
Improve this answer
2
  • How would you define "sufficiently large database", 1gb? 100gb, 1000tb? My current DB is about 1.5gb
    – MrEyes
    Jan 20, 2011 at 15:55
  • I would define "sufficiently large" as a hunch. If your database backs up fast enough with this method and causes no issues for you, go with it. "Fast enough" is up to you. So far, it has been doable for you to fully take the site offline for backups, so I imagine this will be a rather quick process. I would change over if it was taking the better part of the day, causing a load which the database can't keep up with (only likely with deletions), or if I were really bored.
    – Jeff Ferland
    Jan 20, 2011 at 18:33
1

Try having a look at mysqlhotcopy- depending to your database size/tables etc, this might provide you with the answer.

Improve this answer
1
1

If your storage engine is InnoDB, you can get a consistent mysqldump of a live database by using the --single-transaction flag (as long as you don't change your table structures during the backup). But it doesn't work with MyISAM.

Improve this answer
0

Use --single-transaction option for InnoDB databases or --lock-tables for MyISAM.

Improve this answer
2
  • 1
    --lock-tables is a very bad idea. First, it only locks the tables one at a time, so while each table will be consistent, they won't be consistent with each other. Second, it's likely to cause very long delays on the website as the application has to wait for the locks to be released. If InnoDB is not an option, a better bet would be to use an LVM and take the database down for just long enough to make a snapshot, then mysqldump the snapshot later.
    – Mike Scott
    Jan 20, 2011 at 18:22
  • mysqldump(1) says that --lock-tables locks all tables for each database to be dumped. Delays - yes, but shutting down whole website is also a delay :) Snapshots really is a better option when dumping tables to SQL file taking too much time, but if it takes only few seconds - it's overkill.
    – gelraen
    Jan 21, 2011 at 1:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .