MySQL 5.5 主从复制（半同步）

[v5tech]

默认情况下MySQL的复制是异步的，Master上所有的更新操作写入Binlog之后并不确保所有的更新都被复制到Slave之上。异步操作虽然效率高，但是在Master/Slave出现问题的时候，存在很高数据不同步的风险，甚至可能丢失数据。

MySQL5.5引入半同步复制功能的目的是为了保证在master出问题的时候，至少有一台Slave的数据是完整的。在超时的情况下也可以临时转入异步复制，保障业务的正常使用，直到一台salve追赶上之后，继续切换到半同步模式。

具体配置

_mysql5.5半同步插件是由谷歌提供，具体位置/usr/lib/mysql/plugin/下，一个是master用的semisync_master.so，一个是slave用的semisyncslave.so，具体配置如下

master:

(1).安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  
Query OK, 0 rows affected (0.39 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; 
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000; 
Query OK, 0 rows affected (0.00 sec)

(2).修改配置文件

[root@node1 ~]# sudo vim /etc/mysql/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1 #启用半同步
rpl_semi_sync_master_timeout=1000 #超时时间为1s

(3).重新启动服务

[root@node1 ~]# service mysql restart 
Shutting down MySQL... SUCCESS!   
Starting MySQL.. SUCCESS!

slave:

(1).安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  
Query OK, 0 rows affected (0.38 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;  
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD; 
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

(2).修改配置文件

[root@node2 ~]# sudo vim /etc/mysql/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled=1  #启用半同步复制

(3).重新启动服务

[root@node2 ~]# service mysql restart 
Shutting down MySQL. SUCCESS!   
Starting MySQL.. SUCCESS!

查看状态

master:

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+--------------------------------------------+-------+  
| Variable_name                              | Value |  
+--------------------------------------------+-------+  
| Rpl_semi_sync_master_clients               | 1     |  
| 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.00 sec)

slave:

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+----------------------------+-------+  
| Variable_name              | Value |  
+----------------------------+-------+  
| Rpl_semi_sync_slave_status | ON    |  
+----------------------------+-------+  
1 row in set (0.01 sec)

测试

master:

mysql> use crm;
Database changed
mysql> create table user (id int(10)); 
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_crm |
+---------------+
| employee      |
| user          |
+---------------+
2 rows in set (0.00 sec)
mysql> insert user value (1);
Query OK, 1 row affected (0.00 sec)

模拟故障

slave:

mysql> STOP SLAVE IO_THREAD; 
Query OK, 0 rows affected (0.02 sec)

master:

mysql> create table dept (id int(10));
Query OK, 0 rows affected (1.01 sec)

可以看到主服务器会卡1s，我们超时时间设置的为1s。

查看状态

master:

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 542   |
| Rpl_semi_sync_master_net_wait_time         | 1628  |
| Rpl_semi_sync_master_net_waits             | 3     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 597   |
| Rpl_semi_sync_master_tx_wait_time          | 1194  |
| 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)

slave:

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> START SLAVE IO_THREAD;     # 启动SLAVE IO_THREAD
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> show tables;             # 可见dept表已同步过来了
+---------------+
| Tables_in_crm |
+---------------+
| dept          |
| employee      |
| user          |
+---------------+
3 rows in set (0.00 sec)