On Thu, Nov 1, 2012 at 11:34 AM, Rick James <rjames@stripped> wrote:
> Full backup:
> * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as
> * 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.reindl@stripped]
> > Sent: Thursday, November 01, 2012 4:47 AM
> > To: mysql@stripped
> > 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