MySQL Backup Scheme

This MySQL backup scheme is a nightly cronjob running a shell script which calls mysqldump. Every night, subscribed servers run the cronjob /etc/cron.d/mydump which in turn executes /opt/bin/mydump.sh. This script does so auto-discovery and ultimately writes a file onto the server in /net/server/share2/backup/mydump/<host>/<Weekday>/<dbname>.bz2.enc where:

• <host> is the short hostname (e.g. hostname -s) of the server
• <Weekday> is the day of the week e.g. Fri
• <dbname> is the database name e.g. mysql or foodb

Due to the fact that these files are encrypted for security and compressed for space, the recovery process looks something like the following.

A) Unencrypt the compressed file (retrieve the password from keepass).

openssl bf -d -in dbname.bz2.enc -out dbname.bz2
enter bf-cbc decryption password:

B) Uncompress the dbname.bz2 file & load into database

bzcat dbname.bz2 | mysql dbname

C) As above, but soup-to-nuts

mysqladmin drop dbname
mysqladmin create dbname
cd /net/server/share2/backup/mydump/<host>/<Weekday>/
openssl bf -d -in dbname.bz2.enc | bzcat | mysql dbname

Setup Guide

To add a host (mysql server) into this backup scheme, perform the following actions.

On mysql server...

1. Verify or setup automount daemon (autofs) so that /net can be used to access the file server e.g. /net/server/
2. Create the host folder below /share2/backup/mydump/ (use short name)
   mkdir /net/server/share2/backup/mydump/`hostname -s`/
3. create a /root/.my.cnf with the necessary creds (perhaps copy from another host)...
   

cat <<EOF >/root/.my.cnf
[client]
password = somebigsecret
EOF
chmod 600 /root/.my.cnf

1. Run mysqlshow (without -p) to validate you have the transparent auth working as required. This won't work from vanilla sudo, so try su -
2. Create databases.txt using output from mysqlshow (you shouldn't have to enter -p since .my.cnf is setup)...
   ls -1 /var/lib/mysql > /net/server/share2/backup/mydump/`hostname -s`/databases.txt

1. Massage databases.txt so that each line simply has dbname to backup
2. Typically you can skip the databases named 'test' and 'information_schema'
3. Using apg, pick a nice long password to use for encryption...
   apg -m 64 -x 64 -a 1 -s
4. Put the output from apg into /root/.encpass on the host as well as keepass for offline recovery
5. Protect the encpass file like so...
   chmod 600 /root/.encpass
6. Run these commands on the host and everything should "just work" at this point...
   /opt/bin/mydump.sh

Note that the cronjob will run automatically the following morning and thereafter every day, putting the db backups into the corresponding weekday folders. This ensures we have recent backups (up to 1 week) on server which will probably suffice in most situations. In case we need to go back further, retrieval from tape may be necessary.

Related

• MySQL documentation for the mysqldump command.