Backup Your MySQL Database Daily!

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:
1 2 3 4 5 6 7 |
#!/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:
1 |
chmod 755 backupjob.sql |
Test the script by running manually before adding to your crontab.
1 |
./backupjob.sql |
Add the script to crontab to run it daily:
1 |
0 3 * * * root /home/username/backupjob.sql |
This job will run at 03:00 everyday.