部分转载自:http://www.cnblogs.com/ivictor/p/5735580.html
查看是否支持动态加载插件
mysql> show variables like '%have_dynamic_loading%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
1 row in set (0.00 sec)
主从分别加载插件
主:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.04 sec)
mysql> show plugins;
...
...
Name | Status | Type | Library | License
rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL
从:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.07 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
...
...
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+----------------------------+----------+--------------------+-------------------+---------+
启动半同步复制功能
主:mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
重启从库IO线程,或者直接重启slave
mysql> stop SLAVE IO_THREAD;
Query OK, 0 rows affected (0.22 sec)
mysql> start SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
查看从库error-log
2017-01-19 10:45:12 14853 [Note] Slave I/O thread killed while reading event
2017-01-19 10:45:12 14853 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 150082
2017-01-19 10:45:18 14853 [Note] Slave I/O thread: Start semi-sync replication to master 'rep@192.168.16.106:3306' in log 'mysql-bin.000001' at position 150082
监控半同步运行状态
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-------------------------
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
半同步问题
mysql> show variables like 'rpl_semi_sync_master_timeout';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.00 sec)
一些环境变量
mysql> 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_no_slave | ON |
| rpl_stop_slave_timeout | 31536000 |
+------------------------------------+----------+
5 rows in set (0.00 sec)
rpl_semi_sync_master_wait_for_slave_count
MySQL 5.7.3引入的,该变量设置主需要等待多少个slave应答,才能返回给客户端,默认为1。
rpl_semi_sync_master_wait_no_slave
ON
默认值,当状态变量Rpl_semi_sync_master_clients中的值小于rpl_semi_sync_master_wait_for_slave_count时,Rpl_semi_sync_master_status依旧显示为ON。
OFF
当状态变量Rpl_semi_sync_master_clients中的值于rpl_semi_sync_master_wait_for_slave_count时,Rpl_semi_sync_master_status立即显示为OFF,即异步复制。
说得直白一点,如果我的架构是1主2从,2个从都采用了半同步复制,且设置的是rpl_semi_sync_master_wait_for_slave_count=2,如果其中一个挂掉了,对于rpl_semi_sync_master_wait_no_slave设置为ON的情况,此时显示的仍然是半同步复制,如果rpl_semi_sync_master_wait_no_slave设置为OFF,则会立刻变成异步复制。
一些状态变量
mysql> show status like '%rpl%';
+--------------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 59956 |
| Rpl_semi_sync_master_net_wait_time | 119912 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 60098 |
| Rpl_semi_sync_master_tx_wait_time | 120197 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+--------+
14 rows in set (0.00 sec)
Rpl_semi_sync_master_clients
当前半同步复制从的个数,如果是一主多从的架构,并不包含异步复制从的个数。
Rpl_semi_sync_master_no_tx
The number of commits that were not acknowledged successfully by a slave.
成功半同步的个数
Rpl_semi_sync_master_yes_tx
The number of commits that were acknowledged successfully by a slave.
失败半同步个数
总结
1. 在一主多从的架构中,如果要开启半同步复制,并不要求所有的从都是半同步复制。
2. MySQL 5.7极大的提升了半同步复制的性能。
5.6版本的半同步复制,dump thread 承担了两份不同且又十分频繁的任务:传送binlog 给slave ,还需要等待slave反馈信息,而且这两个任务是串行的,dump thread 必须等待 slave 返回之后才会传送下一个 events 事务。dump thread 已然成为整个半同步提高性能的瓶颈。在高并发业务场景下,这样的机制会影响数据库整体的TPS 。
5.7版本的半同步复制中,独立出一个 ack collector thread ,专门用于接收slave 的反馈信息。这样master 上有两个线程独立工作,可以同时发送binlog 到slave ,和接收slave的反馈。