LAMP架构之6——MySQL主从复制及优化方法

LAMP架构之6——MySQL主从复制及优化方法

目录

一、主从节点的IO缺陷及优化方法 (IO -> relaylog -> disk)

1、 安装插件

2、更改配置文件——设置启动即生效

3、测试：

4、故障模拟：

二、GTID的两种模式——AFTER_COMMIT和AFTER_SYNC

三、延迟复制

四、主从节点的SQL缺陷及优化方法 (SQL ->relaylog -> update) 

1、并行复制参考资料

2、修改配置文件

五、mysql的慢查询

---

一、主从节点的IO缺陷及优化方法 (IO -> relaylog -> disk)

原始主从结构的IO不稳定，因为master采用异步的方式发送数据，如果slave端没有接收到的话，会导致主从数据不一致。因此需要“无损复制”，即“半同步复制”。

半同步复制的文档说明：

1、 安装插件

在master和slave端安装的插件是不一样的。

master :  INSTALL PLUGIN rpl_semi_sync_master SonAME 'semisync_master.so';

slave  :  INSTALL PLUGIN rpl_semi_sync_slave SonAME 'semisync_slave.so';

进入数据库里边执行安装命令。

master中：

SET GLOBAL rpl_semi_sync_master_enabled = 1;    #激活半同步参数
SET GLOBAL rpl_semi_sync_master_timeout = N;    #master呼叫slave时的等待时间为毫秒
SHOW VARIABLES LIKE 'rpl_semi_sync%';        #查看变量参数有没有改变
SHOW STATUS LIKE 'Rpl_semi_sync%';           #查看当前状态有没有改变

slave中：

SET GLOBAL rpl_semi_sync_slave_enabled = 1;        #激活从节点的半同步参数

但是slave端激活后，半同步的状态并没有生效。

需要先重启slave中的IO线程才会生效

STOP SLAVE IO_THREAD;        #停止slave的IO线程
START SLAVE IO_THREAD;       #启动slave的IO线程

重启IO线程完成后，在master中查看半同步的状态，可以看到增加了两个半同步的从节点。

2、更改配置文件——设置启动即生效

以上更改是在mysql数据库中生效的，并没有改变mysql的配置文件。重启mysql后将会恢复原样，因此需要在配置文件中做如下更改：

master中做如下更改：

rpl_semi_sync_master_enabled=1

 slave端做如下更改：

rpl_semi_sync_slave_enabled=1

3、测试：

在master端的mysql数据库中新增一条信息。查看master的半同步状态。

三行分别表示：从节点有2个；不成功同步的信息为0条；成功同步的信息为1条。

4、故障模拟：

当slave端坏掉后(停掉slave端的IO线程)，master端有数据更改时会等待

查看master的异步同步状态：和之前的相比，master检查到的slave端已经为0了；半同步状态已关闭(同时切换为异步模式)；且传同步败信息为1条。

重新激活slave端的IO线程()，在master端可以立即看到异步同步的信息已更新为正常。

master和slave的异步同步重新恢复正常后，在slave端可以立即查看到同步过来的信息。

二、GTID的两种模式——AFTER_COMMIT和AFTER_SYNC

 AFTER_COMMIT(5.6版本)模式：

 AFTER_SYNC(5.7版本)模式：

 两种模式的区别是：

SYNC比COMMIT多出一个步骤。SYNC会在收到slave端的反馈确认已成功接收信息后，才会向用户返回“OK”值；而COMMIT则在发送完成后就显示“OK”，并没有确认slave端是否成功接收。

三、延迟复制

当master中的输入数据后，不会立即同步到slave端。而是可以有“撤回/后悔”的机会。这就需要引入延迟同步机制。

设置：

在slave中执行如下 *** 作，时间设置为30秒(也就是30秒的后悔时间)。需要先关闭SQL线程。

CHANGE MASTER TO MASTER_DELAY = 30;            #30秒延迟同步

更改完成后在slave端查看SQL延迟，显示30秒。证明修改成功。

测试：

在master中新增一条数据，查看slave端。

server3没有设置延迟，所以master发生更改后会立即同步到server3；而server2做了30秒延迟，并不会立即同步到server2中，查看server2的状态，以下标注的第一行表示：已经等待时间，第二行表示：剩余等待时间。

四、主从节点的SQL缺陷及优化方法 (SQL ->relaylog -> update) 

SQL默认是单线程的，需要同步的数据较多时会造成很高的延迟，因此需要开启多线程模式。

1、并行复制参考资料

MySQL 5.7新特性：并行复制原理（MTS）_总有些事，值得你去努力-CSDN博客_并行复制众所周知，MySQL的复制延迟是一直被诟病的问题之一，在MySQL 5.7版本已经支持“真正”的并行复制功能，官方称为为enhanced multi-threaded slave（简称MTS），因此复制延迟问题已经得到了极大的改进。总之，MySQL 5.7版本后，复制延迟问题永不存在。一、MySQL 5.6并行复制架构从MySQL 5.6.3版本开始就支持所谓的并行复制了，但是其并行只是基...https://blog.csdn.net/andong154564667/article/details/82117727

2、修改配置文件

slave：

slave-parallel-type=LOGICAL_CLOCK            #表示组提交
slave-parallel-workers=16                    #表示开启16个SQL线程
master_info_repository=TABLE                 #表示存放master信息是以表的方式还是文件方式，默认是文件方式
relay_log_info_repository=TABLE              #同上
relay_log_recovery=ON                        #激活中继日志的恢复

修改完成后重启mysql，可以查询到SQL线程已经增加到16个。

show processlist;            #显示mysql的线程信息

存放master信息由之前的存放在文件中(master.info和relay-log.info)更改为存放在数据表中(slave_master_info和slave_relay_log_info)。

这样的好处是：在多线程的情况下，master的信息变动很频繁，如果是以文件形式存储的话，则会产生大量的读写 *** 作；放在table中则可以避免这个问题。

五、mysql的慢查询

自动查询执行比较慢的mysql语句，并记录在指定文件中。

慢查询功能默认是关闭的。

激活慢查询功能：

set global slow_query_log = 1;        #激活慢查询功能

慢查询功能的默认设置阈值为10秒，即一条sql语句执行时间超过10秒后就认为是慢语句。

SHOW VARIABLES LIKE 'long%';

以一条执行时间为11秒的测试语句为例，执行完毕后，会被检测为慢执行语句 ，会将此语句默认保存到文件中