Create MySQL Database Backup Every Half an Hour

Our company has launched an online contest for our dedicated clients and we are collecting some really important information from them in order join the contest. My boss wants me to create a database backup every half an hour to make sure we reduce the data loss chance to the minimum possible.

I have create a simple BASH script to accomplish this. The script will create a MySQL database backup and specified in a folder based on date. We only stored 3 latest days backup and will remove other backup folder if exist.

1. Create the script in desired directory. In this case, I will use /home/scripts :

$ mkdir /home/scripts
$ touch /home/scripts/mysqlbackup_30min

2. Using your favourite text editor, paste following scripts into the script /home/scripts/mysqlbackup_30min :

#!/bin/bash
# Scripts to create mysql backup every half and hour
 
# Confiration value
mysql_host=""
mysql_database=""
mysql_username=""
mysql_password=''
backup_path=/backup/mysql
expired=3			#how many days before the backup directory will be removed
 
today=`date +%Y-%m-%d`
 
if [ ! -d $backup_path/$today ]
then
        mkdir -p $backup_path/$today
else
        /usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database > $backup_path/$today/$mysql_database-`date +%H%M`.sql
fi
 
# Remove folder which more than 3 days
find $backup_path -type d -mtime +$expired | xargs rm -Rf

3. Change configuration value to suit your environment. In this case I will information as below:

mysql_host="localhost"
mysql_database="contest_db"
mysql_username="contest_userw"
mysql_password='Okf8#$el'

4. Create a cron job to execute this task every half and hour. Open /var/spool/cron/root and add following line:

*/30 * * * * /bin/sh /home/scripts/mysqlbackup_30min

5. Restart cron daemon:

$ service crond restart

Done. You should see something like below after one day:

$ tree /backup/mysql
.
|-- 2011-11-14
|   |-- contest_db-1930.sql
|   |-- contest_db-2000.sql
|   |-- contest_db-2030.sql
|   |-- contest_db-2100.sql
|   |-- contest_db-2130.sql
|   |-- contest_db-2200.sql
|   |-- contest_db-2230.sql
|   |-- contest_db-2300.sql
|   `-- contest_db-2330.sql
`-- 2011-11-15
    |-- contest_db-0030.sql
    |-- contest_db-0100.sql
    |-- contest_db-0130.sql
    |-- contest_db-0200.sql
    |-- contest_db-0230.sql
    |-- contest_db-0300.sql
    |-- contest_db-0330.sql
    |-- contest_db-0400.sql
    |-- contest_db-0430.sql
    |-- contest_db-0500.sql
    |-- contest_db-0530.sql
    |-- contest_db-0600.sql
    |-- contest_db-0630.sql
    |-- contest_db-0700.sql
    |-- contest_db-0730.sql
    |-- contest_db-0800.sql
    |-- contest_db-0830.sql
    |-- contest_db-0900.sql
    |-- contest_db-0930.sql
    |-- contest_db-1000.sql
    |-- contest_db-1030.sql
    |-- contest_db-1100.sql
    |-- contest_db-1130.sql
    `-- contest_db-1200.sql