For When You Can't Have The Real Thing
[ start | index | login ]
start > MySQL > backup and restore

backup and restore

Created by dave. Last edited by dave, 10 years and 196 days ago. Viewed 9,444 times. #12
[diff] [history] [edit] [rdf]

The Theory


for i in `echo "show databases" | mysql -u mysql | grep -v Database`; do
        echo $i
        mysqldump --databases $i --opt --user=root --password=$PASSWORD > /usr/tmp/mysql/$i.dump


# mysql -u root < $DATABASE.dump

The Catch

...but be aware of the mysql backup and restore problem I was having

A Little More Complicated

Having the password on the command line is insecure as anyone on the system when this command runs can see the password through simple manipulations of the ps command. So create yourself a defaults file for mysqldump, /etc/mysqldump.cnf:

  • client is literal, not the name of your client!
  • make sure this file is root:root, 400 or 600, so only the root user can read it!
Now, /usr/local/sbin/mysql-backup:

NOW=`perl -e 'print time;'`
cd /opt/backup/mysql
mkdir $NOW
for i in `echo "show databases" | mysql --defaults-file=/etc/mysqldump.cnf | grep -v Database`; do
        mysqldump --defaults-file=/etc/mysqldump.cnf --databases $i --opt > $NOW/$i.dump
tar cfpz $NOW.tgz $NOW
du -sh $NOW $NOW/* > $
rm -rf $NOW

Run this out of cron. Also you'll want to add a cronjob to clean up old backups:

# capture nightly mysql backup before tape gets cued up.
40 21 * * * /usr/local/sbin/mysql.backup
# delete backups older than two days.
45 3 * * * for i in `find /opt/backup -type f -mtime +2` ; do rm -f $i ; done

My experience shows I can dump and compress 2G of raw MySQL into about a 400M .tgz file in about 10 minutes.

I picked two days for the backup because if my backup for today is still running when the tape hits, I know that yesterday's backup will be complete and will hit the tape; therefore at least one of the three backups on any given day's backup will be complete (three because when the cleaner runs at 03:45, it trims the number of backup sets on disk to two, then at 21:40 we add today's, which is the third).

Be aware of the access permissions on /opt/backup and /usr/local/sbin/mysql.backup; you will probably only want them accessible by the account doing the backup.

Warning: Skipping the data of table mysql.event. Specify the --events option explicitly


Oh fun, a new feature.

This alteration removes mysql from the list of databases dumped, then dumps it separately with the options required to suppress the warning above (>>source). It also deals with the information_schema database separately.

NOW=`date +%s`
cd /opt/backup/mysql
mkdir $NOW
mysqldump --defaults-file=/etc/mysqldump.cnf --databases mysql --events --ignore-table=mysql.event > $NOW/mysql.dump
mysqldump --defaults-file=/etc/mysqldump.cnf --databases information_schema --single-transaction > $NOW/information_schema.dump
for i in `echo "show databases" | mysql --defaults-file=/etc/mysqldump.cnf | egrep -v 'Database|information_schema|mysql'`; do
  mysqldump --defaults-file=/etc/mysqldump.cnf --databases $i --opt > $NOW/$i.dump
tar cfpz $NOW.tgz $NOW
du -sh $NOW $NOW/* > $
rm -rf $NOW
rm -f `find /opt/backup/mysql -type f -mtime +2`
no comments | post comment
This is a collection of techical information, much of it learned the hard way. Consider it a lab book or a /info directory. I doubt much of it will be of use to anyone else.

Useful: | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt