My previous post ran through the steps to install, configure and secure MySQL on CentOS. This post will run through some steps to keep your MySQL installation backed up.
First up, login as root and create a location to store local backups as well as a location to store our backup scripts:
- mkdir /home/backups
- mkdir /home/backups/mysql
- mkdir /home/scripts
We don’t need to reinvent the wheel and there are excellent backup scripts available with regular backup rotation features built in, freely available on the Internet:
- cd /home/scripts
- wget “http://sourceforge.net/projects/automysqlbackup/files/AutoMySQLBackup/AutoMySQLBackup
- mv automysqlbackup-2.5.1-01.sh mysql_backup_all_daily.sh
- chmod u+x *.sh
The last two commands above rename the backup script to something a little more friendly, and then set the required permissions for the script to be executed.
We can now customise the script to connect to our MySQL instance and back up the databases:
- vi mysql_backup_all_daily.sh
The above configurations will essentially back up the local MySQL instance to /home/backups/mysql and then send an email to you, informing you of the completion success/failure of the backup. The script has notes inside that will allow you to customise more features such as emailing you the backups, selecting databases to backup, etc.
Final step is to now schedule the script to run daily at 21:30:
- vi /etc/crontab
- 30 21 * * * root /home/scripts/mysql_backup_all_daily.sh
The script will retain a backup for every day, a week and a month, and will rotate these backups for you. Nice!
You can run this script from a remote backup server, but you will need to ensure you open the necessary firewall ports and use the mysql-public account for remote access.
Leave a comment
A persistently tormenting person, force, or passion: The demon of drug addiction;
One who is extremely zealous, skillful, or diligent: Worked away like a demon;