合 分别在MySQL5.7和8.0中测试主从复制中主库表缺失主键会导致主从延迟的情况
👉 本文共约1556个字，系统预计阅读时间或需6分钟。
目录
之前发布过的一篇类似文章：【DB宝34】测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况
简介
检查延迟的方法：在从库上通过SHOW SLAVE STATUS检查Seconds_Behind_Master值即可获取主从复制延迟的秒数。
主从复制延迟，可能的原因有主库和从库方面：
① 主库写binlog不及时。
② dump线程压力大
③ IO线程阻塞
④ 表缺乏主键或唯一索引（常见）
假设主库更新一张500w表中的20w行数据，该update语句仅需要全表扫描1次；而在row格式下，记录到binlog日志中的SQL为20w次update操作，此时SQL Thread重放将特别慢，因为每一次update都需要进行一次全表扫描，即从库需要执行20w次的全表扫描。
⑤ 主库DML请求频繁（tps较大）
⑥ 主库执行大事务,导致从库SQL慢
⑦ 主库对大表执行DDL语句
⑧ 主库与从库硬件配置不一致
⑨ 从库自身压力过大
⑩ MyISAM存储引擎
⑪ 主从复制的服务器时钟是否一致。主从同步延迟与系统时间的关系，查看主从两台机器间系统时间差
⑫ 网络通信是否存在延时。主从同步延迟与压力、网络、机器性能的关系，查看从库的IO，cpu，mem及网络压力
⑬ 从库查询是否优化（比如存在查询慢，导致从库性能差，处理不过来）
⑭ 是否启用了延迟复制，使用“show slave status”查看SQL_Delay是否大于0
今天我们就通过实验的方式来验证第4种情况。
MySQL 5.7 环境准备
MySQL环境初始化
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | -- 设置主从的网络环境 docker pull mysql:5.7.36 docker network create --subnet=172.72.5.0/24 mysql-network -- 删除之前的容器 rm -rf /lhrmysqltest3/master1/conf.d rm -rf /lhrmysqltest3/slave1/conf.d docker rm -f master1 slave1 -- 创建参数文件路径 mkdir -p /lhrmysqltest3/master1/conf.d mkdir -p /lhrmysqltest3/slave1/conf.d -- 配置主库参数 cat > /lhrmysqltest3/master1/conf.d/my.cnf <<"EOF" [mysqld] port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 5733650 log-bin = binlog_format=row skip-name-resolve gtid-mode=ON enforce-gtid-consistency=on report_host=172.72.5.50 default-time-zone = '+8:00' log_timestamps = SYSTEM log_slave_updates = 1 master_info_repository='table' relay_log_info_repository='table' relay_log_recovery=1 EOF -- 配置从库参数 cat > /lhrmysqltest3/slave1/conf.d/my.cnf <<"EOF" [mysqld] port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 5733651 log-bin = binlog_format=row gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve report_host=172.72.5.51 default-time-zone = '+8:00' log_timestamps = SYSTEM log_slave_updates = 1 master_info_repository='table' relay_log_info_repository='table' relay_log_recovery=1 EOF -- 申请主库环境 docker run -d --name master1 \ -h master1 -p 33650:3306 --net=mysql-network --ip 172.72.5.50 \ -v /lhrmysqltest3/master1/conf.d:/etc/mysql/conf.d \ -e MYSQL_ROOT_PASSWORD=lhr \ mysql:5.7.36 -- 申请从库环境 docker run -d --name slave1 \ -h slave1 -p 33651:3306 --net=mysql-network --ip 172.72.5.51 \ -v /lhrmysqltest3/slave1/conf.d:/etc/mysql/conf.d \ -e MYSQL_ROOT_PASSWORD=lhr \ mysql:5.7.36 -- 登陆 docker exec -it master1 bash docker exec -it master1 mysql -uroot -plhr -- 查询 mysql -uroot -plhr -h192.168.1.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.1.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid" |
主库配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- 主库创建复制用户repl mysql -uroot -plhr -h192.168.1.35 -P33650 grant replication slave on *.* to repl@'%' identified by 'lhr'; select user,host,grant_priv,password_last_changed,authentication_string from mysql.user; create database lhrdb; use lhrdb; create table t(id int,name varchar(30)); create table mytb1(id int,name varchar(30)); insert into mytb1 values(1,'a'),(2,'b'); -- 查询 show master status ; show slave hosts; select @@hostname,@@server_id,@@server_uuid; |
从库配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql -uroot -plhr -h192.168.1.35 -P33651 -- 修改参数 change master to master_host='172.72.5.50', master_port=3306, master_user='repl', master_password='lhr', master_auto_position=1; -- 启动复制进程 start slave; show slave status \G; SELECT * FROM lhrdb.mytb1; -- 主库： insert into mytb1 values(3,'c'),(4,'d'); |
主从查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 主库 show slave hosts; show master status; -- 从库 show slave status; -- 线程查询 SELECT * FROM performance_schema.threads a WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ,'thread/sql/replica_io','thread/sql/replica_sql','thread/sql/replica_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ; SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ; |
MySQL 5.7实验过程
主库创建表
主库先创建一张8万行的大表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- 分批提交，关闭自动提交 DELIMITER $$ create procedure `t_pro`(num int) begin declare i int unsigned default 1; set autocommit=0; set i=1; while i <= num do insert into `t` (`id`,`name`) values(i,concat('主键测试',i)); set i=i + 1; if i%10000 = 0 then commit; end if; end while; set autocommit=1; end$$ DELIMITER ; call `t_pro`(80000); select count(*) from lhrdb.t; |
主库做更新操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | MySQL [lhrdb]> flush logs; Query OK, 0 rows affected (0.02 sec) MySQL [lhrdb]> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 177 | | master-bin.000002 | 3071539 | | master-bin.000003 | 623044079 | | master-bin.000004 | 633267 | | master-bin.000005 | 194 | +-------------------+-----------+ 5 rows in set (0.05 sec) MySQL [lhrdb]> update t set name=concat('主键测试66，结果验证66',t.id) where id <=20000; Query OK, 20000 rows affected (0.72 sec) Rows matched: 20000 Changed: 20000 Warnings: 0 |
可以看出，主库基本在1s就更新完成，变化的行数为2万行。
从库查询延迟，
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
