MySQL半同步复制模式

2014-11-24 18:29:48 · 作者: · 浏览: 0

Mysql复制默认是异步完成的,半同步方式是google为mysql开发的一个补丁,在mysql5.5或者更高的版本中已经集成了这个功能。半同步的意思表示MASTER 只需要接收到其中一台SLAVE的返回信息,就会commit;否则需等待直至达到超时时间然后切换成异步再提交。这个做可以使主从库的数据的延迟较小,可以在损失很小的性能的前提下提高数据的安全性。


半同步的开启比较简单,是需要在master和slave都安装半同步插件,并启用就可以了。


查询mysql复制有没有使用半同步,


mysql> show variables like 'rpl%';


+-------------------+-------+


| Variable_name | Value |


+-------------------+-------+


| rpl_recovery_rank | 0 |


+-------------------+-------+


1 row in set (0.00 sec)


或者使用show status like 'rpl%';


启用半同步模式:


在master执行


mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';


Query OK, 0 rows affected (0.03 sec)


mysql> set global rpl_semi_sync_master_enabled=0;


Query OK, 0 rows affected (0.00 sec)



mysql> show variables like 'rpl%';


+------------------------------------+-------+


| Variable_name | Value |


+------------------------------------+-------+


| rpl_recovery_rank | 0 |


| rpl_semi_sync_master_enabled | OFF |


| rpl_semi_sync_master_timeout | 10000 |


| rpl_semi_sync_master_trace_level | 32 |


| rpl_semi_sync_master_wait_no_slave | ON |


+------------------------------------+-------+


5 rows in set (0.00 sec)



mysql> set global rpl_semi_sync_master_enabled=1;


Query OK, 0 rows affected (0.00 sec)



mysql> show variables like 'rpl%';


+------------------------------------+-------+


| Variable_name | Value |


+------------------------------------+-------+


| rpl_recovery_rank | 0 |


| 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_semi_sync_master_enabled 的值是0(on)或者1(off),默认是1.


rpl_semi_sync_master_timeout的值默认是10000(10s)


5 rows in set (0.00 sec)


在slave执行如下操作:


mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';


Query OK, 0 rows affected (0.03 sec)



mysql> set global rpl_semi_sync_slave_enabled=1;


Query OK, 0 rows affected (0.00 sec)



mysql> show variables like 'rpl%';


+---------------------------------+-------+


| Variable_name | Value |


+---------------------------------+-------+


| rpl_recovery_rank | 0 |


| rpl_semi_sync_slave_enabled | ON |


| rpl_semi_sync_slave_trace_level | 32 |


+---------------------------------+-------+


3 rows in set (0.00 sec)



mysql> show status like 'rpl%';


+----------------------------+-------------+


| Variable_name | Value |


+----------------------------+-------------+


| Rpl_semi_sync_slave_status | ON |


| Rpl_status | AUTH_MASTER |


+----------------------------+-------------+


2 rows in set (0.01 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.00 sec)



rpl_semi_sync_slave_enabled的值是0(on)或者1(off),默认是1.