centos8下搭建mysql集群
前提说明
1. 当前系统版本
[root@hhzz ~]# cat /etc/centos-release
CentOS Stream release 8
2. 当前数据库版本：mysql8
目录
1. 集群搭建之主从复制
1.1 主从复制原理
1.2 binlog和relay日志
1.3 binlog的三种模式
1.4 基于binlog主从复制
1.5 基于GTID的主从复制
1.6 主从同步延迟的原因及解决办法
2. 集群搭建之读写分离
3. 参考博客
1. 集群搭建之主从复制
1.1 主从复制原理
主从复制原理：
(1). master将数据改变记录到二进制日志（binary log）中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件（binary log events）；
(2). slave通过I/O线程读取master中的binary log events并写入到它的中继日志（relay log）；
(3). slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据（数据重放）
注意事项
. 主从服务器操作系统版本与位数一致
. master和slave数据库版本一致
. master和slave数据库中的数据一致
. master开启二进制日志，master和slave的server_id在局域网内必须唯一
1.2 binlog和relay日志
. bin-log: 将数据改变记录到二进制日志(binary log) 中，可用于本机数据恢复和主从同步
. relay log(中继)：重做中继日志，slave节点会把中继日志中的事件信息一条一条的在本地执行一次，实现主从同步，这个过程也叫做数据重放。
1.3 binlog的三种模式
1. ROW模式
日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改
. 优点：bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以ROW模式的日志的内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程或函数，以及触发器的调用和触发无法被正确复制的问题。
. 缺点：ROW模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改记录，这样会产生大量的日志内容。比如：有这样一条update语句：update product set owner_member_id=‘d’ where owner_member_id=‘a’，执行之后，日志中记录的不是这条update语句所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件，自然bin-log日志的量会很大。
2. Statement模式
Statement模式：每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。
优点：Statement模式下的优点，首先就是解决了ROW模式下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约io，提高性能。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息。
缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外，由于mysql现在发展比较快，很多新功能加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在Statement模式下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现。比如：sleep()在有些版本就不能正确复制。
3. Mixed模式
实际上就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选一种。
新版本中的Statement模式还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中对ROW模式被做了优化，并不是所有的修改都会以ROW模式来记录，像遇到表结构变更的时候就会以Statement模式来记录，如果sql语句确实就是Update或者Delete 等修改数据的语句，那么还是会记录所有行的变更。
1.4 基于binlog主从复制
1.4.1 环境
主数据库: 192.168.124.65
从数据库1：192.168.124.66
从数据库2：192.168.124.67
Atlas代理：192.168.124.188
1.4.2 每台服务器上安装mysql8
详细步骤可参考：Centos下安装mysql8.0详细步骤
1.4.3 配置主从复制
(1). 配置主库文件my.cnf
[root@hhzz tmp]# clear
[root@hhzz tmp]# vim /etc/my.cnf
##### 添加如下内容：
# 基于binlog主从复制
# 启动二进制文件
log-bin=mysql-bin
# 服务器id
server-id=65
(2). 重启mysql服务
[root@hhzz tmp]# systemctl restart mysql
(3). 主机给从机备份权限（需要先登录到mysql客户端）
[root@hhzz tmp]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'slave1'@'192.168.124.66' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to 'slave1'@'192.168.124.66' ;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
(4). 查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 886 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
(5). 从服务器配置my.cnf
[root@hhzz ~]# vim /etc/my.cnf
# 基于bin-log主从复制
# 服务器id
server-id=66
(6). 重启从服务器mysql服务
[root@hhzz ~]# systemctl restart mysql
(7). 登录到MySQL进行配置从服务器
[root@hhzz ~]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.124.65',master_port=3306,master_user='slave1',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=886,master_auto_position=0;
Query OK, 0 rows affected, 10 warnings (0.02 sec)
注意：
语句中间不要断开， master_port 为mysql服务器端口号(无引号)， master_user 为执行同步操作的数据库账户， “886” 无单引号(此处的886 就是 show master status 中看到的 position 的值，这里的mysql-bin.000002 就是 file 对应的值)。
(8). 启动从服务器复制功能
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
(9). 检查从服务器复制功能状态
mysql> show slave status\G;
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
(10). 测试（自行测试）
1.5 基于GTID的主从复制
(1). 什么是GTID
GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。MySQL主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。
GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行。GTID在一组复制中，全局唯一。 通过GTID的UUID可以知道这个事务在哪个实例上提交的。
GTID可以保证不会重复执行同一个事务，并且会补全没有执行的事务；
(2). GTID解决了什么问题？
通过GTID可以很方便的进行复制结构上的故障转移，新主设置，这就很好地解决了下面这个图所展现出来的问题。
如果没有GTID：
. Server1(Master)崩溃：根据从上show slave status获得Master_log_File/Read_Master_Log_Pos的值来看
Server1(master)最新pos是1582
Server2(Slave)是1582，已经跟上了主
Server3(Slave)是1493，没有跟上主
这时要是把Server2提升为主，Server3变成Server2的从。
如果没有全局事务ID，在Server3上执行change的时候就需要做一些计算，保证之前的事务都执行完毕了！进行主节点故障转移的时候就比较繁琐；
这个问题在5.6的GTID出现后，就显得非常的简单。由于同一事务的GTID在所有节点上的值一致，那么根据Server3当前停止点的GTID就能定位到Server2上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现，我们都不需要知道GTID的具体值，直接使用CHANGE MASTER TO MASTER_HOST=‘xxx’，MASTER_AUTO_POSITION命令就可以直接完成failover的工作。
使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。
(3). GTID在binlog的结构
Previous_gtid_log_event：Previous_gtid_log_event 在每个binlog 头部都会有。
GTID event 结构：
(4). GTID和Binlog之间的关系是？
假设有4个binlog: bin.001,bin.002,bin.003,bin.004
bin.001 : Previous-GTIDs=empty; binlog_event有：1-40
bin.002 : Previous-GTIDs=1-40; binlog_event有：41-80
bin.003 : Previous-GTIDs=1-80; binlog_event有：81-120
bin.004 : Previous-GTIDs=1-120; binlog_event有：121-160
假设现在我们要找GTID=$A，那么MySQL的扫描顺序为： 从最后一个binlog开始扫描（即：bin.004）
bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中
bin.004的Previous-GTIDs=1-120，
如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤，直到找到为止
(5). 配置GTID主从复制
5.1 修改master、slave服务器的my.cnf文件
[root@hhzz ~]# vim /etc/my.cnf
# 基于binlog主从复制
# 启动二进制文件
log-bin=mysql-bin
# 服务器id
server-id=65
# 基于GTID主从复制
# 开启GTID模式
gtid_mode=ON
# 强制gtid一致性
enforce-gtid-consistency=true
5.2 重启mysql服务
[root@hhzz ~]# systemctl restart mysql
5.3 从服务器中执行change master
在这里插入代mysql> change master to master_host='192.168.124.65',master_port=3306,master_user='slave2',master_password='123456',master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)码片
5.4 自行测试
还有可以测试一下从库停机后，主库仍在写入数据，从库恢复后能否自动同步数据，结果是肯定可以的
1.6 主从同步延迟的原因及解决办法
(1). 原因
我们知道， 一个服务器开放Ｎ个链接给客户端来连接的，这样会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个， 当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。
注意： 5.6.3 之前的IO线程仅有一个。5.6.3之后有多线程去读，速度会大幅提升，所以主从延迟也会相对少一些
(2). 解决办法
实际上主从同步延迟根本没有什么一招制敌的办法， 因为所有的SQL必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入， 那么一旦有延迟产生， 那么延迟加重的可能性就会原来越大。
当然我们可以做一些缓解的措施:
1. 分库，将主库拆分成几个表，那么主库的写并发就会降低很多，主从延迟可以忽略不计
2. 打开mysql的并发复制，如果单个库的写并发很高
3. 重写代码，插入一条数据以后，尽量不要马上去查数据，插入数据直接去更新，不要查询
4. 如果确实存在这样的业务，必须插入数据后马上查询到，对这个查询设置直连主库
(3). 判断延迟的方法
MySQL提供了从服务器状态命令，可以通过 show slave status 进行查看， 比如可以看看Seconds_Behind_Master参数的值来判断，是否有发生主从延时。其值有这么几种：
. NULL ：表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes.
. 0 ：该值为零，表示主从复制状态正常
2. 集群搭建之读写分离-Atlas
2.1 简介
Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。同时，有超过50家公司在生产环境中部署了Atlas。
2.2 Altas配置
下载Atlas会有两个版本，其中有个分表的版本，但是这个需要其他的依赖，我这边不需要分表这种需求，所以安装普通的版本
Atlas (普通) : Atlas-2.2.1.el6.x86_64.rpm
Atlas (分表) : Atlas-sharding_1.0.1-el6.x86_64.rpm
2.3 下载安装
如果网络条件允许下可直接使用如下命令下载
[root@hhzz soft]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
如果网络条件不允许条件直接下载安装包上传到服务器再进行安装
[root@hhzz opt]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
Verifying... ################################# [100%]
准备中... ################################# [100%]
正在升级/安装...
1:Atlas-2.2.1-1 ################################# [100%]
安装好了，它会默认在”/usr/local/mysql-proxy”下给你生成4个文件夹，以及需要配置的文件，如下：
[root@hhzz mysql-proxy]# ll
总用量 4
drwxr-xr-x 2 root root 75 12月 13 21:36 bin
drwxr-xr-x 2 root root 22 12月 13 21:36 conf
drwxr-xr-x 3 root root 4096 12月 13 21:36 lib
drwxr-xr-x 2 root root 6 12月 17 2014 log
bin目录下放的都是可执行文件
1. “encrypt”是用来生成MySQL密码加密的，在配置的时候会用到
2. “mysql-proxy”是MySQL自己的读写分离代理
3. “mysql-proxyd”是360弄出来的，后面有个“d”，服务的启动、重启、停止。都是用他来执行的
conf目录下放的是配置文件
conf目录：下放的是配置文件
“test.cnf”只有一个文件，用来配置代理的，可以使用vim来编辑
lib目录：下放的是一些包，以及Atlas的依赖
log目录：下放的是日志，如报错等错误信息的记录
2.4 配置
进入bin目录，使用encrypt来对数据库的密码进行加密，我的MySQL数据的用户名是atlas，密码是atlas，我需要对密码进行加密
[root@hhzz mysql-proxy]# ./bin/encrypt atlas
KsWNCR6qyNk=
配置Atlas
[root@hhzz mysql-proxy]# vim conf/test.cnf
进入后，可以在Atlas进行配置，360写的中文注释都很详细，根据注释来配置信息，其中比较重要，需要说明的配置如下：
这是用来登录到Atlas的管理员的账号与密码，与之对应的是“#Atlas监听的管理接口IP和端口”，也就是说需要设置管理员登录的端口，才能进入管理员界面，默认端口是2345，也可以指定IP登录，指定IP后，其他的IP无法访问管理员的命令界面。方便测试，我这里没有指定IP和端口登录。
#管理接口的用户名
admin-username = admin
#管理接口的密码
admin-password = admin
这是用来配置主数据的地址与从数据库的地址，这里配置的主数据库是65，从数据库是66,67
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.124.65:3306
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses = 192.168.124.67:3306@1 , 192.168.124.67:3306@2
这个是用来配置MySQL的账户与密码的，我的MySQL的用户是atlas,密码是atlas,刚刚使用Atlas提供的工具生成了对应的加密密码
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds =atlas:KsWNCR6qyNk=
这是设置工作接口与管理接口的，如果ip设置的”0.0.0.0”就是说任意IP都可以访问这个接口，当然也可以指定IP和端口，方便测试我这边没有指定，工作接口的用户名密码与MySQL的账户对应的，管理员的用户密码与上面配置的管理员的用户密码对应。
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234
#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345
2.5 启动Atlas
[root@hhzz mysql-proxy]# ./bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
测试一下Atlas服务器的MySQL状态，要确认它是关闭状态，并且使用mysql命令，进不去数据库
在Atlas代理节点安装一个MySQL。不用启动。关闭状态，主要使用mysql命令进入Atlas自己的数据库< /font>
[root@hhzz ~]# systemctl status mysql
● mysql.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysql; generated)
Active: inactive (dead)
Docs: man:systemd-sysv-generator(8)
[root@hhzz ~]#
[root@hhzz ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/tmp/mysql.sock' (111)
确认系统中自带的MySQL进不去了，使用如下命令，进入Atlas的管理模式“mysql -h127.0.0.1 -P2345 -uuser -ppwd ”，能进去说明Atlas正常运行着呢，因为它会把自己当成一个MySQL数据库，所以在不需要数据库环境的情况下，也可以进入到MySQL数据库模式。
[root@hhzz mysql-proxy]# mysql -uadmin -padmin -h127.0.0.1 -P2345
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
可以访问“help”表，来看MySQL管理员模式都能做些什么。可以使用SQL语句来访问。
mysql> select * from help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id | online backend server, ... |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| SELECT * FROM clients | lists the clients |
| ADD CLIENT $client | example: "add client 192.168.1.2", ... |
| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |
| SELECT * FROM pwds | lists the pwds |
| ADD PWD $pwd | example: "add pwd user:raw_password", ... |
| ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... |
| REMOVE PWD $pwd | example: "remove pwd user", ... |
| SAVE CONFIG | save the backends to config file |
| SELECT VERSION | display the version of Atlas |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)
也可以使用工作接口来访问，使用命令“mysql -h127.0.0.1 -P1234 -uyu -phello”
[root@hhzz mysql-proxy]# mysql -uatlas -patlas -h127.0.0.1 -P1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.81-log
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
如果工作接口可以进入了，就可以在Windows平台下，使用Navicat来连接数据库，填写对应的host，Port，用户名，密码就可以
2.6 测试
#进入Atlas的管理模式
mysql -h127.0.0.1 -P2345 -uuser -ppwd
#查看所有节点
SELECT * FROM backends;
mysql> select * from backends;
+-------------+---------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+---------------------+-------+------+
| 1 | 192.168.124.65:3306 | down | rw |
| 2 | 192.168.124.66:3306 | down | ro |
| 3 | 192.168.124.67:3306 | down | ro |
+-------------+---------------------+-------+------+
3 rows in set (0.00 sec)
修改Slave的数据，Master不会同步Slave数据。此时读取数据，观察Master和Slave的数据那个被查询到了
3. 参考博客
至少需要了解集群原理 | MySQL集群篇
MySQL8.x使用GRANT为用户赋权时报错的解决
mysql的GTID同步原理介绍及详细配置过程
MySQL主从+Atlas 实现读写分离
MySQL主从复制架构搭建问题整理
1065
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
