Backing Up and Rotating MySQL Databases the Easy Way

By · Published · linux, bash, mysql

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/*.sql

We're doing several things here:

  1. First, we define some variables to help us. Notice that we're using --defaults-extra-file=/root/.my.cnf which points to the config file we made in step 1.

  2. We call show databases using the MySQL client, then remove the databases we don't want to drop using egrep. You could conceivably argue that you should backup the users table, but I'm not going to worry about that one for now.

  3. Using xargs we pass each database to mysqldump to create a [name].sql file in /backups/mysql.

  4. 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.

( Comments )

Did something I wrote help you out?

That's great! I don't earn any money from this site - I run no ads, sell no products and participate in no affiliate programs. I do this solely because it's fun; I enjoy writing and sharing what I learn.

All the same, if you found this article helpful and want to show your appreciation, here's my Amazon.com wishlist.


Related Posts

BASH Quickie: Backing Up MySQL Databases

Finding Multi-byte Characters in MySQL Fields


comments powered by Disqus