John Bokma MexIT
freelance Perl programmer

Backup each MySQL database to a separate file bash script

Wednesday, June 8, 2011 | 2 comments

Today, while still preparing for the reinstall of a more recent version of Ubuntu on my Dell Vostro, I wrote a small bash script to backup each MySQL database to a separate compressed file. I have not much experience with writing bash scripts and at first wanted to write the program in Perl. But after firing up Emacs and typing the first lines of the Perl program I had in mind I killed the buffer and wrote a bash script instead, using O'Reilly's "bash cookbook" to help me out with the features I am less familiar with.

I named the script mysqlbu.sh. Use this script at your own risk. The -h option provides a short usage line. If you notice any bug or if you have a useful addition please post your feedback as a comment.

#!/bin/bash

# $Id: mysqlbu.sh 5 2011-06-08 23:58:15Z john $

# Simple MySQL dump script which dumps each database to a compressed
# file with the date included in the file name

MYSQL='/usr/bin/mysql'

MYSQLDUMP='/usr/bin/mysqldump'
DUMPOPTS='--opt --hex-blob --skip-extended-insert'

DATEFORMAT='%Y%m%d-%H%M%S'  # See man date

user=root
pass=
dir='.'
while getopts 'u:d:p:h' OPTION
do
    case $OPTION in
	u)
	    user="$OPTARG"
            ;;
	d)
	    dir="$OPTARG"
            ;;
	p)
	    pass="$OPTARG"
            ;;
	h|?)
	    printf  "Usage: %s: [-u USER] [-p PASSWORD] [-d DIRECTORY]\n" \
		$(basename $0) >&2
	    exit 2
            ;;
    esac
done
if [ -z "$pass" ]
then
    read -s -p "password: " pass ; printf "%b" "\n"
fi

# Get the names of the database tables
databases=`$MYSQL -u$user -p$pass --skip-column-names -e'SHOW DATABASES'`

# Write the compressed dump for each table
for db in $databases; do
    filename=`date +"$dir/$db-$DATEFORMAT.sql.gz"`
    echo "creating $filename"
    $MYSQLDUMP $DUMPOPTS -u$user -p$pass --database $db \
	| gzip -9 > $filename
done

Example: ./mysqlbu.sh -d ~/backup

This will first ask you for the root password of MySQL and then create a file for each database table. Before each file is created the filename is shown so you can follow the progress of the bash script. Excerpt of the output given by the program with some entries replaced by colons:

creating /home/john/backup/information_schema-20110608-190623.sql.gz
:
:
creating /home/john/backup/mysql-20110608-190624.sql.gz
:
:
creating /home/john/backup/wikidb-20110608-190627.sql.gz

Note how each file has a date and time encoded in the filename, the format of which can be controlled via DATEFORMAT in the bash script.

Please post a comment | read 2 comments, latest by John Bokma | RSS feed
Bookshelves and installing software >
< Resolving domain names in CentOS