include/master-slave.inc [connection master] # # STEP 1 - CREATE AND REPLICATE TEST TABLES # connection master; *** Create test tables <<<<<<< HEAD show variables like 'binlog_format%'; ======= show global variables like '%binlog_format%'; >>>>>>> merge-perfschema-5.7 Variable_name Value binlog_format MIXED drop table if exists test.marker; select thread_id into @my_thread_id from performance_schema.threads where processlist_id = connection_id(); create table test.marker(s1 int) engine=innodb; connection slave; *** Clear statement events # # STEP 2 - REPLICATE ONE ROW ON MASTER TO GET REPLICATION THREAD ID ON SLAVE # connection master; insert into test.marker values (0); connection slave; *** Verify row, get replication thread id, clear statement events select thread_id into @slave_thread_id from performance_schema.events_statements_history where sql_text like '%marker%'; *** Verify row inserted on master was replicated select count(*) = 1 as 'Expect 1' from test.marker; Expect 1 1 *** Clear statement events # # STEP 3 - PERFORM DML STATEMENTS ON MASTER # connection master; <<<<<<< HEAD show variables like 'binlog_format%'; ======= show global variables like '%binlog_format%'; >>>>>>> merge-perfschema-5.7 Variable_name Value binlog_format MIXED *** Clear statement events *** Create/drop table, create/drop database create database marker1_db; create database marker2_db; create table marker1_db.table1 (s1 int) engine=innodb; create table marker2_db.table1 (s1 int) engine=innodb; create table marker2_db.table2 (s1 int) engine=innodb; *** Transaction start transaction; insert into marker1_db.table1 values (1), (2), (3); insert into marker2_db.table1 values (1), (2), (3); commit; *** Alter alter table marker1_db.table1 add column (s2 varchar(32)); *** Insert, Update start transaction; insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six'); update marker1_db.table1 set s1 = s1 + 1; commit; *** Rollback start transaction; insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine'); rollback; *** Autocommit, Delete, Drop delete from marker1_db.table1 where s1 > 4; drop table marker2_db.table1; drop database marker2_db; *** Examine statements events that will be compared on the slave select thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text from performance_schema.events_statements_history_long where sql_text like '%marker%' order by event_id; thread_id event_id event_name current_schema digest_text sql_text [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker1_db` create database marker1_db [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker2_db` create database marker2_db [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker1_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker1_db.table1 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table1 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table2` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table2 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3) [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3) [THREAD_ID] [EVENT_ID] statement/sql/alter_table test ALTER TABLE `marker1_db` . `table1` ADD COLUMN ( `s2` VARCHARACTER (?) ) alter table marker1_db.table1 add column (s2 varchar(32)) [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six') [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine') [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` drop table marker2_db.table1 [THREAD_ID] [EVENT_ID] statement/sql/drop_db test DROP SCHEMA `marker2_db` drop database marker2_db # # STEP 4 - REPLICATE STATEMENT EVENTS ON MASTER TO SLAVE # *** Store statement events in holding table, then replicate # # Create table to hold statement events for later comparison on the slave # create table test.master_events_statements_history_long as (select thread_id, event_id, event_name, sql_text, digest, digest_text, current_schema, rows_affected from performance_schema.events_statements_history_long where (thread_id=@my_thread_id and digest_text like '%marker%')); # # STEP 5 - VERIFY DML AND DDL STATEMENT EVENTS ON SLAVE # connection slave; *** List statement events from master select thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text from master_events_statements_history_long order by event_id; thread_id event_id event_name current_schema digest_text sql_text [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker1_db` create database marker1_db [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker2_db` create database marker2_db [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker1_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker1_db.table1 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table1 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table2` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table2 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3) [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3) [THREAD_ID] [EVENT_ID] statement/sql/alter_table test ALTER TABLE `marker1_db` . `table1` ADD COLUMN ( `s2` VARCHARACTER (?) ) alter table marker1_db.table1 add column (s2 varchar(32)) [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six') [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine') [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` drop table marker2_db.table1 [THREAD_ID] [EVENT_ID] statement/sql/drop_db test DROP SCHEMA `marker2_db` drop database marker2_db *** List statement events on slave select thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text from performance_schema.events_statements_history_long where thread_id = @slave_thread_id and sql_text like '%marker%' order by event_id; thread_id event_id event_name current_schema digest_text sql_text [THREAD_ID] [EVENT_ID] statement/sql/create_db marker1_db CREATE SCHEMA `marker1_db` create database marker1_db [THREAD_ID] [EVENT_ID] statement/sql/create_db marker2_db CREATE SCHEMA `marker2_db` create database marker2_db [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker1_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker1_db.table1 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table1 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table2` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table2 (s1 int) engine=innodb [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3) [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3) [THREAD_ID] [EVENT_ID] statement/sql/alter_table test ALTER TABLE `marker1_db` . `table1` ADD COLUMN ( `s2` VARCHARACTER (?) ) alter table marker1_db.table1 add column (s2 varchar(32)) [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six') [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1 [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` DROP TABLE `marker2_db`.`table1` /* generated by server */ [THREAD_ID] [EVENT_ID] statement/sql/drop_db marker2_db DROP SCHEMA `marker2_db` drop database marker2_db *** Compare master and slave events *** Event name comparison - expect 0 mismatches select thread_id, event_id, event_name, digest_text, sql_text from performance_schema.events_statements_history_long t1 where t1.thread_id = @slave_thread_id and sql_text like '%marker%' and not exists (select * from master_events_statements_history_long t2 where t2.event_name = t1.event_name); thread_id event_id event_name digest_text sql_text *** Statement digest comparison - expect 1 mismatch for DROP TABLE select thread_id, event_id, event_name, digest, digest_text, sql_text from performance_schema.events_statements_history_long t1 where t1.thread_id = @slave_thread_id and sql_text like '%marker%' and not exists (select * from master_events_statements_history_long t2 where t2.digest = t1.digest); thread_id event_id event_name digest digest_text sql_text # # STEP 6 - DISABLE REPLICATED STATEMENT EVENTS ON SLAVE # update performance_schema.setup_instruments set enabled='no', timed='no' where name like '%statement/abstract/relay_log%'; select * from performance_schema.setup_instruments where name like '%statement/abstract/relay_log%'; NAME ENABLED TIMED statement/abstract/relay_log NO NO # # STEP 7 - UPDATE TABLES ON MASTER, REPLICATE # connection master; *** Clear statement events *** Update some tables, then replicate insert into marker1_db.table1 values (999, '999'), (998, '998'), (997, '997'); # # STEP 8 - VERIFY TABLE UPDATES FROM MASTER, EXPECT NO STATEMENT EVENTS ON SLAVE # connection slave; *** Confirm rows were replicated select * from marker1_db.table1 where s1 > 900 order by s1; s1 s2 997 997 998 998 999 999 *** Confirm that are no statements events from the replication thread select * from performance_schema.events_statements_history_long where thread_id = @slave_thread_id; THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT LOCK_TIME SQL_TEXT DIGEST DIGEST_TEXT CURRENT_SCHEMA OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OBJECT_INSTANCE_BEGIN MYSQL_ERRNO RETURNED_SQLSTATE MESSAGE_TEXT ERRORS WARNINGS ROWS_AFFECTED ROWS_SENT ROWS_EXAMINED CREATED_TMP_DISK_TABLES CREATED_TMP_TABLES SELECT_FULL_JOIN SELECT_FULL_RANGE_JOIN SELECT_RANGE SELECT_RANGE_CHECK SELECT_SCAN SORT_MERGE_PASSES SORT_RANGE SORT_ROWS SORT_SCAN NO_INDEX_USED NO_GOOD_INDEX_USED NESTING_EVENT_ID NESTING_EVENT_TYPE NESTING_EVENT_LEVEL # # STEP 9 - CLEAN UP # include/rpl_end.inc