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

6 thoughts on “Create MySQL Database Backup Every Half an Hour

  1. Thanks for this script.

    It backs up great but folders older than 3 days are not being removed, I checked after 5 days and had 5 days of backups.

    Reply

    1. It will keep the last 5 days of backup. You can check tomorrow that they will remain only 5 folders inside. The script will ONLY delete folder with modified time is higher than 3 days which is 4 days and above. Everyday, the folder will be modified up until 2330 hours. You will notice that there will be 4 folders between 2330 to 0000 hours everyday, and starting at 0000 hours it will create the 5th folder.

      Reply

  2. Hi,

    Could you please let me know which port needs to be opened in iptables in which I am taking backup. If the iptables are running backup is not happening.

    In mysql server I have already opened 3306 port.

    Regards,
    TechPlesk

    Reply

    1. Hi,

      You just need to have MySQL port (3306) open to accomplish this task. What have you specified in “mysql_host=” section? I could not foresee any problem (with iptables) as MySQL will use socket (it won’t connect through TCP) if you specified “localhost”.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *