合 分别在MySQL5.7和8.0中测试主从复制中主库表缺失主键会导致主从延迟的情况
之前发布过的一篇类似文章:【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; |