Suggestions To Nighly MySQL Dumps?
 

I have a MySQL 5.1 production server & a few critical MySQL servers but I would like to know the best suggestions for backing up my databases? I can manually do a 'mysqldump' on the server and copy them to off site storage but I really don't know what the best thing to do would be. Anyone who admin's a MySQL server offer any assistance on the best way to back-up my databases via MySQL?

Thanks for any assistance. I would like to keep this as easy as possible. Limit the backups to simply using just Linux & MySQL w/o any administration GUI's or applications beyond what I already have installed.

Thanks!

mysqldumps are the easiest way to go, without spending $$$. You can probably get MySQl agents for many backup programs (like Tivoli, Arcserve, etc.), and get more flexibility, but if you just want quick 'hot' backups, mysqldump is the way to go. Easy to cron, too, and you can even put the offsite copy command into the same shell script.

Actually, take a look at ZRM for MySQL (Zmanda Recovery Manager for MySQL). There is a community edition that you can download and use for free and an enterprise edition that you can get support contracts with. It will manage your backups for all of your MySQL databases and servers, and will do both full and incremental (using a transaction log) backups. You can automate the whole thing and get email reports. Then you can work that into your enterprise tape backup scheme if you have that sort of thing, or just rsync your backup directory off site.

We have 5 production servers and a few backup/test servers. All production servers are backed up using mysqldump. It's simple, easy to script and works (yes we also test our recovery procedures on the backup servers). Don't forget the conf's and Modules, especially Apache, MySQL and PHP, 'cos it's a real pain at 4 in the morning to try to remember how the servers were configured.

Just a quick added note about ZRM for MySQL, since I don't think it was clear in my post above -- ZRM has extensive capabilities all based on the standard MySQL tools -- http://wiki.zmanda.com/index.php/Cap..._ZRM_for_MySQL.

ZRM will use mysqldump, mysqlhotcopy, MySQL replication, snapshots (LVM or ZFS), and will do the appropriate thing depending on which storage engine you use for MySQL. It can do point in time recovery using MySQL's binary logs. So, if an operator or sysadmin blows it, and messes up the database, ZRM can recover to the point in time just before the error (assuming you have configured the use of binary logs). It can also use ssl connections to backup remote MySQL servers.

So, it is perl scripted mysqldump and a lot more.

Nicely scripted mysql dump: http://www.ameir.net/blog/index.php?...Cron-v2.1.html with rsnapshot (http://rsnapshot.org/) getting the dumps off the server efficiently.