一、MySQL大事务阻塞主从同步
MySQL大事务阻塞主从同步
数据库fmb_new中fmb_admin_log表数据量太大，需要清除2017年以前的数据，清除前创建备份表。
复制结构：
189（M） -> 190(S1) -> 179(S2) -> 202(S3)
问题说明：
开始时间：2017-04-27 12:11:20
结束时间：2017-04-27 12:31:14
持续时间：1184 秒
说 明：这段时间的一部分读操作的结果不正常，客户下单后没办法看不到订单。
问题原因：
Master使用创建冗余表的方式进行备份，插入的数据量330w左右，字段包含text，binlog_format=‘MIXED’，导致主从复制卡住（MySQL复制为串行）
MySQL的binlog格式为MIXED、ROW、STATEMENT的区别：
举例：insert into t01_s select * from t01 where id <11，使用mysqlbinlog解析binlog
STATEMENT格式：insert into t01_s select * from t01 where id <11 #只记录一条
MIXED格式 ：insert into t01_m select * from t01 where id <11 #只记录一条
ROW格式 ：如下可以看到 #每行数据记录一条
### INSERT INTO `raw`.`t01_r`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='2017-04-26 17:11:26' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### INSERT INTO `raw`.`t01_r`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='2017-04-26 17:11:28' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
正式环境master使用的MIXED，推荐使用ROW格式
问题排除：
从库（S1）等待事务执行完毕。
从库（S2）跳过事务，避免延迟继续增长
从库（S3）跳过事务，避免延迟继续增长
1、==============189-Master
1.1)备份fmb_admin_log表2017年以前的数据到新表fmb_admin_log_bak2015
mysql> create table fmb_admin_log_bak2015 like fmb_admin_log ; #创建备份表
mysql> insert into fmb_admin_log_bak2015 select * from fmb_admin_log order by id asc limit 1; #测试插入数据
mysql> truncate table fmb_admin_log_bak2015; #删除测试数据
mysql> insert into fmb_admin_log_bak2015 select * from fmb_admin_log where id <=9313459; #正式插入数据
1.2)查看事务情况：
mysql> select * from information_schema.innodb_trx \G
*************************** 52. row ***************************
trx_id: 1815CD474
trx_state: RUNNING
trx_started: 2017-04-27 11:54:42 #事务开始时间
trx_query: insert into fmb_admin_log_bak2015 select * from fmb_admin_log where id <=9313459 #执行语句
trx_operation_state: NULL
trx_tables_in_use: 2 #正在使用表的数量
trx_tables_locked: 2 #当前lock表的数量
trx_rows_modified: 3306936 #锁定的数据行数，可以理解为已插入的行数
#此时是锁定的备份表fmb_admin_log_bak2015 和当前fmb_admin_log选择的行锁
2、==============190-S1
查看slave端延迟情况：延迟1184秒
mysql> show slave status \G
*************************** 1. row ***************************
Master_Host: 192.168.0.189
Master_Log_File: mysql-bin.002265
Read_Master_Log_Pos: 9644436
Relay_Master_Log_File: mysql-bin.002265 #执行的binlog的文件
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 2544022 #执行的binlog的位置，该值不变量说明该事务较大
Seconds_Behind_Master: 1184 #延迟1184秒
#结合上面参数情况，可确定S1在执行插入备份的事务，该事务较大，阻塞复制
3、==============179-S2
通过show slave status语句确定在执行insert事务，避免延迟时间持续增长，跳过决定跳过该事务
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务
mysql>slave start
4、==============202-S3
179忽略该事务，202同样忽略
