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

mysql的半同步复制

2012-12-05 20:39 本站整理 浏览(10)

mysql的半同步复制:

一、测试环境:

两台redhat linux5.8虚拟机;

Master:

IP地址:172.16.77.77

MySQL版本:5.5.28

操作系统:RHEL5.8

Slave:

IP地址:172.16.12.7

MySQL版本:5.5.28

操作系统:RHEL5.8;

二、准备工作

1、编译安装mysql,指定数据路径为/data/mydata;用户为mysql(路径和用户都需另行创建);

2、为mysql提供服务脚本,配置文件等等,修改/data/mydata的属主属组为mysql用户(步骤略);

3、分别在两台主机上执行如下命令:

#hwclock -s

同步主机的时间

三、mysql半同步复制的概述

所谓半同步复制,即给主服务器设定等待从服务器来同步的有效时长,在有效时长内主服务器将等待从服务器同步复制数据;若超出时长,主服务器将不再等待从服务

器同步复制数据,从而切换为异步复制数据。

四、设置半同步复制步骤:

1、这里只用一台从服务器,主从配置的设定按照之前配置的主从复制设置过程即可(此处略),要设置mysql的半同步复制,还需安装两个模块:

# cd /usr/local/mysql/lib/  
# ls  
libmysqlclient.a     libmysqlclient_r.so.18      libmysqlclient.so.18      libmysqld-debug.a       plugin  
libmysqlclient_r.a   libmysqlclient_r.so.18.0.0  libmysqlclient.so.18.0.0  libmysqlservices.a  
libmysqlclient_r.so  libmysqlclient.so           libmysqld.a               libtcmalloc_minimal.so  
# cd plugin/  
# ls  
adt_null.so     auth_test_plugin.so  libdaemon_example.so  qa_auth_interface.so  semisync_slave.so  
auth.so         daemon_example.ini   mypluglib.so          qa_auth_server.so  
auth_socket.so  debug                qa_auth_client.so     semisync_master.so
如上所示:在mysql的安装目录下/lib/plugin下有两个模块:semisync_master.so semisync_slave.so;前者是需要安装在主服务器上的,后者安装在从服务器上,安装了这两个模块后,才能对mysql进行半同步复制。

2、主服务器上

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';   
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';  
+------------------------------------+-------+  
| Variable_name                      | Value |  
+------------------------------------+-------+  
| 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    |   
+------------------------------------+-------+  
4 rows in set (0.01 sec)  
################  
查看安装semisync_master.so模块后生成的系统参数:  
rpl_semi_sync_master_enabled:是否开启主服务器上的半同步复制  
rpl_semi_sync_master_timeout:半同步复制的有效时长,单位为毫秒  
rpl_semi_sync_master_trace_level:传输级别  
rpl_semi_sync_master_wait_no_slave:当主服务器没有发现有从服务器在线时,是否依旧按照设定的时长等待从服务器  
################  
 
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;    
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;  
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';  
+------------------------------------+-------+  
| Variable_name                      | Value |  
+------------------------------------+-------+  
| rpl_semi_sync_master_enabled       | ON   |   
| rpl_semi_sync_master_timeout       | 1000 |   
| rpl_semi_sync_master_trace_level   | 32    |   
| rpl_semi_sync_master_wait_no_slave | ON    |   
+------------------------------------+-------+  
4 rows in set (0.01 sec)  
################  
开启主服务器上的半同步复制功能;并将超时时长设置为1000毫秒  
################  
 
mysql> SHOW GLOBAL STATUS  LIKE '%semi%';  
+--------------------------------------------+-------+  
| Variable_name                              | Value |  
+--------------------------------------------+-------+  
| Rpl_semi_sync_master_clients               | 0     |   
| 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)  
################  
在从服务器没有开启前主服务器半同步复制的变量状态:  
Rpl_semi_sync_master_clients:半同步复制中从服务器的在线个数  
Rpl_semi_sync_master_status :主服务器的半同步复制状态  
################  
 
3、从服务器上  
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';  
+---------------------------------+-------+  
| Variable_name                   | Value |  
+---------------------------------+-------+  
| rpl_semi_sync_slave_enabled     | OFF   |  
| rpl_semi_sync_slave_trace_level | 32    |  
+---------------------------------+-------+  
2 rows in set (0.00 sec)  
 
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';  
+----------------------------+-------+  
| Variable_name              | Value |  
+----------------------------+-------+  
| Rpl_semi_sync_slave_status | OFF   |  
+----------------------------+-------+  
1 row in set (0.00 sec)  
#################  
从服务器上安装模块后的参数:  
rpl_semi_sync_slave_enabled:是否开启从服务器上半同步复制  
rpl_semi_sync_slave_trace_level:传输级别  
变量状态:  
Rpl_semi_sync_slave_status:从服务器上半同步复制的状态  
#################  
 
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;   
Query OK, 0 rows affected (0.01 sec)  
 
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;  
Query OK, 0 rows affected (0.02 sec)  
Query OK, 0 rows affected (0.01 sec)  
 
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';  
+---------------------------------+-------+  
| Variable_name                   | Value |  
+---------------------------------+-------+  
| rpl_semi_sync_slave_enabled     | ON    |  
| rpl_semi_sync_slave_trace_level | 32    |  
+---------------------------------+-------+  
2 rows in set (0.00 sec)  
 
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';  
+----------------------------+-------+  
| Variable_name              | Value |  
+----------------------------+-------+  
| Rpl_semi_sync_slave_status | ON    |  
+----------------------------+-------+  
1 row in set (0.01 sec)  
###开启从服务器半同步复制,并开启slave上的io_thread线程###
五、测试半同步复制

a、所有参数已配置完成,从服务器的半同步复制已开启,在主服务器查看链接状态:

mysql> SHOW GLOBAL STATUS  LIKE '%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)  
###Clients的连接数已更改为1,从服务器连接正常###
b、在主服务器上之前创建的sumoan表中插入数据:

mysql> insert into sumoan values (1,'fuyaxu');insert into sumoan values (2,'weiyang');  
Query OK, 1 row affected (0.01 sec)  
Query OK, 1 row affected (0.01 sec)  
 
mysql> select *from sumoan;  
+----+---------+  
| id | name    |  
+----+---------+  
|  1 | fuyaxu  |   
|  2 | weiyang |   
+----+---------+  
2 rows in set (0.00 sec)  
###数据已完成插入,查看主服务器上半同步复制的状态信息###  
mysql> SHOW GLOBAL STATUS  LIKE '%semi%';  
+--------------------------------------------+-------+  
| Variable_name                              | Value |  
+--------------------------------------------+-------+  
| Rpl_semi_sync_master_clients               | 1     |   
| Rpl_semi_sync_master_net_avg_wait_time     | 1673  |   
| Rpl_semi_sync_master_net_wait_time         | 3347  |   
| 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      | 786   |   
| Rpl_semi_sync_master_tx_wait_time          | 1572  |   
| 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)
c、在从服务器上查看是否已同步到数据;

mysql> show tables;  
+----------------+  
| Tables_in_test |  
+----------------+  
| fuyaxu         |  
| moziyan        |  
| sumoan         |  
+----------------+  
3 rows in set (0.00 sec)  
 
mysql> select *from sumoan;  
+----+---------+  
| id | name    |  
+----+---------+  
|  1 | fuyaxu  |  
|  2 | weiyang |  
+----+---------+  
2 rows in set (0.00 sec)  
###同步数据成功###
本文出自 “当了灵魂的躯体” 博客,请务必保留此出处http://7linux.blog.51cto.com/6110108/1079736