On Thu, Nov 1, 2012 at 11:34 AM, Rick James <rja...@yahoo-inc.com> wrote:
> Full backup: > * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as > mentioned)) > * Slave (Backup: zero impact on Master -- once replication is set up) > * LVM -- a minute of server down; see below > Why do you need downtime? > > Full restore: > * Xtrabackup - Takes time > * Slave - minute(s) to "failover", mostly dealing with clients pointing to > the new "master". > * LVM -- a minute? see below > > With LVM you are taking a filesystem snapshot. This requires a brief > restart of mysqld to assure that anything cached is sync'd to disk. After > the snapshot is taken, you are at liberty to copy the snapshot to somewhere > else. (This must be done before you fill up the volume used for > copy-on-write stuff, etc.) The snapshot can be used to "instantly" restore > the entire system on this or some other server. > > For partial backups... > * Xtrabackup - already discussed > * Slave -- You are free to construct whatever slicing and dicing, even > changing engines to MyISAM and copying files. > * LVM -- probably not useful. > > Consider using PARTITIONing. With it, you could split up a table > according to time, copy (row by row) the oldest partition to somewhere > else, DROP PARTITION (instantaneous). Restoring is no better than LOAD > DATA. In the near future (5.6.x?), you can disconnect a partition from a > table and move it to another table; this will greatly speed up "archiving". > > > -----Original Message----- > > From: Reindl Harald [mailto:h.rei...@thelounge.net] > > Sent: Thursday, November 01, 2012 4:47 AM > > To: mysql@lists.mysql.com > > Subject: Re: Mysql backup for large databases > > > > > > > > Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail: > > > Using mysqldump and restores on an 80-100GB database seems a bit > > > unpractical as the restore times seems to get quite long as well as the > > backup times. > > > > * setup a master/slave configuration > > * stop the slave > > * rsync the raw datadir to whatever backup-medium/location > > * start the salve again > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
