Tech Notes And Miscellaneous Thoughts
 
bash, Geek, Linux, mysql, Programming

backup-mysql.sh

A bash script to backup mysql databases, with separate schema and plain-text dump files (INSERT commands) for each database. Makes it easy to restore individual databases or copy them into dev/test servers. Keeps 30 days worth of backups in separate YYYY-MM-DD directories.

Also dumps the mysql grants to a separate file ‘mysql-grants.txt’, and creates a sometimes conveniently useful ‘mysql-create-databases.txt’ containing “CREATE DATABASE” commands.

Based on my similar backup script for postgresql databases.

#! /bin/bash

# mysql backup script
#
# $Id: backup-mysql.sh,v 1.8 2013/06/21 02:20:39 cas Exp $
#
# by Craig Sanders <cas@taz.net.au>
# this script is public domain.  do whatever you want with it.

# set user/password here, or leave undefined to rely on ~/.my.cnf
#MYSQL_USER="root"
#MYSQL_PWD="SECRET"

[ -n "$MYSQL_USER" ] && ARGS="$ARGS -u$MYSQL_USER"
[ -n "$MYSQL_PWD" ] && ARGS="$ARGS -p$MYSQL_PWD"

DATABASES=$(mysql $ARGS -D mysql --skip-column-names -B -e 'show databases;' | egrep -v 'information_schema' );

BACKUPDIR=/var/backups/mysql

YEAR=$(date +"%Y")
MONTH=$(date +"%m")
DAY=$(date +"%d")

DATE="$YEAR-$MONTH/$YEAR-$MONTH-$DAY"

# make sure that perms on all created files/dirs are rwxr-x---
umask 0027

mkdir -p "$BACKUPDIR/$DATE"
cd "$BACKUPDIR/$DATE"

mysql $ARGS -Bse "SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user" | \
    mysql $ARGS -Bs | \
    sed 's/$/;/g'  > mysql-grants.txt

# "create database" lines, for easy cut-and-paste
> mysql-create-databases.txt
for i in $DATABASES ; do
  echo "CREATE DATABASE $i;" >> mysql-create-databases.txt
done


ARGS2="--skip-opt --no-create-db --no-create-info --single-transaction --flush-logs"

for db in $DATABASES ; do 
    echo -n "backing up $db: schema..."
    mysqldump $ARGS --no-data $db > $db.schema.sql

    echo -n "extended..."
    mysqldump $ARGS $ARGS2 --extended-insert $db > $db.data.extended.sql

    # uncomment if you want complete-insert dumps too.
    #echo -n "data...complete..."
    #mysqldump $ARGS $ARGS2 --complete-insert $db > $db.data.complete.sql

    echo -n "compressing..."
    gzip -9fq $db.schema.sql $db.data.extended.sql #$db.data.complete.sql

    echo "done."
done

echo deleting backups older than 30 days:
find "$BACKUPDIR" -mindepth 1 -a -type d -mtime +30 -print0 | xargs -0r rm -rfv

11 Comments

    1. cas

      thanks for the info, i wasn’t aware of that one. I think i’ll stick with my own scripts, though (this one, and another variant that uses rdiff-backup) – they do exactly what I want.

      1. Thomas Goirand

        Hi,
        I’m the maintainer of automysqlbackup. Actually, you should really have a look into automysqlbackup. You thought about few of the problems you may have when doing backups, like the umask, and some others, but you forgot lots of them, and your script is missing many features of automysqlbackup (like daily, weekly, and monthly backups, which covers absolutely all needs). The nice part of automysqlbackup is also that it lived inside Debian for a large amount of time, and gathered feedback from users, which means that many, many, many small problems have been fixed in it. Over the years, I can even say that it’s now a fork from the original code (it’s unfortunate, and happened because of many things that I added in the package, plus upstream not adding them and releasing a new version which was really different and impossible to merge back).

        I would strongly suggest to not work on your own, that’s always less good than working as a team. If there’s something that is missing in automysqlbackup (actually, I don’t think there is), then it can be added, and I would welcome you to contribute patches.

        1. cas

          I’ll take a look at it, but the most important feature to me is the ability to easily restore individual databases (or just create the same database tables on a different server with or without the data).

          I typically run mysql servers for other people, with lots of different users each having their own database(s) on the same server – having to extract the dump for just one db out of a huge dump is a major PITA. Even my own (fairly light) personal use of both postgresql and mysql has numerous different unrelated databases.

          If automysqlbackup makes it easy to do that, then it’s definitely worth me spending time to evaluate it.

  2. agi

    You may want to include the procedures in each database dump (–routines), although they will be in the mysql DB dump, it may be nice to have them with its DB.

    1. cas

      thanks, great suggestion! wish i’d thought of that myself. probably should dump –routines along with the schema (table/index creation), rather than the data dump. or maybe as a separate file.

  3. Konstantin Khomoutov

    Two points:

    Passing passwords on the command line is a bad habit. Yes, you might be doing this in a controlled environment, but in general any process on the same machine might snatch your credentials while your dumping process runs.

    With MySQL, a way to overcome this is to use a minimal client configuration file with the contents like this:

    [client]
    user = some-username
    password = some-password

    This file is then passed to any MySQL tool which connects to the server using the --defaults-file command-line option.

    This file should be readable only by the user executing the backup script. It might be pre-created or generated at runtime (using umask 077 + mktemp + cat …).
    You might want to install the percona-toolkit package (it was called maatkit originally) and use the pt-show-grants binary from there to dump the grants tables. Personally I find it easier to use than hand-scripting dumping these tables.

    1. cas

      Did you see the comment near the top of the script? and that $MYSQL_USER and $MYSQL_PWD are undefined by default.

      # set user/password here, or leave undefined to rely on ~/.my.cnf

      > It might be pre-created or generated at runtime

      that’s actually a good idea – even with $MYSQL_USER and $MYSQL_PWD defined in the script, generate a temporary defaults-file using mktemp and use that rather than use -u and -p. I was never happy about using them but some people I gave the script to wanted it self-contained, without needing to set up a ~/.my.cnf file.

      and thanks for the info about percona-toolkit – I used to use some of the tools in maatkit years ago, but wasn’t aware that it had been renamed/evolved into a different package. at first glance, pt-show-grants seems to produce better-commented and organised but functionally-identical output to my SELECT CONCAT('SHOW GRANTS...). and it probably covers a lot of cases that my simple sql one-liner doesn’t. nice.

        1. cas

          that works only for root, as only root has read access to /etc/mysql/debian.cnf

          if you want to backup some or all of mysql as a non-root user you have to use some other method of providing authorisation credentials.

          that isn’t a huge problem….but it’s also not uncommon to have a ‘backup@localhost’ user or similar (or to use a ‘replication@’ user for backup as well as replication) which only has read access….debian-sys-maint has full RW access.

Comments are closed.