From: Rick James
Date: November 1 2012 3:34pm
Subject: RE: Mysql backup for large databases
List-Archive: http://lists.mysql.com/mysql/228563
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1490E7A295@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 resta=
rt of mysqld to assure that anything cached is sync'd to disk.  After the s=
napshot 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 sy=
stem on this or some other server.

For partial backups...
* Xtrabackup - already discussed
* Slave -- You are free to construct whatever slicing and dicing, even chan=
ging 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 PA=
RTITION (instantaneous).  Restoring is no better than LOAD DATA.  In the ne=
ar 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
>=20
>=20
>=20
> 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.
>=20
> * setup a master/slave configuration
> * stop the slave
> * rsync the raw datadir to whatever backup-medium/location
> * start the salve again
>=20