Hi,

I am using Mysql 5.1, all my tables are partitioned by month. i want to know how to backup large database, where a we have 2-2.5 GB daily increment in size of table, and the whole size of single table containing 70-75GB. which tool will be the best to have backup on tape as well as on disk backup.


thanks,

Here's a worthwhile read: http://www.zmanda.com/quick-backup-setup.html

To spell it out a bit more, you have two steps. One is to manage the dumping of your database to disk files. The other to to back those dumps up to tape along with anything else you might want.

http://www.zmanda.com/backup-mysql.html will manage the MySQL dumps.

http://amanda.zmanda.com/ will handle general network backups and backup to tape.

According to Zamanda ZRM ,

if it performs backup via mysqldum then i think its hard to think about 300GB backup!!!! whats your suggestion, it can be but a single backup will take 3+ days and will generate a ASCII file.

if its sanpshot based--- how it can be done if you dont using LVM??

Is Amanda and Zamanda are same ?,,, if yes then i used it first time but it was so hard to implement i had some issue when i was trying to recover.

thanks all of you.

Lot of questions. As you quoted regarding ZRM, "you can override and select yourself what tool to use." If you want to use snapshots, then you have to use a system that is capable of doing so. You would also have to lock and flush all of MySQL before doing a snapshot. Then unlock and proceed to backup the snapshot. It reduces the downtime while maintaining the integrity of the backup. If you don't lock and flush, then you can't guarantee the integrity.

When you're dealing with large volume data, you have to tune your systems as well as each of your tools. 3+ days might mean you need faster drives, faster cpu, more memory, tuning of MySQL, etc. It all depends on what you already have and what seems to be the critical factor or bottleneck. ZRM uses native tools. It will typically choose the right ones and can deal with different situations, like InnoDB vs. MyISAM, or multiple databases scheduled for backup at different times, or using transaction logs for incremental backups, etc. You can choose how you want to do it and configure it that way.

Zmanda is a company that provides support contracts for users of Amanda and also funds development of Amanda. They have hired a number of full time programmers and have developed ZRM for MySQL as well. Both products are open source, but Zmanda also has enterprise editions. Code that they develop filters out to the open source edition.

I've been using the open source edition for a few years and have a handful of installations I am responsible for. It can be a bit of work setting it up initially, but is reasonable straightforward for an experienced sysadmin. Once it is configured and running, it basically runs itself, adapting to changes in disk usage and recovering from error situations without any intervention. I routinely recover things for people fairly easily using the command line amrecover. It understands my configuraton, finds the version of the files I want, loads the right tape from the tape library, and recovers the files.

In the case of a database, you have a two step recovery. If you put the disk files onto tape, you have to recover them from tape. Then you have to use ZRM to recover the database from the backups. How you do that depends on how you configured your MySQL backups.

I typically recommend that people use the Quick Start Guide from the wiki to get Amanda installed and running -- http://wiki.zmanda.com/index.php/Quick_start. While you are working through that, you jump out to other pages in the wiki for details and clarification. For ZRM, there is an introduction here http://wiki.zmanda.com/index.php/Introduction that contains a link to a quick start for ZRM.

Gr8 work,
thanks for brief reply.