Mysql半同步复制详细配置
来源:互联网 发布:单片机方案公司 编辑:程序博客网 时间:2024/06/03 20:50
MySQL 5.5之前的复制都是异步的,主服务器在将更新操作写入二进制日志文件中后,不用管从服务器是否已经完成复制,就可以自由处理其它事务处理请求。异步复制能提供较高的性能,但无疑易造成主/从服务器数据的不一致。MySQL 5.5开始引入半同步复制功能,此功能是由google开发的一个插件实现的。半同步复制要求主库提交的每一个事务,至少有一个备库成功接收后,才能继续提交下一个。
半同步复制的概念详解:
当slave主机连接到master时,能够查看其是否已开启半同步复制功能。
当master上开启半同步复制的功能时,至少应该有一个slave开启此功能。此时,一个线程在master上提交事务将受到阻塞,直到得知一个已开启半同步复制功能的slave已收到此事务的所有事件,或等待超时。
当一个事务的事件都已写入relay-log中且已刷新到磁盘,slave才会告知已收到。在 master实例上,有一个专门的线程(ack_receiver)接收备库的响应消息。
如果等待超时,也就是master没被告知已收到,此时master会自动转换为异步复制模式。当至少一个半同步的slave赶上了,master与其slave自动转换为半同步复制。
半同步复制的功能要在master,slave都开启,若只开启一边,它依然为异步复制。
配置如下:
master:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 安装插件
SHOW GLOBAL VARIABLES LIKE '%semi%'; 查看参数
SET GLOBAL rpl_semi_sync_master_enabled = on; 修改参数rpl_semi_sync_master_enabled,rpl_semi_sync_master_timeout
SET GLOBAL rpl_semi_sync_master_timeout = 5000; #等待从服务器确认的超时时长,单位为毫秒,超时则转为异步模式
vi /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 5000
slave:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = on;
然后只需重启io thread:
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
vi /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled = on
主库:
master> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.20 sec)
master> 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.00 sec)
master> set global rpl_semi_sync_master_enabled = on;
Query OK, 0 rows affected (0.01 sec)
master> set global rpl_semi_sync_master_timeout = 5000;
Query OK, 0 rows affected (0.00 sec)
master> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 5000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
[root@master ~]# vim /etc/my.cnf
[mysqld]
...
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 5000
从库:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.11 sec)
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> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like 'rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
[root@slave mydata]# vim /etc/my.cnf
[mysqld]
...
rpl_semi_sync_slave_enabled = on
主库:
master> 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 | #表示通过半同步复制到从库的事务数
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
半同步复制的概念详解:
当slave主机连接到master时,能够查看其是否已开启半同步复制功能。
当master上开启半同步复制的功能时,至少应该有一个slave开启此功能。此时,一个线程在master上提交事务将受到阻塞,直到得知一个已开启半同步复制功能的slave已收到此事务的所有事件,或等待超时。
当一个事务的事件都已写入relay-log中且已刷新到磁盘,slave才会告知已收到。在 master实例上,有一个专门的线程(ack_receiver)接收备库的响应消息。
如果等待超时,也就是master没被告知已收到,此时master会自动转换为异步复制模式。当至少一个半同步的slave赶上了,master与其slave自动转换为半同步复制。
半同步复制的功能要在master,slave都开启,若只开启一边,它依然为异步复制。
配置如下:
master:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 安装插件
SHOW GLOBAL VARIABLES LIKE '%semi%'; 查看参数
SET GLOBAL rpl_semi_sync_master_enabled = on; 修改参数rpl_semi_sync_master_enabled,rpl_semi_sync_master_timeout
SET GLOBAL rpl_semi_sync_master_timeout = 5000; #等待从服务器确认的超时时长,单位为毫秒,超时则转为异步模式
vi /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 5000
slave:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = on;
然后只需重启io thread:
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
vi /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled = on
主库:
master> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.20 sec)
master> 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.00 sec)
master> set global rpl_semi_sync_master_enabled = on;
Query OK, 0 rows affected (0.01 sec)
master> set global rpl_semi_sync_master_timeout = 5000;
Query OK, 0 rows affected (0.00 sec)
master> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 5000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
[root@master ~]# vim /etc/my.cnf
[mysqld]
...
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 5000
从库:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.11 sec)
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> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like 'rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
[root@slave mydata]# vim /etc/my.cnf
[mysqld]
...
rpl_semi_sync_slave_enabled = on
主库:
master> 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 | #表示通过半同步复制到从库的事务数
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
阅读全文
0 0
- Mysql半同步复制详细配置
- MySQL半同步复制配置
- mysql配置半同步的复制
- mysql的半同步复制的配置
- MySQL半同步复制
- mysql半同步复制
- MySQL半同步复制
- MySQL半同步复制
- MySql-半同步复制
- MySQL半同步复制
- MySQL半同步复制
- MySQL半同步复制
- mysql半同步复制
- MySQL半同步复制
- MySQL半同步复制
- mysql半同步复制
- ssdb 主从同步复制配置详细步骤
- ssdb 主从同步复制配置详细步骤
- JS For应用!
- scala代码风格指南--<命名规范>
- mac OS 使用SVN命令行工具报 xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools)
- 百练_4096:信号转换
- 关于Promise,Generator,async / await 对异步的处理
- Mysql半同步复制详细配置
- Unity3D 利用Highlighing System插件使物体轮廓高亮显示
- java用户metadata-extractor读取照片信息错误Exception in thread “main” java.lang.NoClassDefFoundError: com/adobe
- v_layout
- Dynamic Web Module 3.0 requires Java 1.6 or newer
- MVC过滤器的使用
- A. Rational Resistance----贪心
- 命令行更新scikit-learn
- 实时加载页面的内容loading(主要针对图片)