Backup MySQL on CentOS v5 x86/64-bit

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
  VER 2.5/automysqlbackup-2.5.1-01.sh”
• 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

• USERNAME=mysql-local
• PASSWORD=<Password>
• BACKUPDIR=”/home/backups/mysql”
• MAILADDR=”email@domain.com

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.

2 Comments

• 
  Magda
  November 24, 2011 at 05:12

  I hate my life but at least this makes it bearlabe.

  Reply
  • 
    demonRICHARD
    November 24, 2011 at 18:13

    What’s wrong with your life?

    Reply