环境:centos7 + mysql8.0.25 一主一从(异步复制)
半同步复制需要安装额外插件之后才能启用,然后通过相应的变量启用,在安装插件之前这些变量不可用
安装半同步复制之前请先配置好异步复制并且可用
1.安装一主一从的异步复制(过程省略)
2.安装加载半同步复制插件
主库:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
备库:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
建议主从把这个2个插件都安装上
3.启用主库的半同步插件:
主库:
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%rpl_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.00 sec)
4.启用从库的半同步插件:
启用从库的半同步插件:
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%rpl_semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| 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 | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.01 sec)
5.检查主库的半同步插件是否被启用
mysql> show status like '%semi%status%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_slave_status | OFF |
+-----------------------------+-------+
2 rows in set (0.01 sec)
mysql>
6.检查备库的半同步插件是否被启用:
mysql> show status like '%semi%status%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+-----------------------------+-------+
2 rows in set (0.01 sec)
mysql>
因为从库的复制线程不能自动感知到半同步复制插件的配置变化,所以此时半同步复制在备库中未生效
重启备库的io_thread
mysql> stop slave io_thread;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show status like '%semi%status%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_slave_status | ON |
+-----------------------------+-------+
2 rows in set (0.00 sec)
现在从库半同步插件启用了
7.在主从实例配置文件中添加半同步插件的配置
#for master
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
#for slave
rpl_semi_sync_slave_enabled = 1
8.半同步复制验证
mysql> show status like '%semi%tx%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+---------------------------------------+-------+
5 rows in set (0.00 sec)
mysql> use ww_xxx
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values (4),(6),(9),(10);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+
| id |
+------+
| 4 |
| 6 |
| 9 |
| 10 |
+------+
4 rows in set (0.00 sec)
mysql> show status like '%semi%tx%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 358 |
| Rpl_semi_sync_master_tx_wait_time | 716 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_yes_tx | 2 |
+---------------------------------------+-------+
5 rows in set (0.00 sec)
mysql>
Rpl_semi_sync_master_yes_tx 成功收到slave ack的事务数量 可以看出有2个事务通过半同步复制进行的
停止备库的io_thread线程,在主库执行事务
mysql> insert into test values(100);
Query OK, 1 row affected (10.01 sec)
mysql>
可以看到语句执行10s 等待备库ack响应超时这也证明半同步复制生效了
超时之后转换为异步复制了
mysql> show status like '%semi%status%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_slave_status | OFF |
+-----------------------------+-------+
2 rows in set (0.00 sec)
启动io_thread,恢复复制连接,主库从异步复制提升为半同步复制
mysql> show status like '%semi%status%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_slave_status | OFF |
+-----------------------------+-------+
2 rows in set (0.00 sec)
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。