Script Vault – mysql Database Backup


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.

# use mysqldump to Dump DB and compress it on the fly to a mounted partition
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

echo "Host [$H]" >> $LF
echo "DB File [$DBFILE]" >> $LF
if [ -a  $DBFILE ]
mv $DBFILE $DBFILE.`date '+%M%S'`
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'`
mkdir -p $LF_DIR
chmod 777 $LF_DIR
touch $LF
chmod 664 $LF


#                     MAIN Code Start

while read line
echo "Backuping up: $line"
done < $DBLIST
echo "All backups Completed" >> $LF

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):
  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

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

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/ > /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.


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.




Leave a Reply

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

You are commenting using your 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