Setting up automated backups on Linux for your MySQL backups are quite easy, thanks to a script called automysqlbackup available on sourceforge.net. The configuration of the script is as simple as editing the script and changing a few parameters to reflect the settings on your server.
Firstly, we need to create folders on the server where our backups will be stored. We can later automate the synchronization of this folder with that of another local or remote server using rsync. For this example I have created the folders /backup/db/ to hold the backups on the server. You can create this folder on the server directly or by using SSH or Putty by using the command sudo mkdir -p /backup/db (I am using Ubuntu 10 server for this tutorial) . Next you need to create a directory where your script will reside.
sudo su (Become root, the shell prompt should change from $ to #)
mkdir -p ~/scripts
Now download the automysqlbackup script from here [ EHA users, download the script from here ] into the folder /root/scripts
An easy way of doing it is using wget. Change over to the directory that you created
cd ~/scripts
Now download the file using wget eg.
wget http://space.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%202.5/automysqlbackup-2.5.1-01.sh
Next we make the script executable with the command…
chmod u+rwx automysqlbackup-2.5.1-01.sh
mkdir -p /srv/backup/db [ This is the default location where the backups will be saved ]
Check whether everything is ok, by manually running the script… [Non EHA users, please check the script for configuration information, It is very well commented.]
EHA users ask for a customized copy of the file.
./automysqlbackup-2.5.1-01.sh
Check your backup folder (In this case /srv/backup/db and if all is well you should see a compressed backup of your MySQL databse under the db folder)
Finally we schedule the script to run every 10 minutes using cron…
crontab -e
If it asks you what editor to use, choose nano (unless you fancy something else)
Add the following lines to the file and press Ctrl + X and follow the instructions to save and exit
*/10 * * * * /root/scripts/automysqlbackup-2.5.1-01.sh # (runs only on minutes divisible by 10: 10, 20, 30, etc.)
@daily cp /var/lib/tomcat6/webapps/hms.war /backup/
The frequency depends on how much activity MySQL sees. For our PF data server the interval I’ve set is 120 minutes.
We also need to exit from the root prompt
exit
There are many more advanced notification features such as notification of events by email which I am not covering here.
The Backups folder
Depending on your database size and backup interval, this setup could fill up your hard disk. It is best done by creating your backup folder on a separate hard disk as if it fills up your system partition, your applications on the server will either stall immediately or on the next reboot. The idea is to keep monitoring your backup folder size in relation to the free space on your hard disk and check whether your backups are happening regularly and test its integrity by restoring the backups on another machine.
The syntax for restoring the backups would be as follows where hms_eha_2010-08-26_13h10m.Thursday.sql.gz is the MySQL backup file.
gunzip < hms_eha_2010-08-26_13h10m.Thursday.sql.gz | mysql -uhms_eha -phms_eha hms_eha
Project Page on Sourceforge for automysqlbackup : http://sourceforge.net/projects/automysqlbackup/
Note:[ 8th August 2011 ]
Our server has been running for about a year and 4 months. Our database size has grown too and the gzipped backup file has reached 56 MB. This has also proportionately increased the time and CPU required to dump and gzip the backup file which would cause frustrating sluggishness at the patient counters. To remedy this, we set our backup to take place when the patient load was less.
Currently as development work is still going on and there are issues in the software itself regarding sluggishness, we have scheduled the backup to take place at midnight so that the developers don’t blame the sluggishness on scheduled backups. the syntax for the crontab file is…
@daily /root/scripts/automysqlbackup-2.5.1-01.sh
If you would like to make a comment, please fill out the form below.
