【经验分享】MySQL集群部署一：主从模式

前言

MySQL的部署模式常见的包括以下几种：

• 独立服务器部署
• 主从复制部署
• 高可用性集群（HA）
• 分片
• 容器化部署

本文主要介绍MySQL集群模式的主从复制部署模式，觉得写得不错的可以点赞支持一下。

一、基本介绍

1.1、概念

主从复制（Master-Slave Replication）：主从复制是最简单的 MySQL 集群架构，将读写操作分离到不同的服务器上。主服务器接收所有的写操作，并将更改记录到二进制日志中，而从服务器则定期地检查主服务器的二进制日志并应用这些更改。主从复制适用于高读写比例，对数据一致性要求不高的场景。简而言之，在主从复制中，数据变更主要通过binlog来实现同步。

1.2、执行流程

执行流程（标红画线）：

1、从库不断试探主库的二进制日志文件，如果这个文件有更新则发送请求到主库获取主库中更新的内容；

2、当向主库中写数据时：包括增加、删除、修改、建库建表等等；

3、主库将写命令记录到二进制日志文件中，并更新二进制日志文件的偏移量；

4、如果从库试探主库的二进制日志文件发现偏移量与从库记录的偏移量值不同，则标识主库有更新，那么就启动IO线程向主库请求从某个偏移量开始到二进制日志文件结束位置之间的所有数据；

5、主库根据从库请求偏移量位置来推送数据到从库中，然后从接收到数据以后，会更新从库所记录的偏移量位置；

6、从库获取到主库的数据后，将这些命令数据写入到中继文件中，然后唤醒SQL线程同时让当前IO线程挂起（休眠等待）；

7、SQL线程根据记录在中继日志文件的偏移量，读取中继日志文件中的命令；

8、SQL线程拿到命令以后在本地数据库进行回放（从库中执行主库更新的SQL语句），回放完成后当前SQL线程挂起（等待下一次操作）；

二、部署

本文主要根据三台服务器来部署一主二从的MySQL集群，服务器布置如下所示：

• 192.168.100.101:3306（主）
• 192.168.100.102:3306（从）
• 192.168.100.103:3306（从）

mysql数据库版本：8.0.34
准备好安装包 mysql-8.0.34-linux-glibc2.17-x86_64.tar

2.1、通用配置

通用配置即为在101、102、103三台服务器上都安装MySQL，并进行基本配置。

# 1、创建目录/opt/mysql，并将安装包上传至当前目录
cd /opt/mysql

# 2、解压安装包
tar -zxvf mysql-8.0.34-linux-glibc2.17-x86_64.tar.gz

# 3、将安解压后的安装包移动到/usr/local/目录下，并改个名字
mv mysql-8.0.34-linux-glibc2.17-x86_64 /usr/local/mysql-8

# 4、创建配置文件 my.cnf，并输入以下内容
vim my.cnf
# *****************************************************************
#-------------------------文本内容分割线----------------------------
[mysqld]
basedir=/usr/local/mysql-8
datadir=/usr/local/mysql-8/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8
# 忽略大小写配置 1忽略
lower_case_table_names=1
log-error=/usr/local/mysql-8/data/mysql.log
pid-file=/usr/local/mysql-8/data/mysql.pid

# 默认使用mysql_native_password插件认证
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set = utf8
# 退出保存，首先按 【esc】然后 【:wq】保存并退出
#-------------------------文本内容分割线----------------------------
# *****************************************************************

# 5、在 /usr/local/mysql-8 目录下创建一个 data 文件夹
cd /usr/local/mysql-8
mkdir data

# 6、增加用户组 mysql：
sudo groupadd mysql

# 7、在用户组下增加用户 mysql：
sudo useradd -r -g mysql mysql

# 8、检查用户组、用户是否存在，存在会输出：mysql : mysql
groups mysql

# 9、初始化mysql
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-8/ --datadir=/usr/local/mysql-8/data/

