分别在MySQL5.7和8.0中测试主从复制中主库表缺失主键会导致主从延迟的情况_产业新知_资讯_猿巴巴

简介：

检查延迟的方法：在从库上通过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-- 设置主从的网络环境

2docker pull mysql:5.7.36 3docker network create --subnet=172.72.5.0/24 mysql-network 4 5 6-- 删除之前的容器 7rm -rf /lhrmysqltest3/master1/conf.d 8rm -rf /lhrmysqltest3/slave1/conf.d 9docker rm -f master1 slave11011-- 创建参数文件路径12mkdir -p /lhrmysqltest3/master1/conf.d13mkdir -p /lhrmysqltest3/slave1/conf.d14151617-- 配置主库参数18cat > /lhrmysqltest3/master1/conf.d/my.cnf <<"EOF"19[mysqld]20port=330621character_set_server=utf8mb422secure_file_priv=23server-id = 573365024log-bin = 25binlog_format=row26skip-name-resolve27gtid-mode=ON28enforce-gtid-consistency=on29report_host=172.72.5.5030default-time-zone = '+8:00'31log_timestamps = SYSTEM32log_slave_updates = 133master_info_repository='table'34relay_log_info_repository='table'35relay_log_recovery=13637EOF383940-- 配置从库参数41cat > /lhrmysqltest3/slave1/conf.d/my.cnf <<"EOF"42[mysqld]43port=330644character_set_server=utf8mb445secure_file_priv=46server-id = 573365147log-bin = 48binlog_format=row49gtid-mode=ON50enforce-gtid-consistency=ON51skip_name_resolve52report_host=172.72.5.5153default-time-zone = '+8:00'54log_timestamps = SYSTEM55log_slave_updates = 156master_info_repository='table'57relay_log_info_repository='table'58relay_log_recovery=15960EOF616263-- 申请主库环境64docker run -d --name master1 65 -h master1 -p 33650:3306 --net=mysql-network --ip 172.72.5.50 66 -v /lhrmysqltest3/master1/conf.d:/etc/mysql/conf.d 67 -e MYSQL_ROOT_PASSWORD=lhr 68 mysql:5.7.36697071-- 申请从库环境72docker run -d --name slave1 73 -h slave1 -p 33651:3306 --net=mysql-network --ip 172.72.5.51 74 -v /lhrmysqltest3/slave1/conf.d:/etc/mysql/conf.d 75 -e MYSQL_ROOT_PASSWORD=lhr 76 mysql:5.7.36777879-- 登陆80docker exec -it master1 bash81docker exec -it master1 mysql -uroot -plhr828384-- 查询85mysql -uroot -plhr -h192.168.1.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid"86mysql -uroot -plhr -h192.168.1.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid"

主库配置

1-- 主库创建复制用户repl

2mysql -uroot -plhr -h192.168.1.35 -P33650 3grant replication slave on *.* to repl@'%' identified by 'lhr'; 4select user,host,grant_priv,password_last_changed,authentication_string from mysql.user; 5 6create database lhrdb; 7use lhrdb; 8create table t(id int,name varchar(30)); 9create table mytb1(id int,name varchar(30));10insert into mytb1 values(1,'a'),(2,'b');111213-- 查询14show master status ;15show slave hosts;16select @@hostname,@@server_id,@@server_uuid;

从库配置

1mysql -uroot -plhr -h192.168.1.35 -P33651

2 3 4-- 修改参数 5change master to 6master_host='172.72.5.50', 7master_port=3306, 8master_user='repl', 9master_password='lhr',10master_auto_position=1;1112-- 启动复制进程13start slave; 14show slave status G;1516SELECT * FROM lhrdb.mytb1;1718-- 主库：19insert into mytb1 values(3,'c'),(4,'d');

主从查询

1-- 主库

2show slave hosts; 3show master status; 4 5-- 从库 6show slave status; 7 8 910-- 线程查询11SELECT *12FROM performance_schema.threads a13WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker'14 ,'thread/sql/replica_io','thread/sql/replica_sql','thread/sql/replica_worker' ) 15or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;161718SELECT * FROM information_schema.`PROCESSLIST` a 19where a.USER='system user' 20or a.command in ('Binlog Dump','Binlog Dump GTID') ;

MySQL 5.7实验过程：

主库创建表

主库先创建一张8万行的大表：

1-- 分批提交，关闭自动提交

2DELIMITER $$ 3create procedure `t_pro`(num int) 4begin 5declare i int unsigned default 1; 6 7set autocommit=0; 8 9set i=1;10 while i <= num do11 insert into `t` (`id`,`name`) values(i,concat('主键测试',i));12 set i=i + 1;13 if i%10000 = 0 then14 commit;15 end if;16 end while;1718set autocommit=1;19end$$20DELIMITER ;2122call `t_pro`(80000);2324select count(*) from lhrdb.t;

