Backing Up and Rotating MySQL Databases the Easy Way

This is an old post!

This post is over 2 years old. Solutions referenced in this article may no longer be valid. Please consider this when utilizing any information referenced here.

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 (0)

Interested in why you can't leave comments on my blog? Read the article about why comments are uniquely terrible and need to die. If you are still interested in commenting on this article, feel free to reach out to me directly and/or share it on social media.

Contact Me
Share It
Linux
In some ways, after years of doing programming and scripting, I’m now sort of rediscovering the power of the shell. Tonight, I was working on my server and remembered that I needed to start backing up my MySQL databases (which you do also … right?). So instead of writing a script to do that, with a little research, I was able to come up with a way to: Dump each database to a separate SQL file, with a timestamp. bzip the file. Keep 5 days worth of backups for each database, rotating the oldest backup off. Here’s what I came up with: cd /backup/mysql; for i in $(mysql -BNe 'show databases' -u root -p<password>); do mysqldump -u root -p<password> $i | bzip2 > $i-`date +"%Y%m%d"`.sql.bz2; rm -rf $i-`date -d "-5 day" +"%Y%m%d"`.sql.bz2; done > /dev/null 2>&1 Shoved that in my crontab. Works great. Linux rocks.
Read More
Apache
In working on a side project with a few friendly developers, we decided to set up a Subversion repository and a Trac bug and issue tracker. Both of these, in normal setups, rely on HTTP authentication. So, being that we already had an authentication database as part of the project, my natural first thought was to find a way to authenticate Trac and Subversion of these against our existing MySQL authentication database rather than to rely on Apache passwd files that would have to be updated separately. Surprisingly, this was more difficult than it sounded. My first thought was to try mod_auth_mysql. However, from the front page, it looks as if this project has not been updated since 2005 and is likely not being actively maintained. Nonetheless, I gave it a shot and, surprisingly, got it mostly working against Apache 2.2.14. Notice I said “mostly.” It would authenticate about 50% of the time, while filling the Apache error logs with fun things like: [Sat Feb 13 11:11:27 2010] [error] [client -.-.-.-] MySQL ERROR: Lost connection to MySQL server at 'reading initial communication packet', system error: 0 [Sat Feb 13 11:11:28 2010] [notice] child pid 19074 exit signal Segmentation fault (11) [Sat Feb 13 11:34:14 2010] [error] [client -.-.-.-] MySQL ERROR: Lost connection to MySQL server during query: [Sat Feb 13 11:34:15 2010] [error] [client -.-.-.-] MySQL ERROR: MySQL server has gone away:` Rather than tear into this and try to figure out why a 5-year-old auth module isn’t working against far newer code, and with very little to actually go on, I just concluded that it wasn’t compatible and looked for a different solution. That’s when I came across mod_authnz_external. If your’e not familiar with this module, what it allows you to do is auth against a program or script running on your system, therefore allowing you to auth against anything you want - a script talking to a database, PAM system logins, LDAP, pretty much anything you have access to. All you have to do is write the glue code. In pipe mode, mod_authnz_external uses pwauth format, where it passes the username and password to stdin, each separated with a newline. It uses exit codes to return back to Apache whether or not the login was valid. Knowing that, it’s pretty easy to write a little script to intercept the username/password, run a query, and return the login. #!/usr/bin/php <?php` include "secure_prepend.php"; include "database.php"; $fp=fopen("php://stdin","r"); $username = stream_get_line($fp,1024,"\n"); $password = stream_get_line($fp,1024,"\n"); $sql = "select user_id from users where username='%s' and password='%s' and disabled=0"; $sql = sprintf($sql, $db->escape_string($username), $db->escape_string($password)); $user = $db->get_row($sql); if(!empty($user)) { exit(0); } exit(1); ?> Then, you just hook this into your Apache config for Trac or Subversion: AddExternalAuth auth /path/to/authenticator/script SetExternalAuthMethod auth pipe <Location /> DAV svn SVNPath /path/to/svn AuthName "SVN" AuthType Basic AuthBasicProvider external AuthExternal auth require valid-user </Location> Restart, and it should be all working. Some may argue that the true “right” way to do this is LDAP. But with just three of us, LDAP is overkill, especially when we already have the rest of the database stuf in place. The big advantage to this, even over mod_auth_mysql, is the amount of processing you can do on login. You basically can run any number of queries in your authenticator script - rather than just one. You can update with last login or last commit date, for instance. Or you can join tables for group checking; say you want someone to have access to Trac, but not Subversion. You can do that with this.
Read More
MySQL
Discovered something neat with the new version of MySQL and thought it warranted a mention. Storing tree structures in a relational database is a common use case across many different areas of tech. The problem comes when you need to construct a query based on a subset of that tree. But MySQL 8 has some nice new features that makes doing this a breeze.
Read More