Backup Your MySQL Database Daily!

Posted on 21/05/2014 by sertdemiry in Guides, Linux, Ubuntu // 0 Comments

Here is quick guide to backup your MySQL database daily:

This guide is prepared on Ubuntu 12.04LTS, MySQL 5.5.

Save this script anywhere in your database server:

#!/bin/bash
backup_dir="/home/<span style="color: #ff0000;">username/mysqlbackup</span>"
filename="${backup_dir}/mysql-`hostname`-`eval date +%Y%m%d`.sql.gz"
# Dump the entire MySQL database
/usr/bin/mysqldump --opt --all-databases --add-drop-database -u root -p<span style="color: #ff0000;">YOURPASSWORD</span> | gzip > $filename
# Delete backups older than 7 days
find $backup_dir -ctime +7 -type f -delete

#!/bin/bash

backup_dir="/home/<span style="color: #ff0000;">username/mysqlbackup</span>"

filename="${backup_dir}/mysql-`hostname`-`eval date +%Y%m%d`.sql.gz"

# Dump the entire MySQL database

/usr/bin/mysqldump --opt --all-databases --add-drop-database -u root -p<span style="color: #ff0000;">YOURPASSWORD</span> | gzip > $filename

# Delete backups older than 7 days

find $backup_dir -ctime +7 -type f -delete

* Edit the red texts for your environment.

This script will use /home/username/mysqlbackup folder to save the backup files.
It will delete the backup files which are 7 days old. It will hold 8 backup files.

Make the the script file executable:

chmod 755 backupjob.sql

1	chmod 755 backupjob.sql

Test the script by running manually before adding to your crontab.

./backupjob.sql

1	./backupjob.sql

Add the script to crontab to run it daily:

0 3 * * * root /home/username/backupjob.sql

1	0 3 * * * root /home/username/backupjob.sql

This job will run at 03:00 everyday.