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
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
