mysql如何开启半同步_mysql半同步开启

开启半同步复制

#在有的高可用架构下，master和slave需同时启动，以便在切换后能继续使用半同步复制

/etc/my.cnf

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"rpl-semi-sync-master-enabled = 1rpl-semi-sync-slave-enabled = 1

查看半同步是否在运行

master：

mysql> show status like 'Rpl_semi_sync_master_status';

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

| Variable_name              | Value |

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

| Rpl_semi_sync_master_status | ON    |

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

slave：

mysql> show status like 'Rpl_semi_sync_slave_status';

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

| Variable_name              | Value |

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

| Rpl_semi_sync_slave_status | ON    |

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

重启slave上的IO线程使半同步生效

mysql> stop slave io_thread;

mysql> start slave io_thread;

#如果没有重启，则默认还是异步复制，重启后，slave会在master上注册为半同步复制的slave角色。

半同步复制测试

master:

mysql>create database db;

mysql>use db

Database changed

mysql> create table t1(id int);

mysql> insert into t1 values(1);

mysql> select * fromt1;+------+| id |+------+| 1 |+------+1 row in set (0.00sec)

slave:

mysql> select * fromdb.t1;+------+| id |+------+| 1 |+------+1 row in set (0.00sec)

#可以看到数据很快同步到了从库上，下面关闭io_thread测试

slave：

mysql>stop slave io_thread;

Query OK,0 rows affected (0.00sec)

master：

mysql> insert into t1 values(2); #此处有一个10s的超时等待时间，超时后转为异步插入

Query OK,1 row affected (10.11sec)

mysql> show status like 'Rpl_semi_sync_master_status'; #半同步已失效+-----------------------------+-------+| Variable_name | Value |

+-----------------------------+-------+| Rpl_semi_sync_master_status | OFF |

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

slave：

mysql> show status like 'Rpl_semi_sync_slave_status'; #从库的半同步也失效+----------------------------+-------+| Variable_name | Value |

+----------------------------+-------+| Rpl_semi_sync_slave_status | OFF |

+----------------------------+-------+1 row in set (0.01sec)

slave：

mysql>start slave io_thread; #从库开启io线程

Query OK,0 rows affected (0.00sec)

master：

mysql> show status like 'Rpl_semi_sync_master_status'; #又重新转为半同步复制+-----------------------------+-------+| Variable_name | Value |

+-----------------------------+-------+| Rpl_semi_sync_master_status | ON |

+-----------------------------+-------+1 row in set (0.00sec)

slave：

mysql> select * fromdb.t1; #从库上数据已同步+------+| id |+------+| 1 || 2 |+------+2 rows in set (0.00 sec)