MySQL 集群之主从部署

2024-10-16 约 3714 字预计阅读 8 分钟

背景信息

为了增加数据的冗余备份，增强服务的高可用性，从传统的单服务应用升级到集群服务应用是一个合适方案，这里介绍一下MySQL 5.7 版本的部署，5.7 版本也是目前大多数企业当前在用的版本，虽然也有很多企业升级到了8.0 版本，不过从安装部署的角度来看其实是大同小异的

环境准备

操作系统版本	cpu	内存	磁盘	IP	部署方式	MySQL版本	主从节点
centos 7.9	2c	4G	40G	192.168.143.101	rpm	5.7.20	Master
centos 7.9	2c	4G	40G	192.168.143.102	rpm	5.7.20	Slave

安装MySQL 5.7

1、下载安装包

1
2
3
4
$ cd /path/to/directory
$ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar
# 删除不需要的安装包
$ ls |grep -vE 'client|libs|common|server-5.7'|xargs rm -f

2、安装依赖

1
2
3
4
5
6
# mysql-community-common-5.7.20-1.el7.x86_64.rpm 依赖
$ sudo yum -y install gcc vim wget net-tools lrzsz libaio
# mysql-community-client-5.7.20-1.el7.x86_64.rpm 依赖
$ sudo yum install  ncurses-compat-libs -y
# mysql-community-server-5.7.20-1.el7.x86_64.rpm 依赖
$ sudo yum install libaio numactl-libs -y

3、服务器时间校正
4、关闭防火墙和安全(主从)

1
2
3
$ setenforce 0
$ systemctl stop firewalld
$ systemctl disable firewalld

4.1 (可选)如果你的仓库中没有 ncurses-compat-libs，你可能需要添加一个额外的仓库，例如 EPEL (Extra Packages for Enterprise Linux)：

1
2
$ sudo yum install epel-release  -y
$ sudo yum install ncurses-compat-libs  -y

4.2 (可选) 检验系统是否包含mariadb-lib 检验系统是否包含mariadb-lib,如果有需要卸载，否则mysql 无法安装

1
2
rpm -qa | grep mariadb
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

5、安装

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$ sudo rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm
$ sudo rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm
$ sudo rpm -ivh mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm
$ sudo rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm
$ sudo rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm

$ rpm -qa|grep mysql
mysql-community-common-5.7.20-1.el7.x86_64
mysql-community-libs-5.7.20-1.el7.x86_64
mysql-community-libs-compat-5.7.20-1.el7.x86_64
mysql-community-client-5.7.20-1.el7.x86_64
mysql-community-server-5.7.20-1.el7.x86_64
# 检查生成的mysql 目录和文件
$ ls  /var/lib/mysql /etc/my.cnf 
/etc/my.cnf

/var/lib/mysql:

6、安装过程摘取

1
2
3
4
5
6
7
# rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-5.7.20-1.e################################# [100%]
/usr/lib/tmpfiles.d/mysql.conf:16: Line references path below legacy directory /var/run/, updating /var/run/mysqld → /run/mysqld; please update the tmpfiles.d/ drop-in file accordingly.

注意

这个警告提示说明该 RPM 包未被 GPG 签名验证。如果你信任这个包的来源，可以忽略这个警告

或者一次性安装会自动进行依赖顺序安装调整：

1
sudo rpm -ivh *.rpm

安装包功能和作用

部署 MySQL 5.7.20 版本的 RPM 包时，需要确保按照正确的顺序安装这些包，因为它们之间存在依赖关系。以下是各个包的功能作用和推荐的安装顺序：

mysql-community-common-5.7.20-1.el7.x86_64.rpm
- 功能：提供所有 MySQL 组件共享的文件。
- 作用：这个包是基础，其他组件依赖它提供的文件。
mysql-community-libs-5.7.20-1.el7.x86_64.rpm
- 功能：包含客户端库文件。
- 作用：为使用 MySQL 客户端连接到 MySQL 数据库服务器提供必要的库文件。
mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm
- 功能：提供兼容性库文件。
- 作用：为旧版本的应用程序提供与旧版 MySQL 库的向后兼容性。
mysql-community-client-5.7.20-1.el7.x86_64.rpm
- 功能：包含 MySQL 客户端命令行工具。
- 作用：允许用户通过命令行界面与 MySQL 数据库交互。
mysql-community-server-5.7.20-1.el7.x86_64.rpm
- 功能：提供 MySQL 数据库服务器。
- 作用：这是核心包，负责处理 SQL 查询并管理数据库。

配置主服务器

MySQL 的主配置文件通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf，具体取决于你的操作系统。对于某些发行版或自定义安装，配置文件可能位于其他位置，如 /etc/my.cnf.d/ 目录下的某个文件中。你可以使用以下命令来查找配置文件的位置：

