1.复制中断怎么办?
2.大数据量变更造成的复制延迟如何定位及解决
3.主从复制延迟排查及开发中处理技巧有哪些?

1.复制中断怎么办?

slave: io_thread,sql_thread;

案例演示了从库插入了一条数据,导致主从复制中断,从库不能应用日志了

start slave sql_thread;



create table tb1(
 id int not null auto_increment.
 c1 varchar(64) not null,
 c2 datetime,
 primary key(id)
);

insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());

select * from tb1;

show slave status\G   // 查看从库的gtid
//在从库上插入一条数据
insert into tb1(c1,c2) values(uuid(),now());
show master status;    // 查看从库的gtid,多了一个

select @@server_uuid
可以通过GTID查看是哪个实例写入的数据

//主库在插入数据
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());


//主库同步报错了,sql_thread 为No 报错,1062
show slave status\G
//查看具体出错信息,更具主键可以在从库查到信息
select * from performance_schema.replication_applier_status_by_worker\G;
LAST_ERROR_MESSAGE:

根据主键 查看从库和主库对比数据，

在从库的执行 show master status; 会有两个gtid值
在binlog里面搜索gitd值，可以看到发生的时间
mysqlbinlog -v --base64-output=decode-rows mysql-bin.00001 > 1.sql    //找gtid
mysqlbinlog -v --base64-output=decode-rows mysql-bin.00001 | grep "gtidxxxx";

从库多了一条数据,如何处理?

从库操作,删除多余的数据
set sql_log_bin=0;
delete from tengcent.tb1 where id=5;
set sql_log_bin=1;
start save sql_thread;


1062(主键,唯一索引)

如果在监控上查看从库多了GTID值
从库去掉执行的GTID,8.0才支持

show slave status\G
stop slave sql_thread;
show global variables like '%gtid%';   记录一下GTID
reset master;                这一步操作需要注意，如果有从库就不要做
set global gtid_purged='gtid-XXX';          

现在是一个比较干净的状态

主库在查看后观察从库
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());

show master status

从库操作
start slave sql_thread;
show slave status\G

主从延迟

mysqlbinlog -v --base64-output=decode-rows mysql-bin.00001 > 1.sql
找
original_commit_timestamp       原实例提交时间
immediate_commit_timestamp    本实例提交时间

如果immediate_commit_timestamp - original_commit_timestamp < 0 说明两台服务器的时间不对

1032错误,从库delete一条数据的时候，可以在从库插入一条数据，数据就能继续复制了

从库3308执行，模拟从库删掉一条数据
set sql_log_bin = 0;
show master status;
delete from tb1 where id=10;
show master status;       -- gtid值无变化
set sql_log_bin = 1；
select * from tb1;

主库3306执行，模拟主库正常写入数据
insert into tb1(c1,c2) values(uuid(),now());

主库3306执行，模拟主库更新主键为10的时候，从库复制报错
update tb1 c1='aaa', c2='2019 xxx' where id = 10;


从库3308,备份tb1表
create table tb2 like tb1;
insert into tb2 select * from tb1;
select * from tb2;

从库3308执行，查看错误原因
select * from performance_schema.replication_applier_status_by_worker\G

从库3308执行，在从库插入一条主键为10的数据，数据就能正常复制了
insert into tb1(id,c1) values（10,'');
start slave sql_thread；

1032错误,从库delete一条数据的时候,主库在删同一条数据的时候报错，可以通过跳过事务解决

从库3308执行
delete from tb1 where id=10;
主库3306执行
delete from tb1 where id=10;
从库3308执行
show slave status\G

主库3306执行，模拟主库继续插入数据
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());
insert into tb1(c1,c2) values(uuid(),now());

从库3308执行，跳过一个那个出错的事务
show slave status\G     Executed_Gtid_Set: 已经执行的gtid,在Last_SQL_Error可以看到具体的位置
stop slave sql_thread;      确认从库的sql_thread 是stop的
set gtid_next='xxxx';
begin;commit;
set gtid_next=automatic;
start slave sql_thread;
show slave status\G    查看是否正常
select * from tb1;        查看是否正常

备注：基于Pos复制跳过事务的方法
stop slave sql_thread;
set global sql_slave_skip_counter=1;
start slave sql_thread;

sql_thread重放SQL逻辑不能满足
从库3308执行，查看错误原因
select * from performance_schema.replication_applier_status_by_worker\G
mysql-bin.000001 start:exec_master_log_pos end: 15444
mysqlbinlog -v --base64-output=decode-rows --start-position=exec_master_log_pos --stop-position=15444 mysql-bin.000001

核对一下gtid

2.大数据量变更造成的复制延迟如何定位及解决

Seconds_Behind_Master越来越大,Retrieved_Gtid_Set 也一直在增长，但是Executed_Gtid_Set，卡着不动或者增长很慢，很有可能就是有大事务

找到Relay_Master_Log_File的值 对应的是 master binlog,在找Exec_Master_Log_Pos的值 去看看什么情况
mysqlbinlog -v --base64-output=decode-rows --start-position=Exec_Master_Log_Pos Relay_Master_Log_File

Executed_Gtid_Set  这个值的下一个

主库3306执行,模拟大事务
insert into tb1(c1,c2) select c1,c2 from tb1;
insert into tb1(c1,c2) select c1,c2 from tb1;
insert into tb1(c1,c2) select c1,c2 from tb1;
insert into tb1(c1,c2) select c1,c2 from tb1;
insert into tb1(c1,c2) select c1,c2 from tb1;
insert into tb1(c1,c2) select c1,c2 from tb1;
insert into tb1(c1,c2) select c1,c2 from tb1;
insert into tb1(c1,c2) select c1,c2 from tb1;

cat /var/log/messages
dmesg

select * from tb1 order by desc limit 10;
select count(*) from tb1 where id< 10289040;

在主库上删除1000万数据

1.session binlog_format='statement'; 
delete from tb1 where id <  10289040;
减少了binlog的传输，实质删除的过程没有少(sql_thread)
拆分在删，10万

2.创建一张表 tb1_new,  原tb1 -> tb1_old
create table tb1_new like tb1;
rename table tb1 to tb1_old;
insert into tb1_new select * from tb1_old where id>=10289040;
rename table tb1_new to tb1;
drop table tb1_old;                  空闲的时候执行

3.  delete from tb1 where id<1028040;
i = 10000
for i < 10289040 do:
  set sql_log_bin=0;
  delete from tb1 where id<=i;
  i += 10000


  set sql_log_bin=0;
  delete from tb1 where id < 10289040;
  set sql_log_bin=1;

3.主从复制延迟排查及开发中处理技巧有哪些?

1.sql_thread在干嘛，是不是应该干的慢
2.并行复制是不是不工作了

select * from performance_schema.replication_applier_status_by_worker\G;

a.主库是不是开启了binlog group commit;
b.从库是不是开启了并行(writeset)

3.iostat -x -m 1 看看io是否正常 pt-ioprofile

show variables like '%flush%';      --> innodb_flush_log_at_trx_commit 改成2或者0  以便更快的刷新
主库sync_binlog=1,从库sync_binlog 可以设置为0

4.一个主库有两个从库，但是其中有一台机器慢，慢一段又好了

raid卡的电池在充电， 通过megacli查看

5. show engine innodb status\G
   查看死锁情况

6. innodb buffer pool 的命中率
   命中率如果小于98，可能内存不够
   show engine innodb status\G 查看命中率
   percona 版本特性 prefetch sql_thread

tokudb