主库做更新操作

1MySQL [lhrdb]> flush logs;

2Query OK, 0 rows affected (0.02 sec) 3 4MySQL [lhrdb]> show master logs; 5+-------------------+-----------+ 6| Log_name | File_size | 7+-------------------+-----------+ 8| master-bin.000001 | 177 | 9| master-bin.000002 | 3071539 |10| master-bin.000003 | 623044079 |11| master-bin.000004 | 633267 |12| master-bin.000005 | 194 |13+-------------------+-----------+145 rows in set (0.05 sec)1516MySQL [lhrdb]> update t set name=concat('主键测试66，结果验证66',t.id) where id <=20000;17Query OK, 20000 rows affected (0.72 sec)18Rows matched: 20000 Changed: 20000 Warnings: 0

可以看出，主库基本在1s就更新完成，变化的行数为2万行。

从库查询延迟，

1C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master

2mysql: [Warning] Using a password on the command line interface can be insecure. 3 Seconds_Behind_Master: 44 4 5C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33652 -e "show slave status G" | grep Seconds_Behind_Master 6mysql: [Warning] Using a password on the command line interface can be insecure. 7 Seconds_Behind_Master: 115 8 9... ... 1011C:Userslhrxxt>12C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master13mysql: [Warning] Using a password on the command line interface can be insecure.14 Seconds_Behind_Master: 821516C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33652 -e "show slave status G" | grep Seconds_Behind_Master17mysql: [Warning] Using a password on the command line interface can be insecure.18 Seconds_Behind_Master: 1541920C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master21mysql: [Warning] Using a password on the command line interface can be insecure.22 Seconds_Behind_Master: 1962324C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33652 -e "show slave status G" | grep Seconds_Behind_Master25mysql: [Warning] Using a password on the command line interface can be insecure.26 Seconds_Behind_Master: 2682728C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master29mysql: [Warning] Using a password on the command line interface can be insecure.30 Seconds_Behind_Master: 03132C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33652 -e "show slave status G" | grep Seconds_Behind_Master33mysql: [Warning] Using a password on the command line interface can be insecure.34 Seconds_Behind_Master: 270353637... ... 3839C:Userslhrxxt>40C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master41mysql: [Warning] Using a password on the command line interface can be insecure.42 Seconds_Behind_Master: 04344C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33652 -e "show slave status G" | grep Seconds_Behind_Master45mysql: [Warning] Using a password on the command line interface can be insecure.46 Seconds_Behind_Master: 277 4748C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master49mysql: [Warning] Using a password on the command line interface can be insecure.50 Seconds_Behind_Master: 05152C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33652 -e "show slave status G" | grep Seconds_Behind_Master53mysql: [Warning] Using a password on the command line interface can be insecure.54 Seconds_Behind_Master: 0 55

可以发现，最长延迟270秒左右，相当于5分钟左右。

分析主库的binlog日志

1[root@docker35 ~]# docker exec -it master1 bash

2root@master:/# cd /var/lib/mysql 3root@master:/var/lib/mysql# mysqlbinlog master-bin.000005 --base64-output=decode-row -vv | grep UPDATE | wc -l 420000 5root@master:/var/lib/mysql# mysqlbinlog master-bin.000005 --base64-output=decode-row -vv | more 6。。。。。。。 7### UPDATE `lhrdb`.`t` 8### WHERE 9### @1=1 10### @2='主键测试，结果验证1' 11### SET12### @1=1 13### @2='主键测试66，结果验证661' 14### UPDATE `lhrdb`.`t`15### WHERE16### @1=2 17### @2='主键测试，结果验证2' 18### SET19### @1=2 20### @2='主键测试66，结果验证662' 2122。。。。。。。

可以看出，在ROW模式下，在主库上执行了一条UPDATE语句，更新了2万行记录，但是在binlog中，记录了2万行的UPDATE语句。

分析从库的中继日志

1[root@docker35 ~]# docker exec -it slave1 bash

2root@slave1:/# cd /var/lib/mysql 3root@slave1:/var/lib/mysql# mysqlbinlog slave1-relay-bin.000011 --base64-output=decode-row -vv | grep UPDATE | wc -l420000

可以看出，在从库上也是2万行的UPDATE语句，也是一条一条的进行更新。由于没有主键和索引，所以，就会导致在从库进行2万次的全表扫描，这样也就拖慢了从库APPLY的效率。

尝试添加并行

1-- 主库

