在 Rocky Linux 10.0 环境下搭建 MySQL 8.4.6 主从复制的详细步骤

1.环境说明

操作系统	MySQL版本	角色	IP 地址	Server ID
Rocky Linux 10.0	8.4.6	主库（Master）	192.168.100.5	1
Rocky Linux 10.0	8.4.6	从库（Slave）	192.168.100.6	2

2.修改主机名

2个节点均需要执行

# Master节点执行
hostnamectl set-hostname MySQL-Master

# Slave节点执行
hostnamectl set-hostname MySQL-Slave

3.关闭防火墙

2个节点均需要执行

systemctl stop firewalld
systemctl disable  firewalld
systemctl status  firewalld

3.关闭SELINUX

2个节点均需要执行

setenforce 0
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

4.安装依赖

2个节点均需要执行

dnf install -y wget glibc libaio numactl libaio-devel

5.下载数据库

2个节点均需要执行

wget wget https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.6-linux-glibc2.28-x86_64.tar.xz

6.解压MySQL

2个节点均需要执行

tar -Jxvf mysql-8.4.6-linux-glibc2.28-x86_64.tar.xz -C /usr/local
mv /usr/local/mysql-8.4.6-linux-glibc2.28-x86_64 /usr/local/mysql

7.创建MySQL用户

2个节点均需要执行

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

8.创建MySQL数据目录

2个节点均需要执行

mkdir -p /usr/local/mysql/data

9.创建MySQL日志目录

2个节点均需要执行

mkdir -p /usr/local/mysql/logs

10.创建MySQL临时目录

2个节点均需要执行

mkdir -p /usr/local/mysql/tmp

11.配置环境变量

2个节点均需要执行

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile

12配置MySQL配置文件

Master节点配置文件

cat >> /usr/local/mysql/my.cnf << EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/tmp/mysql.sock
port = 3306
server-id = 1

log-bin = mysql-bin
binlog-format = ROW
binlog_expire_logs_seconds = 2592000

max_connections = 2000
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
log-error = /usr/local/mysql/logs/mysqld.log
pid-file = /usr/local/mysql/tmp/mysqld.pid

EOF

Slave节点配置文件

cat > /usr/local/mysql/my.cnf << EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/tmp/mysql.sock
port = 3306
server-id = 2

relay-log = mysql-relay-bin
log-slave-updates = ON
read-only = ON
max_connections = 2000
relay-log-purge = ON
relay-log-recovery = ON
binlog_expire_logs_seconds = 2592000
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
log-error = /usr/local/mysql/logs/mysqld.log
pid-file = /usr/local/mysql/tmp/mysqld.pid
EOF

13.修改MySQL安装权限

2个节点均需要执行

chown -R mysql:mysql /usr/local/mysql

14.初始化MySQL数据库

2个节点均需要执行

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

15.创建systemd服务文件

2个节点均需要执行

tee /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
EOF

16.启动MySQL服务

2个节点均需要执行

systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

17.链接sock文件

2个节点均需要执行

ln -s /usr/local/mysql/tmp/mysql.sock /tmp/mysql.sock

18.登录MySQL密码

2个节点均需要执行

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL8^2025';

19.创建主从复制账户

仅在 master 节点执行

CREATE USER 'repl'@'192.168.100.%' IDENTIFIED BY 'MySQL2025';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.%';
FLUSH PRIVILEGES;

20.查看master状态

记录 File 和 Position 的值

SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000018 |      158 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

21.从库配置

仅在 slave 节点执行

mysql -uroot -p
# 停止复制进程
STOP REPLICA;
# 配置主服务器的连接信息
CHANGE REPLICATION SOURCE TO 
    SOURCE_HOST='192.168.100.5',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='MySQL2025',
    SOURCE_PORT=3306,
    SOURCE_LOG_FILE='mysql-bin.000018',
    SOURCE_LOG_POS=158,
    SOURCE_SSL=0,
    GET_SOURCE_PUBLIC_KEY=1;

# 启动复制进程
START REPLICA;

22.查看主从复制状态

仅在 slave 节点执行

show replica status\G;

23.验证主从同步

仅在master节点执行

CREATE DATABASE csdb;
use csdb;
create table user(id int primary key, name varchar(50));
INSERT INTO user VALUES(1, 'SUN');
show databases;

仅在 slave 节点执行

show databases;