How many times have you come across situations when corrupt or old database eventually lost you an entire list of leads and emails collected over several years? It is heart breaking indeed. There have also been instances when the monthly backup got overwritten by the latest one and there was no way to return to the old settings. It is best to have an easy, alternate backup method in place in order to prevent losing those precious data on your server.
You can automate the backup process by making a small shell script which will create a daily backup file without overwriting the older backup .

#!/bin/sh
date=`date -I`
mysqldump –all-databases | gzip > /var/backup/backup-$date.sql.gz
Change the above directory to your backup drive location.

1) Save above script to a file eg: /root/dailybackup.sh
2) Make the file executable :- chmod +x /root/dailybackup.sh
3) Add following line in cron to take backup daily at 01:00 am

0 1 * * * /root/dailybackup.sh

Since mysqldump sends its output to the console, we can pipe the output through gzip or bzip2 and send the compressed dump to the backup file. Here’s another way of doing it with bzip2

mysqldump –all-databases -p | bzip2 -c >databasebackup.sql.bz2

Enter password:
If you wish to transfer the file to your other server and decompress it , say my backup file is

backup-2010-02-14.sql.bz

You can decompress as follows :-

bzip2 -d backup-2010-02-14.sql.bz

It will be now in the form

backup-2010-02-14.sql

Restore your backup using the mysql command:

mysql -p < backup-2010-02-14.sql

Done!

Tags: backup your data, logicsupport review, mysql, server management, technical support

This entry was posted on Wednesday, April 21st, 2010 at 9:58 am and is filed under Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.