MySQL主从数据库搭建步骤

1. 环境准备

主库

IP：192.168.146.157
MySQL：8.0.x
server_id：1

从库

IP：192.168.147.150
MySQL：8.0.x
server_id：2

确认两台服务器网络互通。

从库执行：

ping -c 4 192.168.146.157

测试 MySQL 端口：

nc -zv 192.168.146.157 3306

2. 检查主库 Binlog

在主库执行：

SHOW VARIABLES WHERE Variable_name IN (
    'log_bin',
    'server_id',
    'binlog_format',
    'gtid_mode',
    'binlog_expire_logs_seconds'
);

确认：

log_bin = ON
server_id = 1
binlog_format = ROW

3. 创建主库复制账号

在主库执行：

CREATE USER 'rtms_repl'@'192.168.147.150'
IDENTIFIED BY '复制账号密码';

授予复制权限：

GRANT REPLICATION SLAVE, REPLICATION CLIENT
ON *.*
TO 'rtms_repl'@'192.168.147.150';

检查权限：

SHOW GRANTS FOR 'rtms_repl'@'192.168.147.150';

4. 测试复制账号

在从库执行：

mysql \
    -h 192.168.146.157 \
    -u rtms_repl \
    -p \
    -e "SELECT @@hostname, @@server_id, @@version;"

能够正常返回主库信息即可。

5. 创建主库初始化备份

使用 mysqldump 创建一致性备份，并记录对应的 Binlog 文件和 Position。

主库执行：

sudo bash -c 'mysqldump \
    --defaults-extra-file=/opt/scripts/backup.cnf \
    --single-transaction \
    --source-data=2 \
    --routines \
    --triggers \
    --events \
    rtms > /opt/backup/mysql/replica_init_$(date +%Y%m%d_%H%M%S).sql'

其中：

--single-transaction

用于 InnoDB 在线一致性备份。

--source-data=2

将备份对应的 Binlog 文件和 Position 写入 SQL 文件。

6. 获取初始化 Binlog Position

执行：

grep -m 1 "CHANGE MASTER" \
/opt/backup/mysql/replica_init_*.sql

例如：

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001389',
-- MASTER_LOG_POS=103820985;

记录：

SOURCE_LOG_FILE = binlog.001389
SOURCE_LOG_POS  = 103820985

后续配置从库时使用这个位置。

7. 将初始化备份传输到从库

使用 rsync：

sudo rsync -avh --progress \
    qms@192.168.146.157:/opt/backup/mysql/replica_init_20260924_135404.sql \
    /opt/backup/mysql/

8. 校验备份文件

主库执行：

sha256sum /opt/backup/mysql/replica_init_20260924_135404.sql

从库执行：

sha256sum /opt/backup/mysql/replica_init_20260924_135404.sql

确认两边 SHA256 一致。

9. 在从库导入初始化数据

如果从库已经存在旧的测试数据，先根据实际情况清理。

导入数据库：

sudo mysql rtms < /opt/backup/mysql/replica_init_20260924_135404.sql

导入完成后检查表数量：

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = 'rtms';

检查数据库大小：

SELECT
    ROUND(
        SUM(data_length + index_length) / 1024 / 1024 / 1024,
        2
    ) AS size_gb
FROM information_schema.tables
WHERE table_schema = 'rtms';

10. 配置从库 server_id

编辑：

sudo vim /etc/mysql/mysql.conf.d/replica.cnf

添加：

[mysqld]
server-id = 2

relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index

read_only = ON
super_read_only = ON

11. 检查 MySQL 配置

执行：

sudo mysqld --validate-config

没有报错后重启 MySQL：

sudo systemctl restart mysql

检查 MySQL 状态：

sudo systemctl status mysql --no-pager

12. 验证从库配置

执行：

SHOW VARIABLES WHERE Variable_name IN (
    'server_id',
    'read_only',
    'super_read_only'
);

确认：

server_id = 2
read_only = ON
super_read_only = ON

13. 配置主从复制

在从库执行：

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.146.157',
    SOURCE_PORT=3306,
    SOURCE_USER='rtms_repl',
    SOURCE_PASSWORD='复制账号密码',
    SOURCE_LOG_FILE='binlog.001389',
    SOURCE_LOG_POS=103820985,
    GET_SOURCE_PUBLIC_KEY=1;

其中：

SOURCE_HOST

为主库 IP。

SOURCE_USER

为复制账号。

SOURCE_LOG_FILE
SOURCE_LOG_POS

为初始化备份对应的 Binlog 文件和 Position。

14. 启动主从复制

执行：

START REPLICA;

15. 检查主从复制状态

执行：

SHOW REPLICA STATUS\G

重点检查：

Replica_IO_Running
Replica_SQL_Running
Last_SQL_Errno
Last_SQL_Error

正常情况下：

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Last_SQL_Errno: 0
Last_SQL_Error:

16. 检查复制进度

执行：

SHOW REPLICA STATUS\G

重点关注：

Source_Log_File
Read_Source_Log_Pos
Relay_Source_Log_File
Exec_Source_Log_Pos
Seconds_Behind_Source
Relay_Log_Space

其中：

Source_Log_File
Read_Source_Log_Pos

表示从库 IO 线程从主库读取到的位置。

Relay_Source_Log_File
Exec_Source_Log_Pos

表示从库 SQL 线程实际执行到的位置。

当：

Relay_Source_Log_File

逐渐接近：

Source_Log_File

并且：

Exec_Source_Log_Pos

不断向前推进时，说明从库正在正常追赶。

17. 查看复制线程状态

可以使用：

sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E \
'Source_Log_File|Read_Source_Log_Pos|Relay_Source_Log_File|Exec_Source_Log_Pos|Seconds_Behind_Source|Replica_IO_Running|Replica_SQL_Running'

持续观察复制进度。

18. 检查并行复制配置

查看当前并行复制配置：

SHOW VARIABLES WHERE Variable_name IN (
    'replica_parallel_workers',
    'replica_parallel_type',
    'binlog_transaction_dependency_tracking'
);

例如：

replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 4

19. 最终主从架构

搭建完成后：

                    RTMS
                     │
                     ▼
              ┌──────────────┐
              │    主库      │
              │192.168.146.157│
              └──────┬───────┘
                     │
                  Binlog
                     │
                     ▼
              ┌──────────────┐
              │    从库      │
              │192.168.147.150│
              └──────────────┘

主库负责正常业务读写。

从库持续接收并执行主库 Binlog，作为备用数据库。

注意：MySQL 主从复制本身不会自动完成故障切换。自动故障转移需要额外的高可用方案。

posted @ 2026-09-28 11:08  wellplayed  阅读(3)  评论(0)    收藏  举报