MySQL Forums
Forum List  »  Backup

Re: Which way is best to do backups?
Posted by: Rick James
Date: January 07, 2011 11:40PM

To have a usable backup, you need to stop all writes to the entire database during the copy.

If you are using MyISAM, that has to involve a hard lock via one of these
* Stop the server
* FLUSH TABLES WITH READ LOCK (in a separate thread)
* SET readonly=1

You probably have 65MB to spare on the same machine? Then simply copy the /var/lib/mysql directory (and all subdirectories) to another directory. This will involve one read and one write. Throwing gzip (bzip, etc) into the mix would cut down the write side by 3x, but might run slower due to CPU activity. rsync has to read both copies, so it cannot be faster; it may be the same speed if the target directory starts out empty.

If you don't have 65MB to spare, use -z option on tar. If that is still too big, then you are stuck with waiting for scp to finish.

If you are using only InnoDB, consider xtrabackup. It is like hotbackup, but free.

Do the scp (of the copy) _after_ bringing up mysqld again. There will be some disk and cpu contention, but at least MySQL will be up.

The absolutely fastest way I have found is to use LVM to take a snapshot on Linux -- downtime is 1 minute, regardless of data size. There is still an open question of whether you even need to stop MySQL if you are using just InnoDB.

Options: ReplyQuote


Subject
Views
Written By
Posted
3077
January 06, 2011 12:54PM
1413
January 06, 2011 12:57PM
Re: Which way is best to do backups?
1682
January 07, 2011 11:40PM
1488
January 31, 2011 08:16AM
1504
January 31, 2011 10:16AM
1447
February 09, 2011 02:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.