0

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?

4 Answers 4

2

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.

4
  • 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) Jun 5, 2009 at 16:39
  • 1
    Most likely requests will just block. They may timeout and return a 500 error unless you handle it yourself. Jun 5, 2009 at 16:50
  • i put --single-transaction in the batch file it looks it is working good Jun 8, 2009 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, 2009 at 9:31
4

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.

2
  • 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, 2009 at 16:27
  • This is why you have nagios checks on your replication status :) Jun 5, 2009 at 16:50
0

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).

0

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.7/en/replication-solutions-backups-rawdata.html

You must log in to answer this question.

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