I run a number of MySQL databases, the number of mysqld installations that I run is something like 8, but I may have forgotten some. With the number of servers that I run on a “do nothing except when it breaks” basis it’s difficult to remember the details. The number of actual databases that I run would be something like 30, four databases running on a database server (not counting “mysql” is fairly common. Now I need to maintain some sort of automated backup of these, this fact became obvious to me a couple of days ago when I found myself trying to recreate blog entries and comments from Google’s cache…
There are two types of database that I run. There are ones of significant size (more than 1GB) and tiny ones – I don’t think I run any database which has a MySQL dump file that is more than 20M and less than 2G in size.
For the machines with small databases I have the following script run daily from a cron job (with db1 etc replaced with real database names and /mysql-backup replaced by something more appropriate). The “--skip-extended-insert” option allows the possibility of running diff on the dump files but at the cost of increasing file size, when the raw file size is less than 20M this overhead doesn’t matter – and gzip should handle some extra redundancy well.
#!/bin/bash -e
for n in db1 etc ; do
/usr/bin/mysqldump --skip-extended-insert $n | gzip -9 > /mysql-backup/$n-`date +%Y-%m-%d`.gz
done
Then I have a backup server running the following script from a cron job to copy all the dump files off the machines.
#!/bin/bash -e
cd /backup-store
for n in server1 server2 ; do
scp $n:/mysql-backup/*-`date +%Y-%m-%d`.gz $n
done
This script relies on being run after the script that generates the dump files. Which is a little more tricky than it should be, it’s a pity that cron jobs can’t be set to have UTC run times. I could have run the dumps more frequently and used rsync to transfer the data, but it seems that the risk of losing one day’s worth of data is acceptable. For my blog I can get any posts that I might lose from Planet installations in that time period.
For the bigger databases my backup method starts by putting the database and the binary log files on the same filesystem – not /var. This requires some minor hackery of the MySQL configuration. Then I use rsync to copy the contents of an LVM snapshot of the block device. The risks of data consistency problems involved in doing this should be no greater than the risks from an unexpected power fluctuation – and the system should be able to recover from that without any problems.
My experience with MySQL dumps is that they take too long and too much system resources for large databases so I only use them for backing up small databases (where a dump can be completed in a matter of seconds so even without using a transaction it doesn’t hurt).
Have you heard of BackupNinja (https://labs.riseup.net/code/projects/show/backupninja/) ?
It seems a very nice tool including configuration for mysql databases.
Maybe that’d be helpful also.
Something else worth mentioning: don’t compress the mysql dump if you’re just going to put it into another backup that’s compressed anyway. You’ll improve the chances of being able to deduplicate then.
I rely on rsnapshot (http://rsnapshot.org/) to grab important files over a period of time. I also dump mysql databases out with it.
It works extremely well… if you haven’t already looked at it — it’s worth doing so.
Ohh.. I also forgot to mention… if you have system resource issues…
Try using both ionice and nice to drop the priority of the dumps. I use that in conjunction with rsnapshot (mentioned above), to reduce the load on a server. The combo works well.
I have created a backup user with limited resources on my sql server. I then backup databases on another machine. Mysql port is only open to this one ip address. Anyways posted my script here, http://gist.github.com/235274 ugly as sin currently, but some day I’ll clean it up. Does get the job done great for my needs.
A useful trick we use for backing up our SQL (postgresql) database: dump it to a backup directory as a dated file, encrypted with GPG using both of our public keys, and then share that directory using anonymous rsync. No need to secure rsync if it only copies secure data. :)
I’d advise to add –lock-all-tables to mysqldump or using mysqlhotcopy or mylvmbackup…
Rather than:
for n in db1 etc;do
try:
for db in $(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -Bse ‘show databases’);do
That way you don’t have to remember to update the script when you create new databases.
Keith
Hi Russell,
here’s the latest variation of my mysql backup script. for each database, it dumps the schema (table & index creation, etc) and data into separate files. I’ve found that it’s extremely convenient to have the table creation commands separate from the data.
actually, it backups up two copies of the data. one with extended inserts, one with complete inserts. In the past, i’ve had the occasional problem with restoring extended inserts – complete inserts are much slower but 100% reliable.
it dumps these to a temporary directory, and then uses rdiff-backup to make a revision-controlled backup to a standard location (/var/backups/mysql/$HOSTNAME). this can then be rsynced to another (central) host.
feel free to scavenge ideas from the script, or adapt it your needs.
it assumes there is a ~/.my.cnf file to get the login details from.
#! /bin/bash
HOST=$(hostname -s)
BACKUPDIR=/var/backups/mysql/$HOST
TMPDIR=/tmp/mysql-backups/$HOST
mkdir -p $BACKUPDIR $TMPDIR
ARGS=”–single-transaction –flush-logs –skip-opt –no-create-db –no-create-info”
DATABASES=$( mysql -D mysql –skip-column-names -B -e ‘show databases;’ | egrep -v ‘information_schema’ );
mysql -Bse “SELECT CONCAT(‘SHOW GRANTS FOR \”, user ,’\’@\”, host, ‘\’;’) FROM mysql.user” | mysql -Bs | sed ‘s/$/;/g’ > mysql-grants.txt
for i in $DATABASES ; do
# “create database” lines, for easy cut-and-paste
echo “CREATE DATABASE $i;” >> $TMPDIR/mysql-create-databases.txt
echo -n “backing up $i: schema…”
mysqldump $ARGS –no-data $i > $TMPDIR/$i.schema.sql
echo -n “data…complete…”
mysqldump $ARGS –complete-insert $i > $TMPDIR/$i.data.complete.sql
echo -n “extended…”
mysqldump $ARGS –extended-insert $i > $TMPDIR/$i.data.extended.sql
echo “done.”
done
RDIFF_ARGS=””
rdiff-backup $RDIFF_ARGS $TMPDIR/ $BACKUPDIR/