[CentOS] mysql

[mouss]

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 at centos.org
> http://lists.centos.org/mailman/listinfo/centos