Databases Reference
In-Depth Information
248 insert into test(a) values(1)
278 C'G
325 std
335 test
340 insert into test(a) values(2)
370 C'G
417 std
427 test
432 drop table test
448 D'G
474 mysql-bin.000114
There's a pretty recognizable pattern that should allow you to locate the beginnings of
events. Notice that the strings that end with
'G
are located one byte after the beginning
of the log event. They are part of the fixed-length log event header.
The exact value will vary from server to server, so your results will vary depending on
the server whose log you're examining. With a little sleuthing, though, you should be
able to find the pattern in your binary log and determine the next intact log event's
offset. You can then try to skip past the bad event(s) with the
--start-position
argument
to
mysqlbinlog
, or use the
MASTER_LOG_POS
parameter to
CHANGE MASTER TO
.
Using Nontransactional Tables
If all goes well, statement-based replication usually works fine with nontransactional
tables. However, if there's an error in an update to a nontransactional table, such as
the statement being killed before it is complete, the master and replica will end up with
different data.
For example, suppose you're updating a MyISAM table with 100 rows. If the statement
updates 50 of the rows and then someone kills it, what happens? Half of the rows will
have been changed, but not the other half. Replication is bound to get out of sync as a
result, because the statement will replay on the replica and change all 100 rows.
(MySQL will then notice that the statement caused an error on the master but not the
replica, and replication will stop with an error.)
If you're using MyISAM tables, be sure to run
STOP SLAVE
before stopping the MySQL
server, or the shutdown will kill any running queries (including any incomplete update
statements). Transactional storage engines don't have this problem. If you're using
transactional tables, the failed update will be rolled back on the master and not logged
to the binary log.
Mixing Transactional and Nontransactional Tables
When you use a transactional storage engine, MySQL doesn't log the statements you
execute to the binary log until the transactions commit. Thus, if a transaction is rolled
back, MySQL won't log the statements, so they won't get replayed on the replica.
