原 【DB宝34】测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况
Tags: 原创MySQLDocker主从复制缺失主键主从延迟
一、简介
导致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万行的大表: