| John R Pierce wrote:

Mad Unix wrote:

...

I need to replicate MySQL DB of mulltiple server on SiteA to my DR-Site Site_B... all DB are alocated on RHEL,SuSE,Centos,Debian, FreeBSD servers. I need a script to take Multiple MySql DataBase Backup and then import to SiteB, the replica can be done as cold or hotbackup and cron it

thats nice. sounds like a reasonable task to undertake.

do you have a question?

Maybe man "magic script" woudl work...

[]s.

However, am thinking to do the following

mysqlhotcopy --allowold --flushlog -u <username> --password=<password> <dbname> /var/backups/mysql/

or

/usr/bin/mysqldump -u#### -p#### --all-databases -a > /var/backups/mysql/$(date +%Y%m%d).sql tar cf - /var/backups/mysql/$(date +%Y%m%d).sql | gzip -c > /misc/backups/MySQL/$(date +%Y%m%d).tar.gz

Any recommendation ,,,,,,,,

On Wed, Sep 3, 2008 at 9:06 PM, Jim Perrin jperrin@gmail.com wrote:

On Wed, Sep 3, 2008 at 2:45 PM, Mad Unix madunix@gmail.com wrote:

...

I need to replicate MySQL DB of mulltiple server on SiteA to my DR-Site Site_B... all DB are alocated on RHEL,SuSE,Centos,Debian, FreeBSD servers. I need a script to take Multiple MySql DataBase Backup and then import to SiteB, the replica can be done as cold or hotbackup and cron it Thanks

I'm sure there are several folks on the list who would be willing to contract out for the creation of such a script. If you're looking for cheaper stuff though, you might want to have a look at maatkit or zmanda.

-- During times of universal deceit, telling the truth becomes a revolutionary act. George Orwell _______________________________________________ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos

nate wrote:

Mad Unix wrote:

...

However, am thinking to do the following

mysqlhotcopy --allowold --flushlog -u <username> --password=<password> <dbname> /var/backups/mysql/

mysqlhotcopy doesn't support InnoDB last I checked(past week). InnoDB is generally the suggested engine to use for MySQL these days, I can't imagine using MyISAM anymore.

agreed.

...

/usr/bin/mysqldump -u#### -p#### --all-databases -a > /var/backups/mysql/$(date +%Y%m%d).sql tar cf - /var/backups/mysql/$(date +%Y%m%d).sql | gzip -c > /misc/backups/MySQL/$(date +%Y%m%d).tar.gz

you should use the lock databases option so you can get a consistent backup,

or the single transaction option (again, with InnoDB).

and better yet, he can first replicate it to another server, and do the dumps there (when he can stop replication while doing the dump).

of course no changes to the DB will be possible during the backup. mysqldump also does not back up user permissions/accounts. So you'll have to re-create those on the other side.

it does if you dump the whole thing.

...

Any recommendation ,,,,,,,,

For InnoDB there is a commercial hot backup app for it, which is about $1500/server for a perpetual license:

http://www.innodb.com/hot-backup/

What I did at my last company was shut down the standby mysql database, snapshot it from the storage array, start up the database(this entire process takes about 35 seconds), export the snapshotted volumes to a 3rd server and back it up there, then delete the snapshots after it was done.

sample log file: http://portal.aphroland.org/~aphro/san/mysql-backup-prod-backup-2_20080319_0...

It worked beautifully, reduced standby downtime from ~3 hours to ~35 seconds, and allowed the standby dbs to actually be standby dbs. Also allowed consolidation of several different backups onto a single system.

If you don't yet have a standby database and want to build one you could do something similar, take the primary down for a few seconds to snapshot it, and then put the snapshot on another system for restoration onto a fresh volume to populate the initial standby.

Of course to do that you need good storage infrastructure, not sure what yours looks like.

nate

---

CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos