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_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
_______________________________________________
CentOS mailing list
CentOS@xxxxxxxxxx
http://lists.centos.org/mailman/listinfo/centos
_______________________________________________
CentOS mailing list
CentOS@xxxxxxxxxx
http://lists.centos.org/mailman/listinfo/centos
