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.
> 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>)
INNER JOIN table14
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.
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN