Shell script to Backup MySQL Database

Shell script to Backup MySQL Database

(Exclude the System Databases, like performance_schema & information_schema)

This script will automatically mount the share folder from backup_server and make the backup of all the databases which are in the server and execludes the system databases like performance_schema and information_schema 

1. Create a shell script file with Sql_backup.sh 

# vi /root/Sql_backup.sh

Code: Sql_backup.sh

#!/bin/bash

mount -t cifs //backup_server/backup_folder /mnt/backups -o username=username,password=password,domain=domain_name;

if [ `echo $?` -eq 0 ]

then

                cd /mnt/backups;

                STTIME=`date +%d_%m_%y_%H`;

                mkdir /mnt/backups/`echo -e $STTIME`;

                cd /mnt/backups/`echo -e $STTIME`;

                for x in `mysql -uusername -ppassword < /root/show.sql |  grep -v mysql | grep -v "performance_schema"| grep -v "information_schema" | grep -v "Database"`

                do

                                mysqldump -uusername -ppassword $x > `echo -e $x`.sql;

                done

                cd $HOME;

fi

umount /mnt/backups;

2. we have to make this script as executable

# chmod 700 /etc/Sql_backup.sh

3. Create a MySQL show.sql file 

# vi /root/show.sql

#CONTENTS of the show.sql which shows the the databases in MySQL server

SHOW DATABASES;

Running this script automatically we needed to add this to the cron's  so that we can backup the database automatically at certain period

To list the existing cron's

# crontab -l

4. To edit the cron's

# crontab -e

General syntax of the crontab file is

# Minute   Hour   Day of Month       Month          Day of Week        Command   

# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)               

    0        2          12             *                *            /usr/bin/Sql_backup.sh 

Here I planned to do the backup for every 2 hours

* */2 * * *  /root/Sql_backup.sh

Keywords: MSSQL, SQL Backups, Backup Script