backup and restore

The Theory

Backup:

#!/bin/bash
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
done

Restore:

# 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]
user=root
password=YOUR_MYSQL_ROOT_PASSWORD

• 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!

#!/bin/bash
NOW=`perl -e 'print time;'`
cd /opt/backup/mysql
mkdir $NOW
for i in `echo "show databases" | mysql -u root --password="MySqlRootPassword" | grep -v Database`; do
        mysqldump --defaults-file=/etc/mysqldump.cnf --databases $i --opt > $NOW/$i.dumpdone
tar cfpz $NOW.tgz $NOW
du -sh $NOW $NOW/* > $NOW.report
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.