MySQL半同步安装以及参数

时间:2022-11-07 18:05:20

MySQL半同步安装以及参数

基于MySQL5.5

官档地址:

Semisynchronous Replication Administrative Interface
https://dev.mysql.com/doc/refman/5.5/en/replication-semisync-interface.html

控制半同步复制的系统参数

rpl_semi_sync_master_enabled
Controls whether semisynchronous replication is enabled on the master. To enable or disable the plugin, set this variable to 1 or 0, respectively. The default is 0 (off).
控制是否启用半同步复制 rpl_semi_sync_master_timeout
A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).
等待从库提交并反馈的超时时间,超时则转向异步同步 rpl_semi_sync_slave_enabled
Similar to rpl_semi_sync_master_enabled, but controls the slave plugin.
类似于 rpl_semi_sync_master_enabled,但是用于控制 slave 的插件。

启用半同步复制监视的状态变量

Rpl_semi_sync_master_clients
The number of semisynchronous slaves.
半同步复制的 slave 的数量 Rpl_semi_sync_master_status
Whether semisynchronous replication currently is operational on the master. The value is 1 if the plugin has been enabled and a commit acknowledgment has occurred. It is 0 if the plugin is not enabled or the master has fallen back to asynchronous replication due to commit acknowledgment timeout.
半同步复制目前是否运行在主机上。如果已启用该插件,提交确认发生,值为 1。如果未启用该插件或 master 已经回落到异步复制导致提交确认超时,为 0。 Rpl_semi_sync_master_no_tx
The number of commits that were not acknowledged successfully by a slave.
提交但是没有反馈成功的slave的数量。 Rpl_semi_sync_master_yes_tx
The number of commits that were acknowledged successfully by a slave.
提交后反馈成功的slave的数量。 Rpl_semi_sync_slave_status
Whether semisynchronous replication currently is operational on the slave. This is 1 if the plugin has been enabled and the slave I/O thread is running, 0 otherwise.
是否同步复制目前是业务上的slave。如果已启用该插件,slave的 I/O 线程正在运行,是1 ,否则 0。

安装和配置

前提条件

1. MySQL 5.5 or higher must be installed.
2. The capability of installing plugins requires a MySQL server that supports dynamic loading. To verify this, check that the value of the have_dynamic_loading system variable is YES. Binary distributions should support dynamic loading.
3. Replication must already be working.

安装

1.设置主从库参数文件
[mysqld]
plugin_dir=/path/to/plugin/directory
主库:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从库:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查询安装情况
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
安装完成后,半同步默认是关闭的,需要在主从库上面都开启,才能使用,只开启主或者从,都无法使用 mysql运行时,可以使用以下参数
主库:
SET GLOBAL rpl_semi_sync_master_enabled = {0|1}; #默认是0,1是启用,0是关闭
SET GLOBAL rpl_semi_sync_master_timeout = N; #单位milliseconds(毫秒),10000毫秒=10秒
从库:
SET GLOBAL rpl_semi_sync_slave_enabled = {0|1};
如果要在mysql运行时启用半同步,必须重启IO_THREAD,slave会重新连上master并且被注册为 semisynchronous slave
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
如果不重启的话,slave将会继续使用 异步复制 可以重启mysql时
配置my.cof参数
主库:
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
从库:
[mysqld]
rpl_semi_sync_slave_enabled=1

监控

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

卸载

主库:
UNINSTALL PLUGIN rpl_semi_sync_master;
从库:
UNINSTALL PLUGIN rpl_semi_sync_slave;