2set global binlog_group_commit_sync_delay=10; 3set global binlog_group_commit_sync_no_delay_count=10; 4show variables like 'binlog_group_commit_sync_%'; 5 6-- 从库 7STOP SLAVE SQL_THREAD; 8set global slave_parallel_type=LOGICAL_CLOCK; 9set global slave_parallel_workers=16;1011show variables like 'slave_parallel%';1213start SLAVE SQL_THREAD;141516-- 线程查询17SELECT *18FROM performance_schema.threads a19WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker'20 ,'thread/sql/replica_io','thread/sql/replica_sql','thread/sql/replica_worker' ) 21or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;222324SELECT * FROM information_schema.`PROCESSLIST` a 25where a.USER='system user' 26or a.command in ('Binlog Dump','Binlog Dump GTID') ;272829-- 主库更新30update t set name=concat('主键测试99，结果验证99',t.id) where id <=20000;313233-- 从库查询延迟，发现延迟并没有很大的改善34mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master3536C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master37mysql: [Warning] Using a password on the command line interface can be insecure.38 Seconds_Behind_Master: 1953940C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master41mysql: [Warning] Using a password on the command line interface can be insecure.42 Seconds_Behind_Master: 043

解决延迟：表添加主键

1-- 主库执行，会自动同步到从库

2MySQL [lhrdb]> alter table t add primary key(id); 3Query OK, 0 rows affected (1.05 sec) 4Records: 0 Duplicates: 0 Warnings: 0 5 6MySQL [lhrdb]> desc t; 7+-------+--------------+------+-----+---------+-------+ 8| Field | Type | Null | Key | Default | Extra | 9+-------+--------------+------+-----+---------+-------+10| id | int(11) | NO | PRI | NULL | |11| name | varchar(100) | YES | | NULL | |12+-------+--------------+------+-----+---------+-------+132 rows in set (0.05 sec)1415MySQL [lhrdb]> update t set name=concat('主键测试888，结果验证8888',t.id) where id <=30000;16Query OK, 30000 rows affected (1.29 sec)17Rows matched: 30000 Changed: 30000 Warnings: 0181920-- 查询从库的延迟21C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33651 -e "show slave status G" | grep Seconds_Behind_Master22mysql: [Warning] Using a password on the command line interface can be insecure.23 Seconds_Behind_Master: 32425C:Userslhrxxt>mysql -uroot -plhr -h192.168.1.35 -P33652 -e "show slave status G" | grep Seconds_Behind_Master26mysql: [Warning] Using a password on the command line interface can be insecure.27 Seconds_Behind_Master: 028

可以看到，在有主键的情况下，从库基本无延迟。

MySQL 8.0.27环境实验：

1## 1、初始化环境

