Create MySQL Database Backup Every Half an Hour

For a new customer, i need to store a backup of her MySql Database every Half an Hour so i wrote a batch script for do it.

I need to keep all backups for about 5 days, older than can be deleted.

I prefer to compress every backup for a reason of low disk space.

Here you are:

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.sh :

#!/bin/sh
# Scripts to create mysql backup every half and hour
 
# Confiration value
mysql_host=""
mysql_database=""
mysql_username=""
mysql_password=''
backup_path=/backup/mysql
expired=5 #how many days before the backup directory will be removed
 
today=`date +%Y-%m-%d`
sql_file=$backup_path/$today/$mysql_database-`date +%H%M`.sql
tar_file=$backup_path/$today/$mysql_database-`date +%H%M`.tar.gz
 
if [ ! -d $backup_path/$today ]
then
 mkdir -p $backup_path/$today
 /usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database > $sql_file
 tar zcf $tar_file $sql_file
 rm $sql_file
else
 /usr/bin/mysqldump -h $mysql_host -u $mysql_username -p$mysql_password $mysql_database > $sql_file
 tar zcf $tar_file $sql_file
 rm $sql_file
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="yourdb_db"
mysql_username="yourdb_user"
mysql_password='yourdb_password'

4. Create a cron job to execute this task every half and hour. Open crontab -e and add following line:

*/30 * * * * /home/scripts/mysqlbackup_30min.sh

5. Restart cron daemon:

$ service cron restart

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

$ tree /backup/mysql
.
|-- 2014-09-27
|   |-- yourdb_db-1930.tar.gz
|   |-- yourdb_db-2000.tar.gz
|   |-- yourdb_db-2030.tar.gz
|   |-- yourdb_db-2100.tar.gz
|   |-- yourdb_db-2130.tar.gz
|   |-- yourdb_db-2200.tar.gz
|   |-- yourdb_db-2230.tar.gz
|   |-- yourdb_db-2300.tar.gz
|   `-- yourdb_db-2330.tar.gz
`-- 2014-09-28
    |-- yourdb_db-0030.tar.gz
    |-- yourdb_db-0100.tar.gz
    |-- yourdb_db-0130.tar.gz
    |-- yourdb_db-0200.tar.gz
    |-- yourdb_db-0230.tar.gz
    |-- yourdb_db-0300.tar.gz
    |-- yourdb_db-0330.tar.gz
    |-- yourdb_db-0400.tar.gz
    |-- yourdb_db-0430.tar.gz
    |-- yourdb_db-0500.tar.gz
    |-- yourdb_db-0530.tar.gz
    |-- yourdb_db-0600.tar.gz
    |-- yourdb_db-0630.tar.gz
    |-- yourdb_db-0700.tar.gz
    |-- yourdb_db-0730.tar.gz
    |-- yourdb_db-0800.tar.gz
    |-- yourdb_db-0830.tar.gz
    |-- yourdb_db-0900.tar.gz
    |-- yourdb_db-0930.tar.gz
    |-- yourdb_db-1000.tar.gz
    |-- yourdb_db-1030.tar.gz
    |-- yourdb_db-1100.tar.gz
    |-- yourdb_db-1130.tar.gz
    `-- yourdb_db-1200.tar.gz