Backup Shell script: each Mysql database to a separated dump

MySql LogoAnother life-saver script for a sysadmin.
Run with root mysql user privileges, so.

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

# 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

Hope this helps as much as helped me.
Ciao! 

Incoming search terms:

  • mail esac pt (48)
(Visited 2,139 times, 1 visits today)

Author: Giuseppe Urso

Giuseppe lives in Haarlem now with his shiny dog, Filippa In 1982 received his first home computer, a Commodore 64, followed by Datasette and a 1541 Floppy Disk Drive. In 1999 he installed his first Linux distro (LRH6). In 2006 he switched to Debian as favourite OS. Giuseppe Urso actively sustains the Free Software Fundation and his founder Richard Mattew Stallman, he speaks to people trying to convince them to join the fight now, and about how important is to use Free Software only. He has a job as Infra Specialist at Hippo Enterprise Java Cms an Open Source Enterprise class Content Management System, one of the coolest company ever, in Amsterdam. He's always ready to install Debian on other people computers for free.

Leave a Reply

Your email address will not be published. Required fields are marked *