2docker rm -f master2 slave2 3 4rm -rf /lhrmysqltest3/master2/ 5rm -rf /lhrmysqltest3/slave2/ 6 7mkdir -p /lhrmysqltest3/master2/conf.d 8mkdir -p /lhrmysqltest3/slave2/conf.d 9 10 11 12cat > /lhrmysqltest3/master2/conf.d/my.cnf <<"EOF" 13[mysqld] 14port=3306 15character_set_server=utf8mb4 16secure_file_priv= 17server-id = 8033660 18log-bin = 19binlog_format=row 20skip-name-resolve 21gtid-mode=ON 22enforce-gtid-consistency=on 23report_host=172.72.5.60 24default-time-zone = '+8:00' 25log_timestamps = SYSTEM 26log_slave_updates = 1 27master_info_repository='table' 28relay_log_info_repository='table' 29relay_log_recovery=1 30default_authentication_plugin=mysql_native_password 31EOF 32 33 34cat > /lhrmysqltest3/slave2/conf.d/my.cnf <<"EOF" 35[mysqld] 36port=3306 37character_set_server=utf8mb4 38secure_file_priv= 39server-id = 8033661 40log-bin = 41binlog_format=row 42gtid-mode=ON 43enforce-gtid-consistency=ON 44skip_name_resolve 45report_host=172.72.5.61 46default-time-zone = '+8:00' 47log_timestamps = SYSTEM 48log_slave_updates = 1 49master_info_repository='table' 50relay_log_info_repository='table' 51relay_log_recovery=1 52default_authentication_plugin=mysql_native_password 53EOF 54 55 56 57docker run -d --name master2 58 -h master -p 33660:3306 --net=mysql-network --ip 172.72.5.60 59 -v /lhrmysqltest3/master2/conf.d:/etc/mysql/conf.d 60 -e MYSQL_ROOT_PASSWORD=lhr 61 mysql:8.0.27 62 63 64docker run -d --name slave2 65 -h slave2 -p 33661:3306 --net=mysql-network --ip 172.72.5.61 66 -v /lhrmysqltest3/slave2/conf.d:/etc/mysql/conf.d 67 -e MYSQL_ROOT_PASSWORD=lhr 68 mysql:8.0.27 69 70 71 72 73## 2、主库配置 74 75-- 主库创建复制用户repl 76mysql -uroot -plhr -h192.168.1.35 -P33660 77create user repl@'%' identified with mysql_native_password by 'lhr'; 78grant all on *.* to repl@'%' with grant option; 79flush privileges; 80select user,host,grant_priv,password_last_changed,authentication_string from mysql.user; 81 82create database lhrdb; 83use lhrdb; 84create table t(id int,name varchar(30)); 85create table mytb1(id int,name varchar(30)); 86insert into mytb1 values(1,'a'),(2,'b'); 87 88 89 90 91## 3、从库配置 92 93mysql -uroot -plhr -h192.168.1.35 -P33661 94 95change master to 96master_host='172.72.5.60', 97master_port=3306, 98master_user='repl', 99master_password='lhr',100master_auto_position=1;101102-- 启动复制进程103start slave; 104show slave status G;105106SELECT * FROM lhrdb.mytb1;107108-- 主库：109insert into mytb1 values(3,'c'),(4,'d');110111112SELECT * FROM lhrdb.mytb1;113114115116-- 主库建表117DELIMITER $$118create procedure `t_pro`(num int)119begin120declare i int unsigned default 1;121122set autocommit=0;123124set i=1;125 while i <= num do126 insert into `t` (`id`,`name`) values(i,concat('主键测试',i));127 set i=i + 1;128 if i%10000 = 0 then129 commit;130 end if;131 end while;132133set autocommit=1;134end$$135DELIMITER ;136137call `t_pro`(80000);138139select count(*) from lhrdb.t;140141update t set name=concat('主键测试66，结果验证66',t.id) where id <=20000;142143144145mysql -uroot -plhr -h192.168.1.35 -P33661 -e "show slave status G" | grep Seconds_Behind_Master146147-- 主库开并行148set global binlog_group_commit_sync_delay=10;149set global binlog_group_commit_sync_no_delay_count=10;150show variables like 'binlog_group_commit_sync_%';151152-- 从库153STOP SLAVE SQL_THREAD;154set global slave_parallel_type=LOGICAL_CLOCK;155set global slave_parallel_workers=16;156157show variables like 'slave_parallel%';158159start SLAVE SQL_THREAD;160161162163-- 线程查询164SELECT *165FROM performance_schema.threads a166WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker'167 ,'thread/sql/replica_io','thread/sql/replica_sql','thread/sql/replica_worker' ) 168or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;169170171SELECT * FROM information_schema.`PROCESSLIST` a 172where a.USER='system user' 173or a.command in ('Binlog Dump','Binlog Dump GTID') ;174175176mysql -uroot -plhr -h192.168.1.35 -P33661 -e "show slave status G" | grep Seconds_Behind_Master177178179180-- 主库增加主键181alter table t add primary key(id);182

可见，主库更新2万行数据，从库延迟不超过5秒，但若主库更新6万行，则从库延迟接近20秒。说明，在MySQL 8中，性能有所提升，但仍然需要主键。

总结: ：

1、在MySQL 5.7的主从复制架构中，若存在大表，那么一定要有主键或唯一索引，否则将导致很大的主从延迟。从库即使添加并行复制，也不能改善这种情况。

2、从MySQL 8.0开始的主从复制架构中，若主库大表没有主键，仍然会导致从库的延迟，但是，延迟的现象没有5.7那么严重，所以，我们仍然建议主库的大表一定需要有主键。

分别在MySQL5.7和8.0中测试主从复制中主库表缺失主键会导致主从延迟的情况

更多相关内容

新常态下企业运营用工白皮书

玫琳凯HR：该招聘招聘，该晋升晋升，以坚定的人才观应对环境变化

中国混动“天花板”极限首测！一箱油行驶1711km

“共筑梦想、创赢未来”2022年绿色产业创新创业大赛启动会暨深圳赛区专场推介会成功举办

“二姐”张歆艺做客《毛雪汪》，海信新风空调营造凉爽空气环境

“二姐”张歆艺做客《毛雪汪》，海信新风空调营造凉爽空气环境

“二姐”张歆艺做客《毛雪汪》，海信新风空调营造凉爽空气环境

无人机巡检助力，环境监管插上“翅膀”

国际版抖音不拔卡教程安卓，抖音国际版免拔卡教程

安卓手机可以用tiktok吗，安卓手机怎么登录tiktok

猿巴巴_商业服务平台精选