From: Marcus Bointon
Date: April 18 2008 9:41am
Subject: Re: innodb replication
List-Archive: http://lists.mysql.com/replication/1228
Message-Id: <185F7F0E-6026-435E-8B69-32D70173EF84@synchromedia.co.uk>
MIME-Version: 1.0 (Apple Message framework v919.2)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable

On 18 Apr 2008, at 10:27, Ed W wrote:

>> I=B4m going to set up a innodb database replication, and I have some =20=

>> doubts
>> about the backing up the master database step.
>>
>> Normally, I do a "FLUSH TABLES WITH READ LOCK", backup the data =20
>> files (or
>> use mysqldump), get the current log coordinates with "SHOW MASTER =20
>> STATUS"
>> and unlock the tables. Using exactly the same steps work for innodb
>> databases?
>>
>> According to some texts, it seems not, but I haven=B4t found a clear =20=

>> cookbook.
>>
>>
>> Just doing a "mysqldump --master-data --single-transaction" is =20
>> enough to
>> replace the above steps?
>>
>>
>
> How would you put these two things together in the case of a server =20=

> using mixed database table types for a clean backup that can be used =20=

> to restart synchronisation?


You can't. Only InnoDB supports single-transaction, so though it would =20=

still generate a dump file for other table types, you'd have no =20
assurance that they are clean because writes could be happening as you =20=

do it. This is one of the advantages of using innodb.

You can of course do separate dumps for other DBs, and because that =20
wouldn't need to include the innodb tables, it would at least reduce =20
the time that your tables are locked.

You could also look at maatkit's parallel dump/load scripts.

Marcus
--=20
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/