blog.up-link.ro android bsd linux unix & open-source world

How To Backup MySQL databases using a shell script

MySQL is one of the most popular open source database management system for the development of interactive websites.

If your server stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster.

Below is a backup script for MySQL databases (please make sure you will change MYSQL_PASS):

#!/bin/sh
# backup mysql databases shell script
# UNIX LINUX BSD
# by Adi http://blog.up-link.ro
# March 2010

DATE=$(date +%Y-%m-%d)
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)
MYSQL_USER="root"
MYSQL_PASS="password"
HOSTNAME=$(hostname)
GZIP=$(which gzip)
ARG="-u $MYSQL_USER -p$MYSQL_PASS"
DATABASES=$($MYSQL $ARG -s -e "SHOW DATABASES;")
BACKUP_PATH="/home/backup/$DATE/mysql"

! [ -d $BACKUP_PATH ] && mkdir -p $BACKUP_PATH

for DB in $DATABASES
do
BACKUP_FILE="$BACKUP_PATH/$HOSTNAME-mysql-$DB-$DATE.sql.gz"
$MYSQLDUMP $ARG $DB | $GZIP -9 > $BACKUP_FILE
done

Download the script from here.