Ubuntu MySQL 8.0+ 主从复制配置指南

一、主从复制核心原理

1. 日志传递机制

主库（Master） 通过二进制日志（binlog）记录所有数据变更（DDL/DML）。
从库（Slave） 通过 I/O 线程读取主库 binlog 并写入本地中继日志（relay log）。

2. 数据重放过程

从库的 SQL 线程解析 relay log 并执行相同操作，实现数据同步。
默认采用 异步复制，主库不等待从库确认。

3. GTID 模式（推荐）

MySQL 8.0+ 默认支持全局事务标识符（GTID），通过唯一事务 ID 简化主从位点管理。
避免传统文件名+偏移量模式的数据不一致问题。

二、部署环境

mysql01：192.168.10.27 主库 ubuntu 24.04+mysql 8.0.41

mysql02：192.168.10.28 从库 ubuntu 24.04+mysql 8.0.41

# 主库数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| stocks             |
| sys                |
| zabbix             |
+--------------------+

三、主库（Master）配置

1、准备SSL主从复制所需证书

# 创建证书存储路径
mkdir /etc/mysql/repl_ssl

# 生成 CA 私钥和证书  
sudo openssl genrsa -out ca.key 4096
sudo openssl req -x509 -new -nodes -key ca.key -days 3650 -out ca.pem \
-subj "/CN=mysql01" -addext "subjectAltName = IP:192.168.10.27"

# 生成客户端密钥和证书
sudo openssl genrsa -out client-key.pem 4096 
sudo openssl req -new -key client-key.pem  -out client.csr \
-subj "/CN=192.168.10.27"  \
-addext "subjectAltName = IP:192.168.10.27" 
sudo openssl x509 -req -in client.csr -CA ca.pem -CAkey ca.key \
-CAcreateserial -out client-cert.pem -days 365 -copy_extensions copy 

# 设置权限
sudo chown -R mysql:mysql /etc/mysql/repl_ssl
sudo chmod -R 640 /etc/mysql/repl_ssl

2. 修改配置文件

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

主从复制基本配置

[mysqld]
# 唯一 ID（主从不可重复）
server-id = 1  
# 启用 binlog                   
log_bin = /var/log/mysql/mysql-bin.log  
# 行级日志格式（推荐）
binlog_format = ROW  
# 启用 GTID             
gtid_mode = ON   
# 强制 GTID 一致性                 
enforce_gtid_consistency = ON

SSL证书基本配置

ssl-ca = /etc/mysql/repl_ssl/ca.pem
ssl-cert = /etc/mysql/repl_ssl/client-cert.pem
ssl-key = /etc/mysql/repl_ssl/client-key.pem
ssl_cipher = HIGH:!aNULL:!eNULL:!MD5
require_secure_transport = ON

保存后重启服务：

sudo systemctl restart mysql

3. 创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'YourPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

三、从库（Slave）配置

1、主库全量备份

mysqldump -u root -p \
--all-databases \
--single-transaction  \
--source-data=2 \
> /home/horace/full_backup.sql

2、从库导入备份数据

scp /home/horace/full_backup.sql horace@192.168.10.28://home/horace/

mysql -u admin -p < /home/horace/full_backup.sql

3、复制SSL证书

# 登录主库服务器
sudo rsync -av /etc/mysql/repl_ssl horace@192.168.10.28:/home/horace

# 登录从库服务器
sudo mv /home/horace/repl_ssl /etc/mysql/
sudo chown -R mysql:mysql /etc/mysql/repl_ssl
sudo chmod -R 640 /etc/mysql/repl_ssl

4、修改主配置文件

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

主从复制基本配置

[mysqld]
# 唯一 ID
server-id = 2                     
# 中继日志路径
relay_log = /var/log/mysql/mysql-relay.log
# 从库只读模式（可选）  
read_only = ON                    
# 启用 GTID             
gtid_mode = ON   
# 强制 GTID 一致性                 
enforce_gtid_consistency = ON

SSL证书基本配置

