List:Replication« Previous MessageNext Message »
From:shawn l.green Date:February 19 2014 2:56pm
Subject:Re: Mysql replication setup
View as plain text  
Hello Johan,

On 2/18/2014 7:00 AM, Johan De Meersman wrote:
>
>
> ----- Original Message -----
>> From: "shawn l.green" <shawn.l.green@stripped>
>> Subject: Re: Mysql replication setup
>>
>> The only thing I would add to this is to have your master operating with
>> --binlog-format=ROW.  This way you are not relying on missing tables in
>> your partially-migrated schema to be present in order to execute any SQL
>> commands as you would get if you had --binlog-format=STATEMENT or MIXED.
>
> Shawn,
>
> Could you expand a bit on that? It sounds like you're saying FORMAT=ROW will
> magically create the tables (or, possibly, ignore nonexisting tables)?
>

Consider this case.

You have 15 tables in a database (table1... table15).  At semi-regular 
intervals, you use the data from tables 13-15 to create aggregate 
statistics in table 1.

INSERT table1 (<column list>)
SELECT <columns>
FROM table13
INNER JOIN table14
    ON ...
WHERE ...
GROUP BY ...

(
it doesn't need to be an aggregating query, any table-to-table 
dependency would work. example

UPDATE tbl1 INNER JOIN tbl2 on tbl2.pk = tbl1.pk SET tbl1.counter = 
tbl1.counter + tbl2.counter
)

Let's imagine that we are only partially through our single-table 
migration process. After day 1, tables 1-6 are now present on the slave. 
Someone performs my example INSERT command on the master and if the 
master is recording events using --binlog-format=STATEMENT, then that 
will be the command written to the binary log.

When it arrives at the other end, the slave will be attempting to 
execute an INSERT command using data from tables it does not have. 
Table13 does not yet exist on the slave so the source of the data for 
the INSERT is unavailable.

However, if the master is using --binlog-format=ROW, the the only thing 
that was recorded into the binary log was the actual changes to the 
table1 table.  The lack of the source of the data used by the command 
that changed table1 is unimportant because all the data the slave needs 
to keep table1 properly synchronized is physically in the binary log.

Yours,

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Mysql replication setupBalogh Péter17 Feb 2014
  • Re: Mysql replication setupshawn l.green17 Feb 2014
    • Re: Mysql replication setupJohan De Meersman18 Feb 2014
      • Re: Mysql replication setupshawn l.green19 Feb 2014
        • Re: Mysql replication setupJohan De Meersman19 Feb 2014
          • Re: Mysql replication setupshawn l.green19 Feb 2014
            • Re: Mysql replication setupJohan De Meersman19 Feb 2014
  • Re: Mysql replication setupJohan De Meersman18 Feb 2014
Re: Mysql replication setupJohan De Meersman19 Feb 2014
Re: Mysql replication setupBalogh Péter23 Feb 2014