Script Vault – mysql Database Backup


Overview

It is essential to backup your mySQL databases if they are being used in a production environment. This post describes a MySQL database backup script that can be fully automated to produce a date-time stamped backup file which is compressed to conserve disk space. The script show is run as root but another special user could be used and the permissions adjusted accordingly.

The script has many advantages, it backs up all the databases, store them in a single directory at the mount point /data/db-backups and even logs its activity to a file in /logs/db-backup for auditing and review in case of any issues.

Designed for Linux implementations, the script needs to be automated using the CRON daemon. It is best to configure it run everyday so a full backup of each database occurs. Having a full backup each day will enable you to have some degree of recoverability in case of errors in your database or assist in the easy migration of your database(s) to another system.

Like all automated processes, once configured, it should continue to run daily forever. But you will need to ensure that old backups are removed so that your disks do not fill up!

The Script

The backup script is a very simple BASH shell script tested on a CENTOS Linux Distribution but capable of running on other variants of Linux with little to no modification.

Cut and paste the following text into an editor and make changes as needed to enhance or reduce the functionality.

#!/bin/bash
# mysql-backup.sh
# use mysqldump to Dump DB and compress it on the fly to a mounted partition
#
BACKUP_DIR="/data/db-backups"
mkdir -p $BACKUP_DIR
chmod 777 $BACKUP_DIR
#
#
SERIAL="`date +%Y%m%d-%H%M%S`"

#=====================================
# Log Functions
#
function LogStart
{
echo "====== Log Start =========" >> $LF
echo "Time: `date`" >> $LF
echo " " >> $LF
}
function LogEnd
{
echo " " >> $LF
echo "Time: `date`" >> $LF
echo "====== Log End   =========" >> $LF
}

#=====================================
#
#
function GetDBList
{
echo "Calling GetDBList()" >> $LF
mysqlshow |grep "|"| tr -d ' '|tr -d '|'| egrep -v Databases > $DBLIST
}

#=====================================
#
#
function DoBackup
{
echo "Calling DoBackup()" >> $LF

DBFILE=$BACKUP_DIR/db-$DB-$SERIAL.sql
echo "Host [$H]" >> $LF
echo "DB File [$DBFILE]" >> $LF
if [ -a  $DBFILE ]
then
mv $DBFILE $DBFILE.`date '+%M%S'`
fi
echo "Dumping ${DB}" >> $LF
mysqldump -B ${DB}  --add-drop-database --add-drop-table >> ${DBFILE}
echo "grant all privileges on ${DB}.* to magento;" >> ${DBFILE}
echo "Zipping up file!" >> $LF
gzip ${DBFILE}
echo "Done!" >> $LF
}

FILE_DATE=`date '+%Y-%m-%d'`
LF_DIR=/logs/db-backup
LF=$LF_DIR/db-backup-$FILE_DATE.log
mkdir -p $LF_DIR
chmod 777 $LF_DIR
touch $LF
chmod 664 $LF

DBLIST=/tmp/dblist-$FILE_DATE.list

LogStart
#=====================================
#
#                     MAIN Code Start

GetDBList
while read line
do
echo "Backuping up: $line"
H="localhost"
DB=$line
DoBackup
done < $DBLIST
echo "All backups Completed" >> $LF
LogEnd
#
# EOF

Testing the Backup

  1. To verify that the backup script is working, execute the backup command as follows (it assumes you put the script in /usr/local/bin):
    /usr/local/bin/mysql-backups.sh
  2. After the backup completes, perform a directory listing of the /data/db-backup directory created by the script, you should see some files, each representing your compressed, backed up database!
    [root@yourserver]#ls -l /data/db-backups
    
    -rw-r--r-- 1 root root    510 Mar  5 15:21 db-information_schema-20140305-152103.sql.gz
    -rw-r--r-- 1 root root    526 Mar  5 15:22 db-information_schema-20140305-152229.sql.gz
    -rw-r--r-- 1 root root 142122 Mar  5 15:21 db-mysql-20140305-152103.sql.gz
    -rw-r--r-- 1 root root 142283 Mar  5 15:22 db-mysql-20140305-152229.sql.gz
    ....
    [root@yourserver]#

Automating the Script with CRON

I always put sys admin shell scripts into /usr/local/bin. This directory is specifically set aside for user written system applicable scripts.

The script should be made executable for the root user only, use the following command to flag the file as executable:

"chmod 744 /usr/local/bin/mysql-backup.sh"

To automate the execution of the script (in our case at 10:22pm each night) we will use the Linux CRON application. A single line needs to be entered into the root crontab using the command “crontab -e”, this will pop up an editor and we will add a line to as follows:

30 22 * * * /usr/local/bin/mysql-backups.sh > /dev/null 2>&1

Basically this will enable the script to run at 10:30pm every day. It will also hide any output from the program as the program logs to disk.

Recovery

To recover a backup, use gunzip to de-compress the file then use mysql < backup-file-date-time.sql to play back the SQL file, this will overwrite any existing data.

Here is a typical example of what you need to run:

cd /data/db-backups
gunzip db-mybigdatabase-20140305-152229.sql.gz
mysql < db-mybigdatabase-20140305-152229.sql

Warning: Be very careful of what you are doing as you will over write your database if you make a mistake.

Where to from here?

If you are backing up your server, consider automatically copying the files to another server and recovering them automatically, this enables you to have a production copy of select databases for in-house testing and auditing purposes.

Enjoy!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s