List:General Discussion« Previous MessageNext Message »
From:Rick James Date:November 1 2012 3:34pm
Subject:RE: Mysql backup for large databases
View as plain text  
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.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
> 

Thread
Mysql backup for large databasesMachiel Richards - Gmail1 Nov
  • Re: Mysql backup for large databasesDimitre Radoulov1 Nov
  • Re: Mysql backup for large databasesReindl Harald1 Nov
    • RE: Mysql backup for large databasesRick James1 Nov
      • Re: Mysql backup for large databasesSinger Wang1 Nov
        • Re: Mysql backup for large databasesReindl Harald1 Nov
          • Re: Mysql backup for large databasesSinger Wang1 Nov
            • Re: Mysql backup for large databasesReindl Harald1 Nov
              • Re: Mysql backup for large databasesmachiel.richards1 Nov
  • Re: Mysql backup for large databasesKaren Abgarian1 Nov
    • Re: Mysql backup for large databasesManuel Arostegui2 Nov
  • Re: Mysql backup for large databasesKaren Abgarian2 Nov
Re: Mysql backup for large databasesReindl Harald1 Nov
RE: Mysql backup for large databasesRick James1 Nov