cyxCoder

MySQL 主从架构（Master-Slave Replication）是一种常见的数据库复制模式，用于提高系统的可用性、读写分离和数据冗余。在主从架构中，一个或多个从服务器（Slave）会从主服务器（Master）同步数据，从而实现数据的高可用性和负载均衡。

• 主服务器（Master）：负责处理所有的写操作（如插入、更新、删除）。所有对数据库的更改都会记录到二进制日志（Binary Log）中。
• 从服务器（Slave）：负责从主服务器获取并应用二进制日志中的更改，保持与主服务器的数据一致。从服务器可以处理读操作，分担主服务器的查询压力。

如下我们基于Ubuntu 22.04，一步步手动安装MySQL并配置主从架构。

一、部署主库

1.1、安装MySQL

apt install mysql-server

1.2、修改主库配置文件

vim /etc/mysql/my.cnf

添加如下配置项：

[mysqld] 
server-id=1 # 主数据库的唯一标识符
log-bin=mysql-bin # 启用二进制日志，用于记录主数据库的所有更新操作
binlog-do-db=contact # 指定复制的数据库名称，可指定多个数据库，每行一个
binlog-do-db=jpress
gtid_mode = on
enforce_gtid_consistency = on

重启数据库以使配置生效

systemctl restart mysql.service

1.3、让MySQL可以远程外部连接

修改root密码，并授权任何主机可以通过root连接(测试环境使用)：

create user root@'%' identified by '你的密码';
grant all privileges on *.* to root@'%' WITH GRANT OPTION ;
FLUSH PRIVILEGES;

1.4、修改MySQL配置文件允许MySQL进程任意IP访问

可以输入：netstat -ntlp查看mysql版的的IP，如果如下图所示为127.0.0.1:3306,则表示MySQL绑定了本地IP只允许本机访问。

修改配置文件：

vim /etc/mysql/mysql.conf.d/mysqld.cnf

找到如下配置注释掉，bind-address为经典协议绑定地址对应3306端口，mysqlx-bind-address对应MySQL X协议对应33060端口：

bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1

重启MySQL：systemctl restart mysql.service  

再查看网络端口：netstat -ntlp，Local Address变为:::

MySQL X协议内容可参考：https://www.modb.pro/db/1770332797349203968

1.5、创建一个MySQL复制账号供从节点使用

在主数据库上创建一个用于复制的MySQL账户，并授权其从任何主机连接到主数据库（生产环境建议限制具体IP）

mysql -uroot -p                                                     #登录mysql
CREATE USER 'cyx'@'%' IDENTIFIED BY '123456';                   #创建用户并设置密码
GRANT REPLICATION SLAVE ON *.* TO 'cyx'@'%' WITH GRANT OPTION;    #对用户授予权限 
FLUSH PRIVILEGES;

1.6、账号加密规则确认和配置

mysql8之前,加密规则是mysql_native_password，不需要加密连接，验证速度快但不够安全。

mysql8之后,加密规则是caching_sha2_password，建立安全加密连接，并改良了性能问题速度也快。

可以通过，mysql -uroot -p登录MySQL，然后输入如下SQL进行查询：

use mysql; 
SELECT Host, User, plugin from user; 

如果复制账号为caching_sha2_password，则还需要进行SSL配置，否则从库会连接状态一直是：Slave_IO_Running: Connecting。

方式一：把复制账号改为mysql_native_password（不安全，生产环境不推荐）

ALTER USER 'cyx'@'%' IDENTIFIED WITH mysql_native_password BY 'cyx5595113';
FLUSH PRIVILEGES;

方式二：MySQL自动生成证书，建立安全连接

参考MySQL官方文档：https://dev.mysql.com/doc/refman/8.4/en/creating-ssl-rsa-files-using-mysql.html

Mysql8的auto_generate_certs配置默认为on，服务启动后会在datadir目录下（默认：/var/lib/mysql）自动生成证书文件。即使删除了证书，重启之后依然会生成。