# 10、将mysql加入到可控制启动服务的文件夹内，并命名mysql（service可控制的服务名）：目的：可用 service mysql start命令来控制启动 mysql
cd /usr/local/mysql-8/support-files
cp mysql.server /etc/init.d/mysql

# 11、增加mysql配置
vim /etc/profile
# *****************************************************************
#-------------------------文本内容分割线----------------------------
export MYSQL_HOME=/usr/local/mysql-8
export PATH=$PATH:$MYSQL_HOME/bin
# 退出保存，首先按 【esc】然后 【:wq】保存并退出
#-------------------------文本内容分割线----------------------------
# *****************************************************************

# 12、配置生效
source /etc/profile

# 13、启动mysql
service mysql start

# 14、查看初始MySQL的密码
grep 'A temporary password is generated for' /usr/local/mysql-8/data/mysql.log

# 15、登录mysql
mysql -u root -p 

# 16、修改密码
alter user 'root'@'localhost' identified with mysql_native_password by 'qwerDF123!@#';

# 17、授权远程访问，*.* 表示所有数据库名，第二个*表示所有的数据库表； 
# root@'%'中的root表示用户名，%表示所有ip地址，%也可以指定具体的ip地址，比如：root@192.168.100.102
grant all privileges on *.* to root@'%' identified by 'qwerDF123!@#';

# 18、刷新权限
flush privileges;

# 19、选择mysql数据库，允许使用某个用户名及密码从任何主机连接到该 mysql 服务：
use mysql;
update user set host='%' where user = 'root';
flush privileges;

# 20、退出
exit

# 21、重启mysql
service mysql restart
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

至此，mysql的通用配置已经完成，之后就是主从节点的配置。

2.2、主节点配置

连接192.168.100.101:3306（主）数据库。

# 1、登录mysql
mysql -u root -p

# 2、创建copy用户,并授权，主节点需要做的操作，从节点不需要做
create user 'copy'@'%' identified by 'qwerDF123!@#';
grant replication slave on *.* to 'copy'@'%';

# 或者执行下面的命令（二选一）
grant replication slave on *.* to 'copy'@'%' identified by 'qwerDF123!@#';

# 3、退出mysql
exit

# 4、打开mysql的my.cnf文件
vim /etc/my.cnf
# *****************************************************************
#-------------------------文本内容分割线----------------------------
# 主节点配置
[mysqld]
# 这个id是唯一的，不能和其他从节点相同，否则从节点开启同步时会报错，我这里是以IP为id
server-id=192168100101
# 开启binlog，主节点必须要开启
log_bin=master-bin
log_bin-index=master-bin.index
# 用于在查询过程中跳过DNS反向解析，从而加快查询速度
skip-name-resolve
basedir=/usr/local/mysql-8
datadir=/usr/local/mysql-8/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8
# 忽略大小写配置 1忽略
lower_case_table_names=1
log-error=/usr/local/mysql-8/data/mysql.log
pid-file=/usr/local/mysql-8/data/mysql.pid

# 默认使用mysql_native_password插件认证
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set = utf8
# 退出保存，首先按 【esc】然后 【:wq】保存并退出
#-------------------------文本内容分割线----------------------------
# *****************************************************************

# 5、再次登录主数据库
mysql -u root -p

# 6、授权
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';

# 7、刷新
flush privileges;

# 8、查看主节点同步状态：记录目前的file和position
show master status;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

至此，主节点配置完成。

2.3、从节点配置

# 1、打开mysql的my.cnf文件
vim /etc/my.cnf
# *****************************************************************
#-------------------------文本内容分割线----------------------------
# 从节点配置
[mysqld]
#主库和从库需要不一致，我这里是以IP为id
server-id=192168100102
#打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1
basedir=/usr/local/mysql-8
datadir=/usr/local/mysql-8/data
port=3306
socket=/tmp/mysql.sock
character_set_server=utf8
# 忽略大小写配置 1忽略
lower_case_table_names=1
log-error=/usr/local/mysql-8/data/mysql.log
pid-file=/usr/local/mysql-8/data/mysql.pid