ssl-ca = /etc/mysql/repl_ssl/ca.pem
ssl-cert = /etc/mysql/repl_ssl/client-cert.pem
ssl-key = /etc/mysql/repl_ssl/client-key.pem
ssl_cipher = HIGH:!aNULL:!eNULL:!MD5
require_secure_transport = ON

5、保存后重启服务：

sudo systemctl restart mysql

6、配置主库连接

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.10.27',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'YourPassword123!',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1,  
  SOURCE_SSL_CA = '/etc/mysql/repl_ssl/ca.pem',  
  SOURCE_SSL_CERT = '/etc/mysql/repl_ssl/client-cert.pem',  
  SOURCE_SSL_KEY = '/etc/mysql/repl_ssl/client-key.pem',  
  SOURCE_SSL_VERIFY_SERVER_CERT = 1; 
START SLAVE;

四、启动复制与验证

1. 检查从库状态

SHOW SLAVE STATUS\G
# 预期输出
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.10.27
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 129395319
               Relay_Log_File: mysql-relay.000005
                Relay_Log_Pos: 24537726
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 129394938
              Relay_Log_Space: 129395903
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/repl_ssl/ca.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/mysql/repl_ssl/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql/repl_ssl/client-key.pem
        Seconds_Behind_Master: 1
Master_SSL_Verify_Server_Cert: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: e3719e44-d998-11ef-a3ef-525400e02c13
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: e3719e44-d998-11ef-a3ef-525400e02c13:71484-351174
            Executed_Gtid_Set: e3719e44-d998-11ef-a3ef-525400e02c13:1-351174
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.11 sec)

关键字段验证：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Error 和 Last_SQL_Error 为空。

2. 测试数据同步

在主库创建测试表或插入数据，观察从库是否同步。

# 登录主库
mysql> use stocks;
mysql> create table test (name varchar(10),age int);
mysql> insert test values('leo chen',25);
mysql> select * from test;
+----------+------+
| name     | age  |
+----------+------+
| leo chen |   25 |
+----------+------+

# 登录从库
mysql> select * from test;
+----------+------+
| name     | age  |
+----------+------+
| leo chen |   25 |
+----------+------+

五、常见问题处理

1. 同步复制证书验证失败

报错信息： Message: SSL connection error: error:0A000086:SSL routines::certificate verify failed, Error_code: MY-002026
原因分析：SSL连接证书验证失败。
修复建议：

（1）重建完整证书链‌
在主库和从库的 CA 文件中包含完整的证书链（根 CA + 中间 CA）‌。

‌（2）调整证书主体信息‌
重新生成证书，确保 CN 或 SAN 包含主库的 IP/域名‌，如：
```
-subj "/CN=mysql01" -addext "subjectAltName = IP:192.168.10.27"
```
（3‌）同步主从 SSL 配置‌
主库配置（ssl_ca、ssl_cert、ssl_key）与从库配置（SOURCE_SSL_CA、SOURCE_SSL_CERT 等）需完全一致‌57。

‌（4）临时验证方法‌
在从库配置中增加 SOURCE_SSL_VERIFY_SERVER_CERT=0 跳过证书验证（仅用于测试，正式环境不推荐）‌。

2. binlog缺失无法同步

错误信息： from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID set sent by the replica is '', and the missing transactions are 'e3719e44-d998-11ef-a3ef-525400e02c13:1-11509'', Error_code: MY-013114
原因分析： 主库主配置文件max_binlog_siz默认值为100M，binlog文件被自动清理导致从库同步时缺失
修复建议： max_binlog_siz设置为1G，重新全量备份主库和重新导入从库

六、高级配置选项

复制模式	配置方法
半同步复制	主库配置 `rpl_semi_sync_master_enabled=1`，从库配置 `rpl_semi_sync_slave_enabled=1`
过滤复制	主库配置 `binlog_do_db=db_name` 或从库配置 `replicate_do_db=db_name`

posted @ 2025-04-04 16:45 HoraceXie 阅读(83) 评论(0) 收藏举报

刷新页面返回顶部