Pt-online-schema-change have you abused today?

Note: This article from the real production case, thank the Netizen small dolphin to provide, I have to reproduce the fault check.

Scene

In order to tidy up the line of independent table space fragmentation, so the use of Pt-online-schema-change in the slave from the Library, the purpose is to avoid affecting the main Library CPU, maintain a master-slave switchover, and then shrink the table space fragmentation on the main library.

Slave commands executed from the library are as follows:

# pt-online-schema-change-s/tmp/mysql.sock--alter= "Engine=innodb"--no-check-replication-filters-- Recursion-method=none--user=root d=test,t=sbtest--execute

malfunction

After the DBA modifies the table structure, the business party feedback data is not accurate, in the process of troubleshooting found synchronization error 1032.

Analysis

1. the Binlog format for The main library and the slave library is ROW

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/8B/2D/wKiom1hGTjSjAMYEAAAKJHbkX34384.png "title=" Picture 1.png "alt=" Wkiom1hgtjsjamyeaaakjhbkx34384.png "/>

2, Pt-online-schema-change Insert/updete/delete to the temp table _sbtest_new after completion. _sbtest_old cousin, _sbtest_new temporary table changed to original table sbtest _sbtest _old cousin. The procedure is as follows:

altering  ' Test '. ' Sbtest ' ... Creating new table ... Created new table test._sbtest_new ok. Altering new table ... altered  ' test '. ' _sbtest_new '  ok.2016-12-06t12:15:30 creating triggers ... 2016-12-06t12:15:30 created triggers ok.2016-12-06t12:15:30 copying approximately  1099152 rows ... 2016-12-06t12:15:54 copied rows ok.2016-12-06t12:15:54 analyzing new table ... 2016-12-06t12:15:54 swapping tables ... 2016-12-06t12:15:54 swapped original and new tables ok.2016-12-06t12:15:54  Dropping old table ... 2016-12-06t12:15:54 dropped old table  ' test '. ' _sbtest_old '  ok.2016-12-06t12:15:54  dropping triggers ... 2016-12-06t12:15:54 dropped triggers ok. successfully altered  ' test '. ' Sbtest '. 

3. based on the replication of Binlog for row rows, triggers do not work on slave from the library, which results in inconsistent master-slave data. But based on the replication of Binlog for statement statements, the trigger will work from the library slave.

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master does not execute on the slave.

Reference Documents: http://dev.mysql.com/doc/refman/5.7/en/replication-features-triggers.html

note: mixed statement format, but in the following 6 row format

First case: NDB engine, table DML operation Increment, delete, change will be recorded in ROW format.

second case: The UUID () function is included in the SQL statement .

The third case: the Self-growth field is updated.

Fourth case: contains the INSERT DELAYED statement.

Fifth Scenario: User-defined functions are used ( UDF)

The sixth scenario: a temporary table is used.

Reference Documents: https://dev.mysql.com/doc/refman/5.7/en/binary-log-mixed.html

reproduce

1. Main Library create T1 table

CREATE TABLE ' t1 ' (' id ' int (one) not NULL, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8

2. Create a t2 table from a library and create a trigger

CREATE TABLE ' T2 ' (' id ' int (one) not NULL, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8

Trigger

DELIMITER $$ use ' test ' $$ DROP TRIGGER IF EXISTS ' t1_1 ' $$ CREATE TRIGGER ' t1_1 ' after INSERT on ' T1 ' for each ROW B    Egin INSERT into T2 (ID) VALUES (new.id); end;$$ DELIMITER;

3. Main Library Insert

INSERT into T1 VALUES (1), (2), (3); select * from T2;

at this point the T2 table has no data and the trigger does not work.

Conclusion

If you use pt-online-schema-change Modify the table structure to run on the main library, data inconsistencies do not occur. However, if you are running from a library and the Binlog format of the main library is ROW, it will be dangerous.

This article is from the "Spring Yang Technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1879962

Pt-online-schema-change have you abused today?