1、进入MySQL命令行，输入SHOW VARIABLES LIKE 'auto_generate_certs'; 命令，确认auto_generate_certs配置为ON。
 

2、进入/var/lib/mysql，输入命令：ls *.pem确认证书存在

3、在/etc/mysql/my.cnf中[mysqld]标签下添加如下配置：

ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
gtid_mode = on
enforce_gtid_consistency = on

4、重启服务：systemctl restart mysql.service

方式三：通过openssl生成证书，建立安全连接

在/etc/mysql目录下新建ssl文件夹，进入ssl文件夹中执行如下命令:

// 生成私钥
openssl genrsa 2048 > ca-key.pem

// 生成CA证书
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem -subj "/CN=China CA"

// 生成服务器证书请求文件
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem -subj "/C=CN/ST=guangdong/L=dongguan/O=cyx/OU=IT/CN=www.cyx1.site"

// 自签名服务器证书
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

// 生成客户端证书请求文件
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem --subj "/C=CN/ST=guangdong/L=dongguan/O=cyx/OU=IT/CN=www.cyx1.site"

// 自签名客户端证书
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

// 用此命令验证证书有效性
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

在/etc/mysql/my.cnf中[mysqld]标签下添加如下配置： 

ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
gtid_mode = on
enforce_gtid_consistency = on

二、部署从库

2.1、安装MySQL

apt install mysql-server

2.2、主库生成的SSL证书拷贝到从库

把主库生成的SSL证书拷贝到从库的/etc/mysql/ssl目录下，ssl目录没有可以新建

2.3、修改主库配置文件

命令：vim /etc/mysql/my.cnf

添加如下配置项：

[mysqld]
server-id=2 # 从数据库的唯一标识符，与主数据库的server-id不同
relay-log=mysql-relay-bin # 启用中继日志，用于在从数据库上复制主数据库的操作
read-only=1 # 设置从数据库为只读，防止在从数据库上直接写入数据
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cent.pem
ssl-key=/etc/mysql/ssl/server-key.pem
gtid_mode = on
enforce_gtid_consistency = on

重启数据库以使配置生效：

systemctl restart mysql.service

2.4、登录从库，配置主数据库连接相关数据

登录从库：mysql -uroot -p

输入如下命令，配置主库信息（如果不是首次配置，要先停止从库进程：STOP SLAVE;）：

CHANGE MASTER TO SOURCE_HOST='cyx1.site',  #指向主库             
SOURCE_USER='cyx',                           #用于复制的MySQL账户
SOURCE_PASSWORD='123456',                #密码
SOURCE_AUTO_POSITION = 1,
GET_SOURCE_PUBLIC_KEY=1,
SOURCE_SSL=1,
SOURCE_SSL_CA='/etc/mysql/ssl/ca.pem',
SOURCE_SSL_CERT='/etc/mysql/ssl/client-cert.pem',
SOURCE_SSL_KEY='/etc/mysql/ssl/client-key.pem';

继续输入下面命令，启动从库：

START SLAVE;                #启动从库进程
SHOW SLAVE STATUS\G;      #显示进程状态

如下图所示，如果都显示Yes则表示连接成功。

三、参考资料

https://www.cnblogs.com/larks-islands/p/18212752
https://blog.csdn.net/weixin_42966151/article/details/127777374
https://cloud.tencent.com/developer/article/2307952
mysql_ssl_rsa_setup使用详解：https://blog.csdn.net/jkzyx123/article/details/139678353
MySQL 8.4.0 LTS 变更解析: https://www.modb.pro/db/1790677863824969728
MySQL官方文档，通过openssl创建证书：https://dev.mysql.com/doc/refman/8.4/en/creating-ssl-files-using-openssl.html
MySQL官方文档，自动创建SSL证书：https://dev.mysql.com/doc/refman/8.4/en/creating-ssl-rsa-files-using-mysql.html