Mysql半同步主从复制
半同步复制可以直接在异步复制的基础上完成 Mysql搭建异步主从复制
先检查是否支持动态安装插件
mysql> select @@have_dynamic_loading
-> ;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
查看插件库的路径
mysql> show variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)
有这个两个表示支持在线安装插件，然后在master和slave上同时安装插件
# master
mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.10 sec)
mysql> select * from mysql.plugin;
+----------------------+--------------------+
| name | dl |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.00 sec)
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
# slave
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql.plugin;
+---------------------+-------------------+
| name | dl |
+---------------------+-------------------+
| rpl_semi_sync_slave | semisync_slave.so |
+---------------------+-------------------+
1 row in set (0.00 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
## 开启了半同步，为什么还是off呢，是由于我们之前slave是在运行状态，将其重启下就可以了！
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
查看master上的状态
mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#Rpl_semi_sync_master_clients 表示有几台slave连接到了这台master上，有几台，数字就是几；
将配置写入my.cnf中，避免重启失效
master
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000 ##配置超时多少毫秒切换到异同步复制
slave
[mysqld]
rpl_semi_sync_slave_enabled=1
验证
将 slave 的io thread 停掉，那么将在10秒后切换到异步复制（上面配置文件配置的10秒），也就是插入操作将卡住10秒才插入成功
# slavemysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------+
| Note | 3084 | Replication thread(s) for channel '' are already stopped. |
+-------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
# master
mysql> insert into t1 values(17);
Query OK, 1 row affected (10.01 sec) ##可以看到花了10秒才插入成功，此时就切换到了异步复制
切换到异步复制后，系统不会自动切回半同步，需要手动切，切得方式很简单，就是stop slave；start slave；
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com