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
Notes:
- 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:
#!/bin/bash
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
done
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.
Warning: Skipping the data of table mysql.event. Specify the --events option explicitly
(2013-05-08)
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.
#!/bin/bash
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
done
tar cfpz $NOW.tgz $NOW
du -sh $NOW $NOW/* > $NOW.report
rm -rf $NOW
rm -f `find /opt/backup/mysql -type f -mtime +2`