Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 2 down vote accepted
  • 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
share|improve this answer
    
How would you define "sufficiently large database", 1gb? 100gb, 1000tb? My current DB is about 1.5gb –  MrEyes Jan 20 '11 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 '11 at 18:33

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

share|improve this answer
    
--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 '11 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 '11 at 1:08

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.

share|improve this answer

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

share|improve this answer
    
Looking at the docs for mysqlhotcopy it uses table locks, so in theory a user could be presented with a site error as table their request is attempting to insert into is locked (dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html) –  MrEyes Jan 20 '11 at 15:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.