Shell Script : Backup MySql Databases In Compressed Format

April 28, 2013 | By
| Reply More

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

shell script mysql backup

Filed Under : SHELL SCRIPTS

Tagged With : ,

Free Linux Ebook to Download

Leave a Reply

Commenting Policy:
Promotion of your products ? Comment gets deleted.
All comments are subject to moderation.