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

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