当前位置:首页 » Mysql技术教程

mysql 半同步复制

2014-02-24 15:30 本站整理 浏览(12)
1、半同步的开启,只需要在Master与Slave服务器上都安装上半同步的插件并启用即可;
   而插件在Mysql的安装目录中:"/usr/local/mysql/lib/plugin/"
######查看半同步插件
 ls /usr/local/mysql/lib/plugin/
 semisync_master.so    #用于Master服务器安装的半同步插件
semisync_slave.so     #用于Slave服务器安装的半同步插件
2、在Master与Slave服务器分别安装半同步插件
######在Master服务器安装半同步插件
[root@master ~]# mysql
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装Master半同步插件
mysql> set global rpl_semi_sync_master_enabled = 1;    #开启Master半同步功能
mysql> set global rpl_semi_sync_master_timeout = 1000;
=========================================================================
######在Slave服务器安装半同步插件
[root@slave ~]# mysql
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装Slave半同步插件
mysql> set global rpl_semi_sync_slave_enabled = 1;      #开启Slave半同步功能
mysql> stop slave io_thread;start slave io_thread;      #重启IO线程生效
3、查看半同步开启状态
######在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     |
+--------------------------------------------+-------+
mysql> show global variables like '%rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    | #Master半同步已经开启
| rpl_semi_sync_master_timeout       | 1000  | #超时时间
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
=========================================================================
######在Slave服务器上查看
mysql> show global status like 'rpl_semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |         #已经为开启状态
+----------------------------+-------+
mysql> show global variables like '%rpl%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_recovery_rank               | 0     |
| rpl_semi_sync_slave_enabled     | ON    |    #Slave半同步已经开启
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
4、查看Slave线程是否启动
[root@slave ~]# mysql -e 'show slave status\G;' | grep Running
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
注释:这两项必须为"Yes",如果是"No"说明启动失败
5、在Master服务器上将前面创建的"allen"数据库删除,然后验证Slave服务器
######在Master服务器删除数据库
[root@master ~]# mysql -e 'drop database allen;'
[root@master ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
=======================================================
######在Slave服务器查看
[root@slave ~]# mysql -e 'show databases;'
+--------------------+
| Database           |    注释:已经成功删除"allen"数据库
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6、以上配置都不能永久生效,如果想要永久生效,将以上配置加入到配置文件重启服务即可;
On the master:
vim /etc/my.cnf 
===============================
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
==============================
On each slave:
vim /etc/my.cnf 
======================
[mysqld]
rpl_semi_sync_slave_enabled=1
========================