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 217 days ago. Viewed 9,495 times. #12
[diff] [history] [edit] [rdf]
labels
attachments

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`
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:


snipsnap.org | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt