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)
- ###同步数据成功###