1
2
3
$ mysql --help | grep "Default options" -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

调整需要将数据放到/data/mysql 目录下，该目录为外挂磁盘，磁盘空间较大，而不是安装在默认的系统磁盘目录下

创建安装目录/data/mysql

1
2
3
mkdir -p /data/mysql/binlog
chown -R mysql:mysql /data/mysql
chomod -R 755 /data/mysql

修改/etc/my.cnf配置文件

 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
[mysqld]
user = mysql
port = 3306
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
lower_case_table_names = 1
group_concat_max_len = 40960
max_allowed_packet = 1G

datadir = /data/mysql
socket = /data/mysql/mysql.sock
log_error = /data/mysql/mysqld.log
pid_file = /data/mysql/mysqld.pid

# 设置server_id,注意要唯一
server_id = 1
# 忽略的数据，指不需要同步的数据库
#binlog-ignore-db=mysql
# 指定同步的数据库
#binlog-do-db=test

###binlog###
binlog_format = row
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log_bin = /data/mysql/binlog/binlog.log
expire_logs_days = 7
master_info_repository = table
relay_log_info_repository = table

###slave###
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
log_bin_trust_function_creators = 1
skip_slave_start = ON
relay_log_recovery = ON

###buffer_pool###
join_buffer_size = 32M
sort_buffer_size = 16M
query_cache_size = 512M
myisam_sort_buffer_size = 128M
thread_cache_size = 128
tmp_table_size = 512M
max_heap_table_size = 128M
read_rnd_buffer_size = 2M

#InnoDB#
default_storage_engine = InnoDB
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1024M
innodb_flush_method = O_DIRECT

##### MyISAM#####
key_buffer_size = 512M

### character_set#####
character_set_server = utf8
collation_server = utf8_general_ci
explicit_defaults_for_timestamp = ON

####name_resolves####
host_cache_size = 0
skip_name_resolve = ON

###connection###
max_connections = 25000
max_connect_errors = 10000

### Enable slow_query_log permanent ###
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /data/mysql/mysql_slow.log

### MASTER_MASTER ###
auto_increment_increment = 1
auto_increment_offset = 1

[mysql]
#prompt = 'mysql[\u@\h]:[\d]:'

[client]
socket = /data/mysql/mysql.sock
port = 3306

启动mysqld服务，并设置开机自启

1
2
$ sudo systemctl enable mysqld --now
$ sudo systemctl status mysqld

配置从服务器

创建安装目录/data/mysql

1
2
3
mkdir -p /data/mysql/binlog
chown -R mysql:mysql /data/mysql
chomod -R 755 /data/mysql

修改/etc/my.cnf配置文件

1
2
3
4
5
6
7
[mysqld]
# 设置server_id,注意要唯一
server_id = 1
...
### MASTER_MASTER ###
auto_increment_increment = 1
auto_increment_offset = 2   # 可根据需要调整，确保从节点与主节点的 AUTO_INCREMENT 值不冲突

启动mysqld服务，并设置开机自启

1
2
$ sudo systemctl enable mysqld --now
$ sudo systemctl status mysqld

修改登录密码

快捷操作

