[CentOS] mysql

[nate]

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.

> /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, 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.

> 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_070501.log

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