原 【DB宝34】测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况
Tags： 原创MySQLDocker主从复制缺失主键主从延迟
👉 本文共约773个字，系统预计阅读时间或需3分钟。
一、简介
导致MySQL主从复制延迟的原因有很多，其中一个原因就是大表缺失主键或唯一索引。
今天我们就通过实验的方式来验证这种情况。
二、环境准备
主库：IP为192.168.68.168，端口3306，版本为8.0.20
从库：IP为192.168.68.168，端口3306，版本为8.0.20
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 80 81 82 83 84 85 | [root@docker35 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES ebe3b62a2358 mysql:8.0.20 "docker-entrypoint.s…" 8 days ago Up 8 days 33060/tcp, 0.0.0.0:3319->3306/tcp mysql8020S1 76140b04e2fd mysql:8.0.20 "docker-entrypoint.s…" 8 days ago Up 8 days 33060/tcp, 0.0.0.0:3318->3306/tcp mysql8020M1 -- 主库 MySQL [lhrdb1]> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 80203319 | | 3306 | 80203318 | e12dfcd2-1e40-11eb-b2f0-0242c0a844a9 | +-----------+------+------+-----------+--------------------------------------+ MySQL [lhrdb1]> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) -- 从库 MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.68.168 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql8020M1-bin.000007 Read_Master_Log_Pos: 19665393 Relay_Log_File: mysql8020S1-relay-bin.000008 Relay_Log_Pos: 19665620 Relay_Master_Log_File: mysql8020M1-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 19665393 Relay_Log_Space: 19665928 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 80203318 Master_UUID: dcccf122-1e40-11eb-8ca0-0242c0a844a8 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:20-160037 Executed_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:1-160037 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) |
三、实验过程
3.1 主库创建表
主库先创建一张8万行的大表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | MySQL [lhrdb1]> DELIMITER $$ MySQL [lhrdb1]> drop procedure if exists `t_pro`$$ Query OK, 0 rows affected (0.01 sec) MySQL [lhrdb1]> create procedure `t_pro`(num int) -> begin -> declare i int unsigned default 1; -> set i=1; -> while i <= num do -> insert into `t` (`id`,`name`) -> values(i,concat('主键测试',i)); -> set i=i + 1; -> end while; -> end$$ Query OK, 0 rows affected (0.01 sec) MySQL [lhrdb1]> DELIMITER ; MySQL [lhrdb1]> call `t_pro`(80000); --- .... 这里插入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 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 80 81 82 83 84 85 86 87 | D:\Program Files\MySQL\mysql-8.0.15-winx64\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \G" | grep Seconds_Behind_Master mysql: [Warning] Using a password on the command line interface can be insecure. Seconds_Behind_Master: 11 D:\Program Files\MySQL\mysql-8.0.15-winx64\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \G" mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.68.168 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql8020M1-bin.000007 Read_Master_Log_Pos: 26029948 Relay_Log_File: mysql8020S1-relay-bin.000008 Relay_Log_Pos: 25067097 Relay_Master_Log_File: mysql8020M1-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 25066870 Relay_Log_Space: 26030483 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 12 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 80203318 Master_UUID: dcccf122-1e40-11eb-8ca0-0242c0a844a8 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: waiting for handler commit Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:20-181149 Executed_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:1-177960 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: D:\Program Files\MySQL\mysql-8.0.15-winx64\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \G" | grep Seconds_Behind_Master mysql: [Warning] Using a password on the command line interface can be insecure. Seconds_Behind_Master: 19 MySQL [(none)]> select count(*) from lhrdb1.t; +----------+ | count(*) | +----------+ | 41613 | +----------+ 1 row in set (0.01 sec) MySQL [(none)]> select count(*) from lhrdb1.t; +----------+ | count(*) | +----------+ | 41941 | +----------+ 1 row in set (0.01 sec) |
发现，从库延迟越来越高，主要原因是主库正在进行大批量的数据插入操作。
大约5分钟后，主库执行完毕，
1 2 3 4 5 6 7 8 9 10 | MySQL [lhrdb1]> call `t_pro`(80000); Query OK, 1 row affected (5 min 3.37 sec) MySQL [lhrdb1]> select count(*) from lhrdb1.t; +----------+ | count(*) | +----------+ | 80000 | +----------+ 1 row in set (0.01 sec) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
