Shell Script to Backup MySql Databases in Compressed Format

This shell script will create dump of all databases from mysql server. Allow system administrator to have backups of all databases. The output would be in a tar.gz zipped format. Script is using mysqldump and tar command for this purpose.

Shell Script

#!/bin/bash

#Check if user input hostname, if not – exit from script
if [ "$#" = "0" ]; then

echo "Usage: `basename $0` "

exit 2

fi

#We use some variables:
#we save hostname that user input to MYSQL variable
MYSQL=$1

#Folder to keep backups
BACKUP_DIR="/data"

#we getting current timestamp, to use in filenames
NOW=`date +%Y%m%d-%H%M%S`

#name of file will be HOSTNAME-CURRENT_TIMESTAMP
NAME=${MYSQL}-${NOW}

#we use mysql access without password
PASSWORD=""

#This will create folder, where we will keep all dumps
mkdir ${BACKUP_DIR}/${NAME}

#Telling user that we start to process hostnname
echo "Processing ${MYSQL}..."

#This will get list all of databases, we use mysql command to login to server, and with awk get only
#database name without description and database with name information_schema
DBS=`echo "show databases;" | mysql --host=${MYSQL} --user=root --password=${PASSWD} | \

awk '{if ($1 != "Database" && $1 != "information_schema") {print $1}}'`

#Now we will process every database, we will create dump for each
for DB in ${DBS}; do

#telling user that we starting to dump each database
echo -n " Dumping ${DB}... "

#if database is not mysql we need to lock tables, to avoid problems when user during same time save
#something to db
if [ ${DB} != "mysql" ]; then

LOCKS="--lock-tables"

else

LOCKS=""

fi

#command that will create dump
mysqldump --host=${MYSQL} --user=root --password=${PASSWD} --force \

--allow-keywords --complete-insert --add-drop-table --add-locks --flush-logs \

${LOCKS} --result-file=${BACKUP_DIR}/${NAME}/${DB}-`date +%Y%m%d-%H%M%S`.sql ${DB} < /dev/null #telling user that process done for this database echo "Done" done echo #Now we will compress all dumps (stored in directory) to single file echo -n " Compressing files... " #with tar command we compress directory tar -czf ${BACKUP_DIR}/mysql-${NAME}.tar.gz ${BACKUP_DIR}/${NAME} >/dev/null 2>&1

echo Done

#removing uneeded directory
rm -rf ${BACKUP_DIR}/${NAME}

echo "Dump of host ${MYSQL} finished"

Output

#./mysql_backup.sh localhost

Processing localhost...

Dumping mysql... Done

Dumping test... Done

Compressing files... Done

Dump of host localhost finished

#ls -la /data/
# to get list of files inside directory
total 152

drwxrwxrwx 2 root root 4096 Mar 4 14:44 .

drwxr-xr-x 23 root root 4096 Mar 4 14:25 ..

-rw-r--r-- 1 yevhen yevhen 139361 Mar 4 14:44 mysql-localhost-20130304-144450.tar.gz

Unpack the tar.gz file

server# tar -zxvf mysql-localhost-20130304-144450.tar.gz

data/localhost-20130304-144450/

data/localhost-20130304-144450/test-20130304-144450.sql

data/localhost-20130304-144450/mysql-20130304-144450.sql