Here’s a little quickie for you. Say you have a small MySQL server floating around your house that you want to have regular backups of. You do want regular backups right?
In my case, the biggest motivation was wanting a regular way to grab a recent
MySQL dump of an internal tool I use at home to develop against. After poking
around the Internet a bit, I was surprised that, other than mysqldump itself,
there doesn’t seem to be a simple tool out there that you can slam into a
cronjob and let it do it’s thing.
So, like any good hacker, I decided to brew my own. After all, when you have 256,428 different solutions, why not make solution 256,429? :)
Caveat: Do not do this on a busy production server. This is mostly because
mysqldump locks tables while dumping. So if you have a large database running
on a busy server this will render each table mostly inaccessible while it is
dumping. If you have a busy server, you should probably use a tool like
XtraBackup.
But for a quiet home system this is fine.
User Permissions
As a general rule, it is bad form to put passwords into a script unless you really have no other recourse. Unfortunately, MySQL doesn’t support something like keyed authentication, but there is another option. We can create a special config file that contains the username and password that is not part of the script.
As root, create a file /root/.my.cnf:
[client]
user = root
password = your-password-here
host = localhost
Now, make it readable to root only:
$ chmod 600 /root/.my.cnf
The Script
The bash script is basically a one liner with some variables.
#!/bin/bash
MYSQL_ARGS="--defaults-extra-file=/root/.my.cnf"
MYSQL="/usr/bin/mysql $MYSQL_ARGS"
MYSQLDUMP="/usr/bin/mysqldump $MYSQL_ARGS"
BACKUP="/backups/mysql"
$MYSQL -BNe "show databases" | egrep -v '(mysql|.*_schema|sys)' | xargs -n1 -I {} $MYSQLDUMP {} -r $BACKUP/{}.sql > /dev/null 2>&1 && chmod 640 $BACKUP/*.sql && chgrp backup $BACKUP/*.sqlWe’re doing several things here:
-
First, we define some variables to help us. Notice that we’re using
--defaults-extra-file=/root/.my.cnfwhich points to the config file we made in step 1. -
We call
show databasesusing the MySQL client, then remove the databases we don’t want to drop usingegrep. You could conceivably argue that you should backup the users table, but I’m not going to worry about that one for now. -
Using
xargswe pass each database tomysqldumpto create a[name].sqlfile in/backups/mysql. -
Finally, we change the user permissions of the new files to be readable only to the root user or users in the backup group, and change the group to backup.
Save and run the script. If /backups/mysql has dumps in it, it works.
Rotating Files
Now, you could totally just cram this script into cron and call it good. But that would only leave the current day’s dump in the directory, and would overwrite it every day. But we want to save a few days’ backups. And you’ll notice there wasn’t anything in the script dealing with rotating backups.
The very first point of the UNIX Philosophy states:
Make each program do one thing well. To do a new job, build afresh rather than complicate old programs by adding new “features”.
With this in mind, remember that there is already a program installed on nearly every Linux machine that is very, very good at rotating files on a schedule and deleting old files.
I’m talking, of course, about logrotate! It can rotate more than just logs. You can use it to rotate nearly any file that you want changed on a schedule.
So instead of using cron, we can do something like this:
$ vi /etc/logrotate.d/mysqlbackup
/backups/mysql/*.sql {
daily
rotate 8
compress
delaycompress
create 640 root backup
postrotate
/root/mysqlbackup.sh
endscript
}
So we let logrotate run the backup script, the compress older copies of the dump and rotate off the really old ones. We run this daily.
To test it, you can run:
$ logrotate --force /etc/logrotate.d/mysqlbackup
If your MySQL dumps rotate, congratulations, everything is now working.
