-- 关闭GTID mysql> show global variables like '%gtid%'; +----------------------------------------+----------+ | Variable_name | Value | +----------------------------------------+----------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | OFF | | gtid_executed | | | gtid_mode | OFF | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +----------------------------------------+----------+
-- 禁用read_only mysql> show global variables like 'read_only'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | read_only | OFF | +------------------+-------+
-- 设置row模式 mysql> show global variables like 'binlog_format'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | binlog_format | ROW | +---------------+----------+
在主库创建两张表,一张是事务表,另一张为非事务表,然后往各表中写入 id=1 的记录。
1 2 3 4 5 6 7
-- 主库创建测试表 createtable replica_innodb (id int, msg char(32)) engine=innodb; createtable replica_myisam (id int, msg char(32)) engine=myisam;
setsql_log_bin=0; delete from replica_myisam where id=1; setsql_log_bin=1;
(2)主库更新 replica_myisam 非事务表 id=1 的记录,并新增数据到两张表中
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
-- 编写insert存储过程 delimiter $$ createprocedure p_insert() begin declare i int; set i = 2; while i < 6do insertinto replica_innodb values (i, concat('innodb_', repeat(0, 2), i)); insertinto replica_myisam values (i, concat('myisam_', repeat(0, 2), i)); set i = i + 1; endwhile; end$$ delimiter ;
stop slave; start slave; show global variables like 'sql_slave_skip_counter'\G *************************** 1. row *************************** Variable_name: sql_slave_skip_counter Value: 0
方式二:手工设置为零,立即生效
1 2 3 4 5 6 7
stop slave; set global sql_slave_skip_counter=0; start slave; show variables like 'sql_slave_skip_counter'\G *************************** 1. row *************************** Variable_name: sql_slave_skip_counter Value: 0