MySQL Backup Recommendation

carlosinfl · 04-27-2010, 09:14 AM

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:

Code:

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

troop · 04-27-2010, 09:28 AM

http://sourceforge.net/projects/automysqlbackup/
+ cron
e.g.

Code:

15 4  * * 6 /path/to/automysqlbackup.pl

carlosinfl · 04-27-2010, 11:28 AM

Thank you very much. I will test the script and see how it works.

choogendyk · 04-27-2010, 08:02 PM

Another possibility is ZRM for MySQL. You can also get that from sourceforge, MySQL.com, or Sun (now Oracle). Probably the easiest is to download the community edition from Zmanda (the first link above).

There are lots of howtos if you google zrm for mysql. I found an Ubuntu howto on howtoforge, and there's a quickstart on the Zmanda wiki.

(by the way, I like your owl sig.

)

carlosinfl · 04-29-2010, 07:51 AM

Thanks all. I downloaded the 1st suggested script and it worked perfectly however now I am a little nervous about having a MySQL root password in plain text right in the script configuration file. Should I create a generic user in MySQL just for backup roles and privileges? I don't know enough about GRANT OPTIONS or backups to know if I can create a dummy user and give him only what he requires to backup the databases safely or if this script requires the root or someone with GRANT ALL OPTIONS enabled.

choogendyk · 04-29-2010, 08:58 AM

Here is an example of details for setting up a MySQL backup user from the ZRM for MySQL wiki -- http://wiki.zmanda.com/index.php/Pre...QL_Backup_User. That ought to be generic to whatever script you use for backup. This allows you to set it up so that backups are done with read only access, and recovery requires you to provide the password.