Shell Script to Backup All MySql Databases

In this tutorial, I will show how to write a shell script to backup all your mysql databases. Its very essential to take database backup in timely manner.

The shell script will create dump of all databases from mysql server and then convert file to a compressed format (tar.gz). The script is using mysqldump and tar command for this purpose.

Shell Script to Backup all mysql database and compress file

#!/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"

Script Output

We named script as 'mysql_backup.sh' and running on localhost. Below shows the output of the script.

#./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 tar.gz file

You can use tar command to unpack the compressed file as shown below:

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