MySQL 主从复制的过程
MySQL 主从复制,其线程和过程分析
Master ->Slave IO_Thread (单线程)->Slave Relay log -> Slave SQL_Thread (单线程,每次等待所有Worker线程处理完毕之后才重新拉取新binlog)->Slave_Coordinator - >Slave_Worker (parallel_worker),假设parallel_worker = 8,其中一个是大DDL,其余7个dml语句必须是等待这个DDL执行完成后,才会继续下一轮的sql_thread回放应用relaylog。
– Coordinator //协调者、统筹者、监控
– Worker //工作线程,对应的是slave_parallel_worker 参数设置
– 除去show slave status\G命令无法提供足够的信息排查复制延迟问题,可通过了解进程处于什么活动状态或正在等待中
1、查看MySQL的线程表
SELECT name, processlist_state, processlist_time FROM performance_schema.threads WHERE name LIKE '%slave%';
2、查看replication_connection_status
SELECT * FROM performance_schema.replication_connection_status;
3、查看replication_applier_status_by_worker
SELECT * FROM performance_schema.replication_applier_status_by_worker;