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 --databases $db \ | gzip -9 > $filename done
./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.