MySQL Backup Recommendation

[Carlosinfl]

I have a MySQL server that has about 4 very important but relatively small (by today's standards) databases. They all run on CentOS 5 Linux and would like to know if anyone has a recommended simple script / procedure for doing a nightly or weekly backup on the 4 databases? I generally do a MySQL dump manually on Friday evening before I leave for the day and I realize there has to be a much better way to go about this from a professional stand point.

Here are the commands I run every week but would like to automate somehow and perhaps move to nightly:

mysqldump -u root -p mysql > mysql.sql
mysqldump -u root -p cal > mrbs.sql
mysqldump -u root -p webmail > webmail.sql
mysqldump -u root -p wiki > wiki.sql

 

[keenan]

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

If your storage backend is set up using LVM and you have some free extents on your VG I would recommend using snapshotting. It requires only a very brief lock to flush the tables and create a snapshot, you can then copy/rdiff/whatever to your backup media at your leisure without affecting DB performance much. The read locks required by most other methods can cause queries to stall for a long time. You can also use a tool like rdiff-backup or duplicity to create incremental backups in a storage-efficient manner.

Getting into larger-scale things, you can also replicate your DB to a secondary machine and do all your backups directly on that box, so you don't need to affect the performance of the live system at all, if that is important.

Whenever I have a need for this I generally use a custom backup script, though I have also used backupninja with some success.

I will NB my post saying that I try to avoid MySQL these days in lieu of Postgres, but the issues are much the same and the solutions similar as well.

 

[longblock454]

If you send up scripting you might also want to include the date:

mysqldump -u root -psomepassword --all-databases > /home/bart/webmail`date +%F_%T`.sql

Or some variation.

 

[Crax]

I've done something similar to what longblock454 suggested via a cron job to back them up and then transfer them to an offsite ftp

 

[`danny]

Here's something I created for my old job last year. It would be really easy to add email notifications and what not.

http://pastebin.com/w97sckqP

 

[The_Mage18]

Here's something I created for my old job last year. It would be really easy to add email notifications and what not.

http://pastebin.com/w97sckqP

Very nicely written script. It definitly would be a great one to use in a cron job.