LAMP架构之mysql（3）：mysql两大线程的优化

slave端和master端直接相连。

所有节点（server123）都要作的操作：

1、vim /etc/my.cnf ###下边这两句话，无论主从节点都要写上。三个节点配置文件中没有要删的内容

gtid_mode=ON ###声明采用gtid模式
enforce-gtid-consistency=ON ###强制使用

2、/etc/init.d/mysqld restart重启服务，所有节点均要重启

3、登陆进入数据库

首先stop slave；

接着 change master to master_host='172.25.73.1',master_user='repl',master_password='westos',MASTER_AUTO_POSITION=1;

然后打开节点 start slave；

SHOW SLAVE STATUS\G; 出现两个yes就成功了

注：数据库删除命令：delete from USERS where username='2022'||username='HEHE';

效果测试：

SERVER1: INSERT INTO USERS VALUES('UTEST','TEST');

SERVER2\3: SELECT * FROM USERS;

2.gtid模式优势

假如A节点挂掉，距离A最近的节点（假设是B），会自动接手MASTER的工作，此时他复制时，会直接找到下一跳的从节点。

下一跳信息: cd /data/mysql

mysqlbinlog mysql-bin.000005 (一般是尾数最大的)，最后有下一跳的信息

slave端，有master的信息

cd /data/mysql

cat master.info 查看

因此，gtid模式总体优于主从复制模式。

2、基于gtid模式的半同步复制

1.半同步复制的优势

（1）AFTER_COMMIT模式：存在于mysql5.6中

master端数据发生变化后，二进制文件binlog会被发送到slave端，等slave端把该binlog数据进行备份（不一定什么时候会回放，也就是更新到slave端）后，会给master反馈一个ack消息。但是master进行egine commit时并不在乎slave的ack。

因此极有可能出现：master都提交了，slave节点都没有备份成功，不存在相应数据。master挂了以后slave接管，此时用户之前保存的数据都查不到了，影响用户体验。

（2）AFTER_SYNC模式：存在于mysql5.7中

可以看出AFTER_SYNC的引擎提交功能，必须受到slave端的ack确认后，才能进行，因此这样能解决之前的问题

2.半同步模式部署

（1）安装插件

登陆数据库，在数据库里操作：

master端（server1）：install plugin rpl_semi_sync_master soname 'semisync_master.so';

slave端（server2、3）： install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

show plugins; 察看是否安装成功

（2）修改相关参数

设置参数启用半同步，并带有热生效，即修改后不需要重启服务

master：set global rpl_semi_sync_master_enabled=1;

slave：set global rpl_semi_sync_slave_enabled=1;

show variables like 'rpl%'; 查看节点参数设置情况，可以看到rpl_semi_sync_master（slave）_enabled 已经设置成功

在这里rpl_semi_sync_master_timeout 是最大等待时间，100000ms，即10s后，master始终未等到来自skave的ack回应，则直接转为异步复制模式。

此时在两个slave节点查询节点状态： show status like 'rpl%';

会发现节点状态都是off，处于关闭状态，此时我们要重启IO线程，使节点生效。

stop slave IO_THREAD;

start slave IO_THREAD;

show status like 'rpl%'; 状态变为on

在master端可以通过 show status like 'rpl%'; 对节点简单管理

Rpl_semi_sync_master_clients 表示有几个slave节点

Rpl_semi_sync_master_yes_tx 表示复制成功的次数

Rpl_semi_sync_master_no_tx 表示复制失败的次数

（3）效果测试

关闭serer2、3的进程： stop slave IO_THREAD;

MASTER端插入新的数据：INSERT INTO USERS VALUES('0941','0941');

此时master端等了半天才反馈ok，这是因为slave端线程关闭以后，超过10s，MASTER才把复制改为异步复制，如果一直不想改复制模式，就把10秒改为无穷大

此时master端查看节点状态： show status like 'rpl%';

可以看到master端的slave节点变成0了，并且数据复制失败了。

server2、3重新打开线程 start slave IO_THREAD;

master端： show status like 'rpl%'; 可以看到slave节点恢复成了两个。

SERVER2、3查看数据表，内容是同步的

SELECT * FROM WESTOS.USERS;

（4）设置开自启动

所有节点都需要操作： vim /etc/my.cnf

server2\3： rpl_semi_sync_slave_enabled=1

server1： rpl_semi_sync_master_enabled=1

不需要重启mysql，因为有热生效。

二、mysql线程优化

1、延迟复制

(1)修改延迟复制参数

延迟复制是指从节点接收binlog后，等待一段时间再进行回放。

万一主节点误操作删掉重要信息，还有时间可以去从节点做个备份。

直接对从节点进行操作，这里用server2，登陆数据库状态。

STOP SLAVE SQL_THREAD;

change master to master_delay=30;

START SLAVE SQL_THREAD;

show slave status\G;

可以看到参数修改成功

（2）效果测试

master端新增数据 INSERT INTO WESTOS.USERS VALUES('1113','1113');

分别在server2、3查看 SELECT * FROM WESTOS.USERS;

结果server2要等30秒才有，server3不用等

注：在server2上查看节点状态 show slave status\G; 其中

Seconds_Behind_Master:表示过去了多久

SQL_Remaining_Delay: 表示剩下多久才更新

2、并行复制

（1）并行复制的部署

server2：

首先 show processlist; 查看未经优化前进程状态

server2： vim /etc/my.cnf

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

/etc/init.d/mysqld restart 重启数据库

效果查看：

server2登陆数据库后，show processlist;

可看到很多作为协调的进程，16个

3、慢查询

（1）设置慢查询功能

应用场景：数据库查询时，有些命令运行半天没有回应，这类命令就算是异常命令，需要记录。这里在server1进行操作

server1：show variables like 'slow%'; 查看服务状态，目前是关闭状态

打开服务：

set global slow_query_log=1;

show variables like 'slow%'; 查看服务是否打开成功