How to create MySQL Database Backup using Shell Script

How to create MySQL Database Backup using Shell Script

I am going to represent a general Mysql Backup Script here.

-- You have to create appropriate directories for the backup task and make sure that file permission on configuration file, script file, lock file etc. are appropriately set so that only the intended user can access the various files and no one else. 

-- MySQL user (os user) that is used to create the database backup has a configuration file in his home directory that stores the password that is required to login into the database.

-- I have commented the uuencode command, you can test the script and mailing capability of the server and can uncomment the command to send mails if required.

-- The script is running under mysql user therefore he will have the rights over the backup and log files that will be created in the process.

I had put my script under /dba/mysql/scripts directory and give mysql appropriate privileges to run it.

You can place your script anywhere as you desire.

NOTE:

The script and backup & restore process must be discussed and tested thoroughly before applying to production level databases.

MySQL Backup Script

~~~~~~~~~~~~~~~~~~~~~~

# Script to backup MySQL databases

#!/bin/bash

# Lock file to check that only one instance of the Script run at a time

lock_file="/dba/locks/mysql_autobckp.lck"

# MySQL and Mysqldump settings

mysql_user="root"

# Create backup directory, log file and set permissions

backup_parent_dir="/mysql_backup"

backup_date=`date +%d_%m_%Y_%H_%M`

log_date=`date +%d%m%Y_%H%M`

backup_dir="${backup_parent_dir}/${backup_date}"

log_file="/dba/logs/mysqlbckp_${log_date}.log"

touch "${log_file}"

mkdir -p "${backup_dir}"

chmod -R 700 "${backup_dir}"

chmod 600 "${log_file}"

# DO NOT BACKUP these databases

#ignore_db="test mynewdb mytest"

ignore_db="mynewdb mytest"

# Check whether Log File is accessible or not

if [ ! -f "${log_file}" ] && [ ! -w "${log_file}" ]; then

# Generate a mail saying that log file inaccessible and send it to user.

log_file="/dev/null"

fi

# Check whether Lock File exists or not

if [ -f "${lock_file}" ]; then

echo " The Mysql Database(s) backup script is already running .........." >> ${log_file}

echo " DBA Investigation required .........." >> ${log_file}

# uuencode ${log_file} ${log_file} | mail someone@domain.com

exit 0

else

touch /dba/locks/mysql_autobckp.lck

chmod 600 /dba/locks/mysql_autobckp.lck

fi

# Get MySQL databases if running

mysql_alive=`mysqladmin --user=${mysql_user} -s ping|grep alive|wc -l`

if [ "$mysql_alive" == 1 ]; then

{

mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} -B | sed /^Database$/d`

}

else

{

echo "Oops Can’t Connect to Database, may be your database server is DOWN" >> ${log_file}

rm -f /dba/locks/mysql_autobckp.lck

echo "Lock File removed ....." >> ${log_file}

# uuencode ${log_file} ${log_file} | mail someone@domain.com

exit 0

}

fi

# Check whether Backup directory created and writable by user

if [ -d "${backup_dir}" ] && [ -w "${backup_dir}" ]; then

{

echo "Ready to Perform Backup ......." >> ${log_file}

echo " " >> ${log_file}

}

else

{

echo "Backup Directory either does not exists or not Writeable by User: `echo $USER|tr [a-z] [A-Z]`" >> ${log_file}

rm -f /dba/locks/mysql_autobckp.lck

echo "Lock File removed ....." >> ${log_file}

# uuencode ${log_file} ${log_file} | mail someone@domain.com

exit 0

}

fi

echo " " >> ${log_file}

echo "MySQL Database(s) Backup Started......." >> ${log_file}

echo " " >> ${log_file}

echo "MySQL Database(s) Backup Process ID: $$ " >> ${log_file}

echo " " >> ${log_file}

# Backup and compress each database

for database in $mysql_databases

do

skipdb=-1

if [ "$ignore_db" != "" ]; then

      for i in $ignore_db

      do

      [ "$database" == "$i" ] && skipdb=1 || :

      done

            if [ "$skipdb" == 1 ]; then

            echo "Skipping backup of \"${database}\" database ........." >> ${log_file}

            echo " " >> ${log_file}

            fi

fi

if [ "$skipdb" == -1 ]; then

if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ];then

      additional_mysqldump_params="--skip-lock-tables"

else

      additional_mysqldump_params=""

fi

echo "Creating backup of \"${database}\" database .........." >> ${log_file}

echo " " >> ${log_file}

echo "Start Time:" >> ${log_file}

echo `date +%d%m%Y_%H:%M:%S` >> ${log_file}

echo " " >> ${log_file}

mysqldump ${additional_mysqldump_params} --user=${mysql_user} ${database} | gzip > "${backup_dir}/${database}_bckp.sql.gz"

echo "Finish Time:" >> ${log_file}

echo `date +%d%m%Y_%H:%M:%S` >> ${log_file}

echo " " >> ${log_file}

chown mysql:mysql "${backup_dir}/${database}_bckp.sql.gz"

chmod 600 "${backup_dir}/${database}_bckp.sql.gz"

fi

done

echo "MySQL Database(s) Backup Completed ......." >> ${log_file}

echo " " >> ${log_file}

rm -f /dba/locks/mysql_autobckp.lck

echo "Lock File removed ....." >> ${log_file}

# uuencode ${log_file} ${log_file} | mail someone@domain.com