MySQL主从复制之增强半同步(无损复制)、延迟和并行复制
Tags: MySQL主从复制半同步复制增强半同步复制多线程复制并行复制延迟从库延迟复制无损复制高可用
简介
MySQL主从复制过程:
主从复制方式
MySQL有四种同步方式:
1、异步复制(Async Replication)
2、同步复制(sync Replication)
3、半同步复制(Async Replication)
4、增强半同步复制(lossless Semi-Sync Replication)、无损复制
1、异步复制(Async Replication)
主库将更新写入Binlog日志文件后,不需要等待数据更新是否已经复制到从库中,就可以继续处理更多的请求。Master将事件写入binlog,但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况下,如果Master宕机,事务在Master上已提交,但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制,此时Slave也可能会丢失事务。MySQL复制默认是异步复制,异步复制提供了最佳性能。
2、同步复制(Sync Replication)
主库将更新写入Binlog日志文件后,需要等待数据更新已经复制到从库中,并且已经在从库执行成功,然后才能返回继续处理其它的请求。同步复制提供了最佳安全性,保证数据安全,数据不会丢失,但对性能有一定的影响。
3、半同步复制(Semi-Sync Replication)
主库提交更新写入二进制日志文件后,等待数据更新写入了从服务器中继日志中,然后才能再继续处理其它请求。该功能确保至少有1个从库接收完主库传递过来的binlog内容已经写入到自己的relay log里面了,才会通知主库上面的等待线程,该操作完毕。
半同步复制,是最佳安全性与最佳性能之间的一个折中。
MySQL 5.5版本之后引入了半同步复制功能,主从服务器必须安装半同步复制插件,才能开启该复制功能。如果等待超时,超过rpl_semi_sync_master_timeout参数设置时间(默认值为10000,表示10秒),则关闭半同步复制,并自动转换为异步复制模式。当master dump线程发送完一个事务的所有事件之后,如果在rpl_semi_sync_master_timeout内,收到了从库的响应,则主从又重新恢复为增强半同步复制。
ACK (Acknowledge character)即是确认字符。
4、增强半同步复制(lossless Semi-Sync Replication、无损复制)
增强半同步是在MySQL 5.7引入,其实半同步可以看成是一个过渡功能,因为默认的配置就是增强半同步,所以,大家一般说的半同步复制其实就是增强的半同步复制,也就是无损复制。
增强半同步和半同步不同的是,等待ACK时间不同
rpl_semi_sync_master_wait_point = AFTER_SYNC(默认)
半同步的问题是因为等待ACK的点是Commit之后,此时Master已经完成数据变更,用户已经可以看到最新数据,当Binlog还未同步到Slave时,发生主从切换,那么此时从库是没有这个最新数据的,用户看到的是老数据。
增强半同步将等待ACK的点放在提交Commit之前,此时数据还未被提交,外界看不到数据变更,此时如果发送主从切换,新库依然还是老数据,不存在数据不一致的问题。
图示如下:
异步复制示例
MySQL主从复制之1主2从异步复制搭建及同步测试参考:https://www.xmmup.com/dbbao64mysqlzhucongzhi1zhu2congyibufuzhidajianjitongbuceshi.html
如何配置半同步复制
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 1.分别在主从安装插件 主: INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 从: INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 2.主服务器开启半同步 set global rpl_semi_sync_master_enabled=on; 3.从服务器开启半同步 set global rpl_semi_sync_slave_enabled=on; 4.配置主从同步(GTID模式示例) server-id=1 (从为2,保证唯一性) log-bin=master-bin log-bin-index=master-bin.index binlog-format=ROW gtid-mode=on enforce-gtid-consistency=true |
延迟从库
延迟从库:从库落后于主库一段时间。
SQL线程延时:数据已经写入relaylog中了,只是让SQL线程"慢点"运行
MySQL延迟复制的好处主要有几点:
1.误删除时,能更快恢复数据。有时候手抖了,把线上数据给误删除了,或者误删除库、表、其他对象,或不加WHERE条件的更新、删除,都可以让延迟从库在误操作前的时间点停下,然后进行恢复。
2.把延迟从库作为专用的备份节点。虽然有一定的延迟,但并不影响利用该节点作为备份角色,也不影响生产节点数据库。
3.还可以把延迟从库当做一些问题、案例研究的对象。个别时候,可能有些Binlog Event在普通从库上会有问题(例如:早期版本中无主键会导致从库更新非常慢的经典问题),这时就有时间在延迟从库上慢慢琢磨研究了。
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间。
启用延迟从库的方法也挺简单的:
1 2 3 4 5 6 7 8 | -- 直接用CHANGE MASTER TO设置,后面的N单位是秒数: STOP SLAVE SQL_THREAD; CHANGE MASTER TO MASTER_DELAY = N; START SLAVE SQL_THREAD; -- 查询: show slave status\G SQL_Delay: 0 |
并行复制
MySQL 5.6提供了并行复制,但是这种并行只是基于database的(slave-parallel-type=DATABASE)。如果用户的MySQL数据库实例中存在多个database,对于从库复制的速度的确可以有比较大的帮助。如果是基于单个database的复制依然无法做到真正的并行回放,这个阶段很多DBA将数据库进行垂直拆分,将一个database拆分成几个database,通过设置slave_parallel_workers=n,可以进行database级别的并行复制,但对于热点业务复制延迟依然无法解决。参数slave_parallel_workers默认值为0,表示禁用并行。
到了MySQL 5.7,才实现了真正的并行复制(slave-parallel-type=LOGICAL_CLOCK),也叫多线程复制,复制效率提升很多。MySQL 5.7的并行复制,multi-threaded slave即MTS,期望最大化还原主库的并行度,实现方式是在binlog event中增加必要的信息,以便slave节点根据这些信息实现并行复制。
一个组提交的事务都是可以并行回放,因为这些事务都已进入到事务的 Prepare 阶段,则说明事务之间没有任何冲突(否则就不可能提交)。如何知道事务是否在一组中呢?在MySQL 5.7版本中,其设计方式是将组提交的信息存放在GTID中。即使用户没有开启GTID,那么每个事务在开始前也会存在一个Anonymous_Gtid(匿名gtid),而这GTID中就存在着组提交的信息。
要开启 MySQL 5.7 并行复制需要以下2步:
1、首先在主库设置 binlog_group_commit_sync_delay 的值大于0 。
1 2 | set global binlog_group_commit_sync_delay=10; set global binlog_group_commit_sync_no_delay_count=10; |
binlog_group_commit_sync_delay 全局动态变量,单位微妙,默认0,范围:0~1000000(1秒)。
表示 binlog 提交后等待延迟多少时间再同步到磁盘,默认0 ,不延迟。当设置为 0 以上的时候,就允许多个事务的日志同时一起提交,也就是我们说的组提交。组提交是并行复制的基础,我们设置这个值的大于 0 就代表打开了组提交的功能。
binlog_group_commit_sync_no_delay_count 全局动态变量,单位个数,默认0,范围:0~1000000。
表示等待延迟提交的最大事务数,如果上面参数的时间没到,但事务数到了,则直接同步到磁盘。若 binlog_group_commit_sync_delay 没有开启,则该参数也不会开启。
2、其次要在 Slave 主机上设置如下几个参数:
1 2 | set global slave-parallel-type=LOGICAL_CLOCK; set global slave-parallel-workers=16; |
MySQL 5.7 引入的变量 slave-parallel-type,其可以配置的值有:
- DATABASE:默认值,基于库的并行复制方式。
- LOGICAL_CLOCK:基于组提交的并行复制方式。
一、1主2从之增强半同步复制(无损复制)示例
半同步复制比异步复制多了如下步骤:
1 2 3 4 5 6 7 | -- 1.分别在主从安装插件 主: INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 从: INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; -- 2.主服务器开启半同步 set global rpl_semi_sync_master_enabled=on; -- 3.从服务器开启半同步 set global rpl_semi_sync_slave_enabled=on; |
1、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 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 | -- 设置主从的网络环境 docker pull mysql:5.7.36 docker network create --subnet=172.72.0.0/24 mysqln0 -- 删除之前的容器 docker rm -f MSS57M33650 MSS57S33651 MSS57S33652 -- 创建参数文件路径 mkdir -p /lhrmysqltest3/master/conf.d mkdir -p /lhrmysqltest3/slave1/conf.d mkdir -p /lhrmysqltest3/slave2/conf.d -- 申请主库环境 docker run -d --name MSS57M33650 \ -h master -p 33650:3306 --net=mysqln0 --ip 172.72.0.50 \ -v /lhrmysqltest3/master/conf.d:/etc/mysql/conf.d \ -e MYSQL_ROOT_PASSWORD=lhr \ mysql:5.7.36 -- 申请从库1环境 docker run -d --name MSS57S33651 \ -h slave1 -p 33651:3306 --net=mysqln0 --ip 172.72.0.51 \ -v /lhrmysqltest3/slave1/conf.d:/etc/mysql/conf.d \ -e MYSQL_ROOT_PASSWORD=lhr \ mysql:5.7.36 -- 申请从库2环境 docker run -d --name MSS57S33652 \ -h slave2 -p 33652:3306 --net=mysqln0 --ip 172.72.0.52 \ -v /lhrmysqltest3/slave2/conf.d:/etc/mysql/conf.d \ -e MYSQL_ROOT_PASSWORD=lhr \ mysql:5.7.36 -- 配置主库参数 cat > /lhrmysqltest3/master/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.0.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 -- 配置从库1参数 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.0.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 -- 配置从库2参数 cat > /lhrmysqltest3/slave2/conf.d/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 5733652 log-bin = binlog_format=row gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve report_host=172.72.0.52 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 -- 重启3台环境 docker restart MSS57M33650 MSS57S33651 MSS57S33652 docker ps [root@docker35 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES b6311aac4c08 mysql:5.7.36 "docker-entrypoint.s…" 6 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:33652->3306/tcp, :::33652->3306/tcp MSS57S33652 b1f5f35c4fea mysql:5.7.36 "docker-entrypoint.s…" 11 seconds ago Up 8 seconds 33060/tcp, 0.0.0.0:33651->3306/tcp, :::33651->3306/tcp MSS57S33651 9a9826423f2d mysql:5.7.36 "docker-entrypoint.s…" 17 seconds ago Up 15 seconds 33060/tcp, 0.0.0.0:33650->3306/tcp, :::33650->3306/tcp MSS57M33650 -- 登陆 docker exec -it MSS57M33650 bash docker exec -it MSS57M33650 mysql -uroot -plhr -- 查询 mysql -uroot -plhr -h192.168.88.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.88.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.88.35 -P33652 -e "select @@hostname,@@server_id,@@server_uuid" C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | master | 5733650 | a5c6a094-dbf1-11ec-8f03-0242ac480032 | +------------+-------------+--------------------------------------+ C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | slave1 | 5733651 | a99e5d38-dbf1-11ec-bfe9-0242ac480033 | +------------+-------------+--------------------------------------+ C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33652 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | slave2 | 5733652 | ac918575-dbf1-11ec-b236-0242ac480034 | +------------+-------------+--------------------------------------+ |
2、主库配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- 主库创建复制用户repl mysql -uroot -plhr -h192.168.88.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 mytb1(id int,name varchar(30)); insert into mytb1 values(1,'a'),(2,'b'); -- 安装插件 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; -- 配置参数,后期需要配置到参数文件中去 set global rpl_semi_sync_master_enabled=on; -- 查询 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 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 | MySQL [lhrdb]> show master status ; +-------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000004 | 1085 | | | 1bc15639-bc5b-11eb-b1eb-0242ac480032:1-9 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.06 sec) MySQL [lhrdb]> show slave hosts; Empty set (0.06 sec) MySQL [lhrdb]> select @@hostname,@@server_id,@@server_uuid; +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | master | 5733650 | 1bc15639-bc5b-11eb-b1eb-0242ac480032 | +------------+-------------+--------------------------------------+ 1 row in set (0.05 sec) MySQL [lhrdb]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected (0.08 sec) MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected (0.08 sec) MySQL [lhrdb]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; +----------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +----------------------+---------------+ | rpl_semi_sync_master | ACTIVE | | rpl_semi_sync_slave | ACTIVE | +----------------------+---------------+ 2 rows in set (0.00 sec) MySQL [lhrdb]> set global rpl_semi_sync_master_enabled=on; Query OK, 0 rows affected (0.05 sec) MySQL [lhrdb]> show variables like '%semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +-------------------------------------------+------------+ 8 rows in set (0.01 sec) |
3、从库1配置
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 | -- 从库1 mysql -uroot -plhr -h192.168.88.35 -P33651 -- 安装插件 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; -- 配置参数,后期需要配置到参数文件中去 set global rpl_semi_sync_slave_enabled=on; -- 修改参数 change master to master_host='172.72.0.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'); |
4、从库2配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- S2 mysql -uroot -plhr -h192.168.88.35 -P33652 -- 安装插件 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; -- 配置参数,后期需要配置到参数文件中去 set global rpl_semi_sync_slave_enabled=on; -- 修改参数 change master to master_host='172.72.0.50', master_port=3306, master_user='repl', master_password='lhr', master_auto_position=1; start slave; show slave status \G; SELECT * FROM lhrdb.mytb1; |
5、主从查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- 主库 show slave hosts; show master status; show global status like 'rpl_semi%'; show variables like '%Rpl%'; -- 从库 show slave status; show variables like '%Rpl%'; -- 线程查询 SELECT * FROM performance_schema.threads a WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) 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') ; |
半同步复制查询:
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 | -- 主库 MySQL [lhrdb]> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 1 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 747 | | Rpl_semi_sync_master_tx_wait_time | 747 | | Rpl_semi_sync_master_tx_waits | 1 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 1 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 15 rows in set (0.01 sec) MySQL [lhrdb]> show variables like '%Rpl%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | | rpl_stop_slave_timeout | 31536000 | +-------------------------------------------+------------+ 9 rows in set (0.09 sec) -- 从库 MySQL [(none)]> show global status like 'Rpl_semi_sync_slave_status%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.05 sec) |
Rpl_semi_sync_master_clients表示记录支持半同步的slave的个数
Rpl_semi_sync_master_status标记master现在是否是半同步复制状态
6、测试半同步复制
测试过程:
1、关闭从库io线程,模拟网络卡顿
2、主库插入数据,等待10秒,同步方式自动转换为异步模式
3、从库启动io线程
4、同步方式变为半同步模式
先关闭一个从库,我们测试1主1从的情况。
二、配置延迟复制示例
我们把从库2配置为延迟从库,如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | STOP SLAVE SQL_THREAD; CHANGE MASTER TO MASTER_DELAY = 120; START SLAVE SQL_THREAD; show slave status \G 返回:SQL_Delay: 120 -- 主库插入数据 insert into mytb1 values(5,'e'),(6,'f'); select * from mytb1; -- 从库2查询 show slave status \G Seconds_Behind_Master: 110 SQL_Remaining_Delay: 46 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event |
三、配置并行复制示例
3.1、主库配置
1 2 3 | set global binlog_group_commit_sync_delay=10; set global binlog_group_commit_sync_no_delay_count=10; show variables like 'binlog_group_commit_sync_%'; |
3.2、从库2配置并行复制
只在从库2做配置,从库1不做配置。
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 | STOP SLAVE SQL_THREAD; set global slave_parallel_type=LOGICAL_CLOCK; set global slave_parallel_workers=4; show variables like 'slave_parallel%'; start SLAVE SQL_THREAD; -- 线程查询 SELECT * FROM performance_schema.threads a WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_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 [(none)]> SELECT * -> FROM performance_schema.threads a -> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ; +-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | +-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ | 33 | thread/sql/slave_io | FOREGROUND | 8 | root | 172.72.0.1 | NULL | Connect | 1203 | Waiting for master to send event | NULL | 30 | NULL | YES | YES | NULL | 79 | | 36 | thread/sql/slave_sql | FOREGROUND | 11 | root | 172.72.0.1 | NULL | Connect | 312 | Slave has read all relay log; waiting for more updates | NULL | 30 | NULL | YES | YES | NULL | 82 | | 37 | thread/sql/slave_worker | FOREGROUND | 12 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 83 | | 38 | thread/sql/slave_worker | FOREGROUND | 13 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 84 | | 39 | thread/sql/slave_worker | FOREGROUND | 14 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 85 | | 40 | thread/sql/slave_worker | FOREGROUND | 15 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 86 | +-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ 6 rows in set (0.05 sec) MySQL [(none)]> SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ; +----+-------------+------+------+---------+------+--------------------------------------------------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+-------------+------+------+---------+------+--------------------------------------------------------+------+ | 14 | system user | | NULL | Connect | 37 | Waiting for an event from Coordinator | NULL | | 15 | system user | | NULL | Connect | 37 | Waiting for an event from Coordinator | NULL | | 12 | system user | | NULL | Connect | 37 | Waiting for an event from Coordinator | NULL | | 13 | system user | | NULL | Connect | 37 | Waiting for an event from Coordinator | NULL | | 11 | system user | | NULL | Connect | 37 | Slave has read all relay log; waiting for more updates | NULL | | 8 | system user | | NULL | Connect | 928 | Waiting for master to send event | NULL | +----+-------------+------+------+---------+------+--------------------------------------------------------+------+ 6 rows in set (0.05 sec) |
3.3、主库压测测试并行复制
因为这里的架构为1主2从,我只配置了从库2为并行复制,从库1不是并行复制,那么接下来测试并行复制的效果。
首先,取消从库2的延迟复制功能:
1 2 3 4 5 6 7 | STOP SLAVE SQL_THREAD; CHANGE MASTER TO MASTER_DELAY = 0; START SLAVE SQL_THREAD; show slave status \G 返回:SQL_Delay: 0 |
首先,主库进行压测生成数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- 主库修改 set GLOBAL max_connections=10000; set global max_prepared_stmt_count=65536; -- 做压测 sysbench /usr/share/sysbench/oltp_common.lua --time=300 --mysql-host=172.72.0.50 --mysql-port=3306 \ --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=100000 --tables=10 \ --threads=16 --events=999999999 prepare sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=172.72.0.50 --mysql-port=3306 \ --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=1000000 --tables=10 --threads=16 \ --events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run -- 查询从库 mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master |
结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -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: 126 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -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: 62 [root@docker35 ~]# [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -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: 139 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -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: 65 [root@docker35 ~]# [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -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: 214 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -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: 78 |
可以看到,从库2比从库1能至少快一倍。
若是将从库的slave_parallel_workers配置为16,则配置了并行复制的库基本无延迟,而没有配置并行复制的库,延迟会越来越严重:
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 | STOP SLAVE SQL_THREAD; set global slave_parallel_workers=16; show variables like 'slave_parallel%'; start SLAVE SQL_THREAD; -- 线程查询 SELECT * FROM performance_schema.threads a WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_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 [lhrdb]> -- 线程查询 MySQL [lhrdb]> SELECT * -> FROM performance_schema.threads a -> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ; +-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | +-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ | 29 | thread/sql/slave_io | FOREGROUND | 4 | root | 192.168.88.64 | NULL | Connect | 1780 | Waiting for master to send event | NULL | 28 | NULL | YES | YES | NULL | 154 | | 106 | thread/sql/slave_sql | FOREGROUND | 81 | root | 192.168.88.64 | NULL | Connect | 24 | Slave has read all relay log; waiting for more updates | NULL | 28 | NULL | YES | YES | NULL | 168 | | 107 | thread/sql/slave_worker | FOREGROUND | 82 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 169 | | 108 | thread/sql/slave_worker | FOREGROUND | 83 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 170 | | 109 | thread/sql/slave_worker | FOREGROUND | 84 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 171 | | 110 | thread/sql/slave_worker | FOREGROUND | 85 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 172 | | 111 | thread/sql/slave_worker | FOREGROUND | 86 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 173 | | 112 | thread/sql/slave_worker | FOREGROUND | 87 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 174 | | 113 | thread/sql/slave_worker | FOREGROUND | 88 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 175 | | 114 | thread/sql/slave_worker | FOREGROUND | 89 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 176 | | 115 | thread/sql/slave_worker | FOREGROUND | 90 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 177 | | 116 | thread/sql/slave_worker | FOREGROUND | 91 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 178 | | 117 | thread/sql/slave_worker | FOREGROUND | 92 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 179 | | 118 | thread/sql/slave_worker | FOREGROUND | 93 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 180 | | 119 | thread/sql/slave_worker | FOREGROUND | 94 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 181 | | 120 | thread/sql/slave_worker | FOREGROUND | 95 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 182 | | 121 | thread/sql/slave_worker | FOREGROUND | 96 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 183 | | 122 | thread/sql/slave_worker | FOREGROUND | 97 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 184 | +-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+ 18 rows in set (0.01 sec) MySQL [lhrdb]> MySQL [lhrdb]> SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ; +----+-------------+------+------+---------+------+--------------------------------------------------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+-------------+------+------+---------+------+--------------------------------------------------------+------+ | 96 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 97 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 94 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 95 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 92 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 93 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 90 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 91 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 88 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 89 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 86 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 87 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 84 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 82 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 85 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 83 | system user | | NULL | Connect | 322 | Waiting for an event from Coordinator | NULL | | 81 | system user | | NULL | Connect | 24 | Slave has read all relay log; waiting for more updates | NULL | | 4 | system user | | NULL | Connect | 1780 | Waiting for master to send event | NULL | +----+-------------+------+------+---------+------+--------------------------------------------------------+------+ 18 rows in set (0.01 sec) |
重新测试,可以看到从库2基本无延迟了:
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 | [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -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: 50 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -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: 0 [root@docker35 ~]# [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master mysql: [Warning] Using a password on the command line interface can be insecure. mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master Seconds_Behind_Master: 131 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -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: 1 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -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: 210 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -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: 1 [root@docker35 ~]# [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Mastermysql: [Warning] Using a password on the command line interface can be insecure. Seconds_Behind_Master: 381 [root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -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: 1 |