企业运维 --- LAMP架构（ mysql [3] 配置管理、组复制、mysql路由器配置)

一、mysql配置管理

1.延迟复制

进入slave端数据库;
先停止slave，然后设定延迟复制30s

查看slave状态，设定成功（30s后同步）；
sql是单线程 ，只有slave端才有，因为mysql主从复制本身含有延迟；当主server操作有误，可以进行数据回滚

测试：server1向数据库的表中插入一条数据

查看slave端状态，可以看到当有数据需要复制时候，会显示需要等待的时间

当30s过后，slave端才会成功复制master端的数据（二进制日志）


查看slave端的服务器进程

2.并行复制

修改slave端的配置文件

添加mysql的配置参数；
slave_paraller_workers ：设置从库SQL线程并行重放events(事务)的worker线程数量（在MySQL cluster中不支持多线程复制，如果设置了则会自动忽略）；
（当值设定不为0时）激活成为协调线程，负责转发给worker处理

在进行主从服务器复制时，从属服务器会创建两个日志文件。 默认情况下，这两个日志是创建在数据目录下名为 master.info 和 relay-log.info 的文件；

master.info：日志文件记录了从节点的连接信息，例如用户名，密码等。同时包括主节点信息（当mysql启动时，它必须知道从主库哪里开始复制，master.info就是记载这样的信息）；

查看master.info日志内容，可以看到该slave（server2）的master是server1


查看 relay-log.info 日志内容；
中继日志：从服务器的I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致

继续修改slave端的配置文件;
master_info_repository有两个值，分别是file和table，该参数决定了slave记录master的状态，如果参数是file，就会创建master.info文件，如果参数值是table，就在mysql中创建slave_master_info的表(由数据库管理)。

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能。

MySQL复制包括两部分，IO线程 和 SQL线程。IO线程主要是用于拉取接收Master传递过来的binlog，并将其写入到relay log ；SQL线程主要负责解析relay log，并应用到slave中。不管怎么说，IO和SQL线程都是单线程的，然后master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了。目前都是在SQL线程上实现了多线程，来提升slave的并发度。

LOGICAL CLOCK：基于组提交的并行复制方式，把事件归类为组，并行提交（ 注意：当操作针对一个数据库时，还是单线程）

重启mysql

slave端查看进程

3.慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，

显示慢查询的变量值；
slow_query_log =1 ：开启慢查询日志

查询一下多少秒会加入慢查询日志，需要十秒

通过在MySQL中执行select sleep(N)可以让此语句运行N秒钟

查看slave端日志，可以看到这个慢查询日志


在slave的数据库表中

可以看到slave_master_info 和 slave_relay_log_info 的表（配置文件中设定的）

查看表中数据

二、mysql组复制

MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。组复制是MySQL5.7版本出现的新特性，它提供了高可用、高扩展、高可靠的MySQL集群服务。MySQL组复制分单主模式和多主模式，mysql 的复制技术仅解决了数据同步的问题，如果 master 宕机，意味着数据库管理员需要介入，应用系统可能需要修改数据库连接地址或者重启才能实现。组复制在数据库层面上做到了，只要集群中大多数主机可用，则服务可用，也就是说3台服务器的集群，允许其中1台宕机。

配置之前，需要将server1/2/3的数据库先停止；
清除/data/mysql目录的内容



可以参考官方文档的讲解
查看

编辑server1的主配置文件

1、对于组复制，数据必须存储在InnoDB的事务存储引擎中。使用其他存储引擎(包括临时内存存储引擎)可能会导致Group Replication中的错误。
禁用存储引擎系统变量的方法：disabled_storage_engines=“MyISAM, BLACKHOLE, FEDERATED, ARCHIVE, MEMORY”

2、以下这些设置将服务器配置为使用编号为1的唯一标识符，以启用全局事务标识符（GTID），并将复制元数据存储在系统表而不是文件中。此外，它指示服务器打开二进制日志记录，使用基于行的格式并禁用二进制日志事件校验和。

3、配置服务器的组复制设置。

• plugin-load-add 将 Group Replication插件添加到服务器在启动时加载的插件列表中。在生产部署中，这比手动安装插件更可取；
• 配置组复制组名告诉插件它要加入或创建的组名为“aaaaa-aaaaa-aaxx，组复制组名必须为有效的UUID；
• 将组复制启动变量设置为off会指示插件在服务器启动时不会自动启动操作。这在设置组复制时非常重要，因为它确保我们可以在手动启动插件之前配置服务器。一旦配置了成员，就可以将组复制启动时启动设置为on，以便组复制在服务器启动时自动启动；
• 配置组复制本地地址可以设置成员与组内其他成员进行内部通信时使用的网络地址和端口。Group Replication将此地址用于涉及组通信引擎的远程实例的内部成员到成员连接。注意：此地址必须与用于SQL的主机名和端口不同，并且不能用于客户机应用程序。它只能用于运行组复制时组成员之间的内部通信；
• 配置组复制组种子，设置组成员的主机名和端口，新成员使用这些主机名和端口建立到组的连接。这些成员被称为种子成员。一旦建立了连接，就会在性能模式中列出组成员信息；

