Speed up database backups with ease
Submitted by cyberlot on Thu, 08/23/2007 - 01:10
You need a snapshot of your database to restore replication but you want to reduce your downtime what can do you?
Backing up gigs of data can take a few minutes, minutes your database is down because you wanted to ensure you get a perfect copy.
You could try the following
mysql stop
cp -ax mysql backup
mysql start
Now I am sure everyone is familiar with the above but how about
mysqladmin -u root -p flush-tables
rsync -av mysql backup
mysql stop
rsync -av mysql backup
mysql start
The above method makes sure you have a "almost" perfect copy before bringing down your db then "fixes" that copy with any minor changes before restarting the db. In my own case backup downtime went from 10 minutes to under a minute.
no need to stop mysql
What about this solution:
mysql -u root -p < backup.sql
- backup.sql -
system rsync -av --stats /path/to/mysql /path/to/backup
FLUSH TABLES WITH READ LOCK;
system rsync -av --stats /path/to/mysql /path/to/backup
UNLOCK TABLES;