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 do daily backups of my mysql databases with mysqldump+scheduled tasks

but, as i can read on this answer, mysqldump does not guarantee a consistent dataset... what's the meaning? That means that if someone will access the website while i am backing up, and his actions will edit two tables, and one table is has already parsed by mysqldump, resulting in an "incomplete" backup that can lead to problems? If mysqldump is not good, what is the best way to backup?

share|improve this question

4 Answers 4

up vote 2 down vote accepted

Your scenario of what might make an inconsistent backup is exactly right.

You can use mysqldump to take a consistent backup by adding the flag --lock-all-tables. The caveat is, as the name suggests, that all tables will be locked against writes until the backup is completed. Which depending on the size of your database could be a long time and cause great disruption.

There are some alternatives. Some of which are commercial. I'm going assume you are using MyISAM, which is generally more tricky to backup consistently than InnoDB.

One solution is to place MySQL's data onto storage that facilitates quicker backups by another method. Such as a SAN or LVM2 block device, which support snapshots. You will still have to place MySQL into a locked state but because snapshots take very little time to complete it the disruptive effect is negligible. You can then start another MySQL daemon against the snapshot of data should wish to take a consistent mysqldump and export it elsewhere at leisure.

I use a similar method myself against iSCSI backed storage.

share|improve this answer
    
well, i do the backup at a time that, according to the server logs, is less active. But since my website is a multilingual one, i cannot be 100% sure of no activity. If i lock all the tables, for the 5 minutes required for the backup, every write access to the database will throw an error 500 / database unavailable? Or "just lost"? (the user won't be noticed that the inputted data is not retained/saved) –  Magnetic_dud Jun 5 '09 at 16:39
1  
Most likely requests will just block. They may timeout and return a 500 error unless you handle it yourself. –  David Pashley Jun 5 '09 at 16:50
    
i put --single-transaction in the batch file it looks it is working good –  Magnetic_dud Jun 8 '09 at 8:41
    
Unless you are using a transactional storage engine for your tables, such as InnoDB or BDB, then --single-transaction won't have the desired effect. –  Dan Carley Jun 8 '09 at 9:31

Imagine two tables. While you're backing up the first, the second gets changed.The backup will include that change. You don't have a snapshot. This is a problem if say you deleted a foreign key.

The solution is to have a slave database replicated to, which when you do the backup, you stop replication, do your backup and then start replication again. This way, you have a snapshot in time of your database.

share|improve this answer
    
The slave solution is good also if you have the hardware available and can afford to pause replication. Just make sure it's in sync before you do so :) –  Dan Carley Jun 5 '09 at 16:27
    
This is why you have nagios checks on your replication status :) –  David Pashley Jun 5 '09 at 16:50

If you are using InnoDB tables, there is a commercial tool called "hot backup" that creates a consistent backup while the server is up. I won't link it as it's a commercial site, but a google search should bring it up if you're interested.

With other table formats, you can obtain a read lock on the tables, then copy the underlying data files, but this will block write operations to the tables, so you have to do it when applications are not relying on writes or with applications that have been written to handle a failed write gracefully.

More info in the MySQL docs:

http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html

share|improve this answer

mysqldump does normally create consistent snapshots (unless you tell it not to). It achieves this by locking the whole server using a global read lock, which is not desirable in most cases.

If you use InnoDB tables exclusively, you can use a transactional dump which is also consistent but won't lock anything out (for long).

share|improve this answer

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.