mysql --verbose：查看数据库配置


server1重新进行无密码的数据库初始化，并且指定用户为mysql；
开启数据库

开始设置用户凭证

确保mysql各节点已经添加解析

进入数据库

首先，使用SET SQI_LOG_BIN=0 禁用二进制日志记录;
然后创建一个具有REPLICATION SLAVE权限的MysQL用户，用户名rpl user 密码为westos；
flush privileges 命令：本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令；
授权完成后，再打开二进制日志记录;

配置完用户后，使用CHANGE MASTER To语句配置服务器，以便在下一次需要从另一个成员恢复其状态时使用给定的group_replication恢复复制通道凭据

分布式恢复是加入组且不具有与组成员相同事务集的服务器所采取的第一步。如果没有为组复制恢复复制通道和rpl用户正确设置凭证，服务器将无法连接到提供方成员并运行分布式恢复流程以与其他组成员保持同步，因此最终无法加入组

第一次启动一个组的过程称为bootstrapping；
最后的两条SET命令只需要在一个引导节点输入就行，后面的节点不需要执行，否则会各自开启一个新的集群

配置完成，查看插件

查看集群；
该表中的信息确认组中有一个唯一标识ID的成员，该成员ONLINE，并且在server1监听端口3306上的客户端连接。

server2编辑配置文件

与server1不同的地方在于server_id和ip

server2也重新进行无密码的数据库初始化，并且指定用户为mysql；
开启数据库

前面的指令和server1的相同，最后不需要执行两个SET指令


配置完成，查看集群；

server2编辑配置文件

进行无密码的数据库初始化，并且指定用户为mysql；
开启数据库

进行配置

配置完成，查看集群；


为确保数据库安全，进入数据库，使用alter user 语句来设定数据库的root用户密码为westos

此时无论集群中那个成员登录数据库，都需要密码

此时测试一下：在任意一台主机写入数据：

在其他主机都可以查看到，说明主复制集群搭建成功

三、mysql路由器配置

mysqlrouter介绍：
MySQL Router是MySQL官方提供的一个轻量级MySQL中间件，用于取代以前老版本的SQL proxy。
既然MySQL Router是一个数据库的中间件，那么MySQL Router必须能够分析来自前面客户端的SQL请求是写请求还是读请求，以便决定这个SQL请求是发送给master还是slave，以及发送给哪个master、哪个slave。这样，MySQL Router就实现了MySQL的读写分离，对MySQL请求进行了负载均衡。

因此，MySQL Router的前提是后端实现了MySQL的主从复制。MySQL Router很轻量级，只能通过不同的端口来实现简单的读/写分离，且读请求的调度算法只能使用默认的rr(round-robin)，更多一点、更复杂一点的能力都不具备。所以，在实现MySQL Router时，需要自行配置好后端MySQL的高可用。

启动一个新的虚拟机server4，server4作为路由器的配置；
从服务器上获取mysql路由的rpm包

安装

编辑配置文件

通过不同的端口，区分mysql读写分离模块；
rw请求的路由策略设定First available 模式，表示当server1的数据库可以提供服务时，就不会跳转到其他mysql主机

启动mysql路由服务，查看端口

在数据库创建用于远程连接的用户，由于做了组复制，在一个主机创建，其他主机会自动复制

真机安装mariabd服务，进行7001端口（只读模块）测试

客户端连接到远程主机server4上的7001端口，成功进入数据库（客户端访问路由器，由路由器对后端的mysql服务器（集群 ）进行调度）

各主机都安装lsof，lsof命令可以用来列出被各种进程打开的文件信息

查看7001端口的进程，可以看到此时客户端（36）的连接进程


server1查看3306（数据库默认端口），无其他连接

server2查看3306（数据库默认端口），可以看到与server4（mysql路由器）的连接进程，由于server4配置的ro模块设置的策略是RR轮叫，说明此时提供服务的是server2

当客户端退出重新连接

在server3上可以看到与server4（mysql路由器）的连接进程，说明此时提供服务的是server3

客户端进行7002端口（rw模块）测试

由于server4配置的rw模块设置的策略是first_available，说明此时提供服务的是server1


但是当server1的数据库关闭之后

客户端连接server4会出现几秒钟的中断，然后又可以成功进入数据库

server2查看3306（数据库默认端口），可以看到与server4（mysql路由器）的连接进程，说明只有当server1主机down掉，否则不会更换主机提供服务

myql路由器的好处就在于一台主机down掉，对客户端的访问请求不会有很大的影响，但是如果客户端直接连接的是某个数据库主机，那么当这台主机down掉，也就无法为客户端提供服务

server1开启数据库

客户端再次进行连接测试

仍然是server2提供服务（不中断）