# 默认使用mysql_native_password插件认证
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set = utf8
# 退出保存，首先按 【esc】然后 【:wq】保存并退出
#-------------------------文本内容分割线----------------------------
# *****************************************************************

# 2、登录从数据库
mysql -u root -p

# 3、设置同步主节点配置, 信息需要看主节点的 show master status;
CHANGE MASTER TO
MASTER_HOST='192.168.100.101',
MASTER_PORT=3306,
MASTER_USER='copy',
MASTER_PASSWORD='qwerDF123!@#',
MASTER_LOG_FILE='binlog.000011',
MASTER_LOG_POS=156,
GET_MASTER_PUBLIC_KEY=1;


# 4、开启同步
start slave;

# 5、查看同步状态
SHOW SLAVE STATUS\G;

# 注意：假如主从同步失败，就一定要先执行以下命令停止同步。
stop slave;


# 假如第5步打印的问题描述不清楚，那么就执行查看具体信息
select * from performance_schema.replication_applier_status_by_worker \G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

第3步说明：

• MASTER_HOST：主节点的IP地址；
• MASTER_PORT：主节点的端口；
• MASTER_USER：这里是主节点的用户，我们上面新建了一个copy用户，所以这里是copy，可以填root或者其他；
• MASTER_PASSWORD：这个是用户密码；
• MASTER_LOG_FILE：这个是binlog日志的文件号，主节点中使用show master status命令展示的File值；
• MASTER_LOG_POS：这个是binlog日志偏移量，主节点中使用show master status命令展示的Position值；
• GET_MASTER_PUBLIC_KEY：用于在MySQL中获取主节点的公钥，因为从节点需要主服务器的公钥来验证主服务器发送的加密数据；

第5步说明：
SHOW SLAVE STATUS\G; 会展示从节点的状态，主从是否配置成功，看Slave_IO_Running和Slave_SQL_Running都为Yes，如果有其中一个不是Yes，那么就表示主从配置失败。

假如主从配置失败，那么具体原因可查看Last_IO_Error参数说明，例如：

• 表示主从集群中，存在相同的server_id，那么就需要更改本节点中的my.cnf文件中的server-id参数，然后重新从第三步开始执行。

Last_IO_Errno: 13117
Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it).
1
2

• 其他问题可百度。我这边就不一一列举了。

2.4、主从测试

使用Navicat或者其他工具连接主节点192.168.100.101:3306，然后新建一个数据库，新建一个表，然后连接其他两个从节点可以发现，那边也将新建的数据库和表同步过来了，同样的，在主节点中增、删、改数据，从节点也会同步过来。

需要注意的是：不可用在从节点中新增/删除/修改数据、建表、删除表等数据库变更操作，否则主从就会失效，失效之后从节点就需要重新配置主从关系，先stop slave，然后又是从2.3的第3步开始。

2.5、谈一谈主节点历史数据同步问题

以上配置其实是比较扯淡的，假如你目前项目是已经跑了有一段时间了，那么数据库里面肯定会有历史数据，而前面配置主从的时候，使用show master status;查看的肯定是主节点当前已有历史数据的状态，配置同步后历史数据不会同步过来，这就是一个很大的问题，那么如何解决这个问题呢？

我在网上看了挺多别人给出的解决方案，
首先是flush tables with read lock;将主库的写操作锁了，
然后show master status;记录当前主库的file 和 position值，
之后再使用mysqldump导出主库所有的数据库数据，然后再丢到从库中运行。
再之后就是执行2.3的第3步，将记录的主库file 和 position值填上去后，再开启同步，
从节点开启同步后，没问题的话，再将主库的写操作释放，然后进行测试。

目前我也是用的这个比较笨的办法，这种办法在生产环境的话会有较大问题，比如说你锁住主库的写操作这段期间，系统是不能进行写操作的，相当于从你锁住主库到释放主库这段时间，系统只可查不可写，而如果你生产的数据比较大，这个时间会延长。

如果有大佬有更好的方案，欢迎评论区讨论或留言，咱们一起学习。ヾ(◍°∇°◍)ﾉﾞ