List:Replication« Previous MessageNext Message »
From:Andrew Garner Date:December 10 2009 7:56pm
Subject:Re: best practice - restore slave
View as plain text  
On Thu, Dec 10, 2009 at 1:49 PM, Marcus Bointon
<marcus@stripped> wrote:
> On 10 Dec 2009, at 20:35, Song Voong wrote:
>
>> My question is while the master is running is it a good ideas to do the
>> dump? Or do I need to stop all activities on master to do the dump?
>
> It's safe if you lock tables while doing the dump, but then clients won't be able to
> write to the DB. If you don't do this, you can end up with inconsistent data as the
> database can change during the dump. Alternatively if you're using innodb tables you can
> use a hot backup system like xtrabackup, which can take a consistent backup without
> blocking write access to the master, and has the added benefit that the restore on the
> slave is more or less instant.
>
> Marcus
> --
> Marcus Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/
> UK resellers of info@hand CRM solutions
> marcus@stripped | http://www.synchromedia.co.uk/

mysqldump --master-data will automatically lock the master to get a
consistent binary log position.   If you use --single-transaction for
InnoDB this will be relatively short (but only safe for innodb),
otherwise this is equivalent to specifying --lock-all-tables which
runs FLUSH TABLES WITH READ LOCK.

~Andrew
Thread
best practice - restore slaveSong Voong10 Dec
  • Re: best practice - restore slaveMarcus Bointon10 Dec
    • Re: best practice - restore slaveAndrew Garner10 Dec