1
2
3
4
MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysqld.log`
MYSQL_ROOT_PASSWORD="your_mysql_password"

mysqladmin -u root -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null

分步操作

1、获取临时密码默认是在/var/log/mysqld.log 文件下，这里修改了安装目录为/data/mysql

1
2
3
sudo grep 'temporary password' /data/mysq/mysqld.log
# grep 'temporary password' /var/log/mysqld.log
2024-11-25T06:34:37.637723Z 1 [Note] A temporary password is generated for root@localhost: hvqki:Gp>0EI

2、临时密码登录

1
mysql -u root -p

3、登录后修改默认密码并进行安全配置：首次登录后，建议立即更改 root 密码并执行 mysql_secure_installation 脚本来提高安全性。登录后修改root密码

 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
ALTER USER 'root'@'%' IDENTIFIED BY 'your_password';


mysql>  ALTER USER 'root'@'%' IDENTIFIED BY 'your_password';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

# 赋远程访问权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, authentication_string, plugin from user;
+-----------+---------------+-------------------------------------------+-----------------------+
| host      | user          | authentication_string                     | plugin                |
+-----------+---------------+-------------------------------------------+-----------------------+
| localhost | root          | *4E02A7566A743B1042608F7ED03213DE937E7D0E | mysql_native_password |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| %         | root          | *4E02A7566A743B1042608F7ED03213DE937E7D0E | mysql_native_password |
+-----------+---------------+-------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

4、mysql_secure_installation MySQL 安全设置(可选)
mysql_secure_installation 是一个用于提高 MySQL 安装安全性的脚本。它可以帮助你执行一系列的安全设置，例如设置 root 密码、移除匿名用户、禁用远程 root 登录、移除测试数据库等。以下是执行 mysql_secure_installation 脚本的详细步骤：

1
2
3
4
5
[root@localhost mysql]<20241225 14:42:33># sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

跟随提示完成配置

运行脚本后，你会被问到一系列问题。下面是每个问题的大致内容和建议的回答方式：

Enter password for user root: 输入你当前的 root 用户密码（即安装时生成的临时密码或你刚刚设置的新密码）。
Switch to unix_socket authentication [Y/n] 这个选项是关于是否切换到 Unix Socket 认证插件，默认情况下选择 N 不启用这个选项，这样可以继续使用基于密码的身份验证。
Change the root password? [Y/n] 如果你已经在登录时修改了 root 密码，可以选择 n；如果还没有修改，可以选择 y 并按照提示设置新密码。
Remove anonymous users? [Y/n] 移除匿名用户可以提高安全性，推荐选择 Y。
Disallow root login remotely? [Y/n] 禁止 root 用户从远程登录通常是个好主意，除非你需要远程管理 MySQL，推荐选择 Y。测试环境也可以选择n
Remove test database and access to it? [Y/n] 移除测试数据库及其访问权限也是提高安全性的措施之一，推荐选择 Y。测试环境也可以选择n
Reload privilege tables now? [Y/n] 重新加载权限表以使更改立即生效，推荐选择 Y。

对话参考

 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
Securing the MySQL server deployment.

Enter password for user root: ********

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
 ... Success!

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
 ... Success!

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
 ... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

注意

安装过程有问题查看服务日志，/data/mysql/mysqld.log

安装后验证

可以尝试通过 MySQL 客户端登录到服务器。
使用 mysqladmin 工具验证服务器是否正在运行：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
$ mysqladmin -u root -p version
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.7.20, for Linux on x86_64
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.7.20
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 14 min 46 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.013

开启主从同步

Master 节点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# 登录到 MySQL 主节点
$ mysql -u root -p

# 创建复制用户（例如：`replica_user`）
CREATE USER 'replica_user'@'192.168.143.102' IDENTIFIED BY 'your_password';

#授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.143.102';

#  刷新权限
FLUSH PRIVILEGES;
#  获取主节点的二进制日志位置
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 154       | your_database_name |                |
+------------------+----------+--------------+------------------+

记下 File二进制文件（例如：mysql-bin.000001）和 Position偏移量（例如：154）的值。

Slave 节点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 登录到 MySQL 备节点
$ mysql -u root -p

# 配置主节点信息
# 使用 `CHANGE MASTER TO` 命令配置从库指向主库。你需要提供主库的 IP 地址、用户名、密码以及上面提到的 `File` 和 `Position` 值：
CHANGE MASTER TO
  MASTER_HOST='192.168.143.101',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='mysql-bin.000001',    -- 主节点的日志文件
  MASTER_LOG_POS=154;                    -- 主节点的日志位置

#启动复制进程 开始从库的 I/O 和 SQL 线程以开始复制过程：
START SLAVE;

# 查看复制状态 检查从库的状态，确认复制是否正常工作：
SHOW SLAVE STATUS\G;

关注以下字段：

Slave_IO_Running: 应该是 Yes
Slave_SQL_Running: 应该是 Yes
Last_Error: 应为空或无错误信息

测试主从同步

在Master主服务器上创建数据库和表，并插入数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> create database test;
mysql> use test;
mysql> create table test(age int);
mysql> insert into test values(1);
mysql> select * from test;
+------+
| age  |
+------+
|    1 |
+------+

在从服务器上检查数据是否同步：

1
2
3
4
5
6
7
mysql> select * from test.test;
+------+
| age  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

以上操作确保了主服务器的数据成功复制到从服务器，实现了主从同步。

卸载 MySQL

卸载或者卸载重新安装

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
systemctl stop mysqld
systemctl status mysqld

rpm -evh rpm包名
# 依次卸载rpm包，按照下面顺序卸载不然会报错
sudo rpm -evh mysql-community-server-5.7.20-1.el7.x86_64
sudo rpm -evh mysql-community-client-5.7.20-1.el7.x86_64
sudo rpm -evh mysql-community-libs-compat-5.7.20-1.el7.x86_64
sudo rpm -evh mysql-community-libs-5.7.20-1.el7.x86_64
sudo rpm -evh mysql-community-common-5.7.20-1.el7.x86_64
# 查询是否还存在遗漏文件
rpm -qa|grep mysql
# 删除 mysql 数据库内容

sudo rm -rf  /data/mysql
sudo rm -rf  /var/lib/mysql
sudo rm -rf /usr/local/mysql
sudo rm -rf /var/run/mysqld
sudo rm -rf /etc/my.cnf
sudo rm -f /var/log/mysqld.log