MySQL数据库：主从库搭建复制配置

MySQL 主从库复制配置

1. 主库配置

编辑文件

sudo vim /etc/mysql/my.cnf

在这个文件行末添加下列信息

[mysqld]
server-id = 1
log-bin = mysql-bin
bind-address = 0.0.0.0  # 允许远程连接

重启主库

sudo systemctl restart mysql

2. 创建复制账号

在主库执行：

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

-- 查看用户列表及认证方式
SELECT user, host, plugin FROM mysql.user WHERE user='repl' AND host='%';

-- 下面的表格不是命令，展示使用
-- 预期输出（使用caching_sha2_password时）：
+------+------+-----------------------+
| user | host | plugin                |
+------+------+-----------------------+
| repl | %    | caching_sha2_password |
+------+------+-----------------------+

3. 从库配置

编辑文件

sudo vim /etc/mysql/my.cnf.slave

在这个文件行末添加下列信息

[mysqld]
server-id = 2
port = 3307
datadir = /var/lib/mysql_slave
socket = /var/run/mysqld/mysqld_slave.sock
log-bin = mysql-bin  # 可选（如需级联复制）
read_only = ON

4. 初始化从库数据目录

sudo mkdir -p /var/lib/mysql_slave
sudo chown -R mysql:mysql /var/lib/mysql_slave
sudo chmod 750 /var/lib/mysql_slave

sudo mysqld --defaults-file=/etc/mysql/my.cnf.slave --initialize-insecure --user=mysql

• --initialize-insecure：快速初始化（无密码，需后续设置）。
• 其他做法可改用 --initialize 生成随机密码（记录在日志中）。

5. 启动从库（正确方式）

sudo -u mysql mysqld --defaults-file=/etc/mysql/my.cnf.slave --daemonize

验证从库启动允许：

ps aux | grep mysqld | grep 3307

6. 配置主从复制

在从库执行：

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',  # 替换为主库实际IP
MASTER_USER='repl',
MASTER_PASSWORD='12345678',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=157;

START SLAVE;

7. 配置主从复制

改用传统认证插件（推荐）

改用传统认证的原因：两个主从库位于本地同一主机或同一受信任的局域网内，互相通过本地同一主机或同一受信任的局域网通信，避免因为使用caching_sha2_password导致因为SSL配置问题导致从库无法访问主库，且SSL配置比较麻烦，本地同一主机或同一受信任的局域网内可以这么使用，限制访问来源为本地同一主机或同一受信任的局域网就行。

且如果在生产环境中，两个主从库互相通过公网通信，强烈推荐使用 caching_sha2_password（MySQL 8.0+ 默认认证插件），并强制启用 SSL/TLS 加密。

• caching_sha2_password 默认需要 SSL 加密连接，若未正确配置 SSL，复制可能中断。
• mysql_native_password 无需强制 SSL，简化配置。

在主库执行：

-- 查看用户列表及认证方式
SELECT user, host, plugin FROM mysql.user WHERE user='repl' AND host='%';

-- 修改认证方式（不记录到binlog）
SET sql_log_bin = 0;
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';
SET sql_log_bin = 1;
FLUSH PRIVILEGES;

-- 再次查看用户列表及认证方式
SELECT user, host, plugin FROM mysql.user WHERE user='repl' AND host='%';

-- 下面的表格不是命令，展示使用
-- 预期输出（使用mysql_native_password时）：
+------+------+-----------------------+
| user | host | plugin                |
+------+------+-----------------------+
| repl | %    | mysql_native_password |
+------+------+-----------------------+

在从库执行：

-- 先停止同步避免冲突
STOP SLAVE;

-- 重新配置从库连接
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='12345678';

START SLAVE;

8. 验证复制状态

在从库执行：

SHOW SLAVE STATUS\G

关键指标：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0
• 示例数据：

• 

  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for source to send event
                    Master_Host: 127.0.0.1
                    Master_User: repl
                    Master_Port: 3306
                  Connect_Retry: 10
                Master_Log_File: mysql-bin.000002
            Read_Master_Log_Pos: 157
                 Relay_Log_File: debian-3-relay-bin.000003
                  Relay_Log_Pos: 373
          Relay_Master_Log_File: mysql-bin.000002
               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: 157
                Relay_Log_Space: 755
                Until_Condition: None
                 Until_Log_File: 
                  Until_Log_Pos: 0
             Master_SSL_Allowed: No
             Master_SSL_CA_File: 
             Master_SSL_CA_Path: 
                Master_SSL_Cert: 
              Master_SSL_Cipher: 
                 Master_SSL_Key: 
          Seconds_Behind_Master: 0
  Master_SSL_Verify_Server_Cert: No
                  Last_IO_Errno: 0
                  Last_IO_Error: 
                 Last_SQL_Errno: 0
                 Last_SQL_Error: 
    Replicate_Ignore_Server_Ids: 
               Master_Server_Id: 1
                    Master_UUID: d55a5231-58b5-11f0-a8a8-000c2998b642
               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: 
              Executed_Gtid_Set: 
                  Auto_Position: 0
           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.00 sec)
  

设置root用户密码

在MySQL命令行中执行SQL语句为root用户设置密码

在从库执行：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';
FLUSH PRIVILEGES;

• 替换新密码为你想要设置的密码，这边使用12345678。
• 使用 mysql_native_password 插件以确保兼容性（与主从复制配置一致）。

9.杀掉MySQL从库的进程

杀掉从库进程是为下一步的系统服务文件做准备

ps aux | grep mysqld

命令输出

mysql       4740  0.6  6.1 2677544 494196 ?      Sl   15:55   0:10 mysqld --defaults-file=/etc/mysql/my.cnf.slave --daemonize
mysql       5047  0.6  5.0 2479060 408268 ?      Ssl  16:14   0:04 /usr/sbin/mysqld
root        5226  0.0  0.0   6564  2288 pts/1    S+   16:25   0:00 grep mysqld

看到mysqld --defaults-file=/etc/mysql/my.cnf.slave --daemonize，找到从库的PID然后杀掉，例如这边PID是4740

# 先获取从库的PID（第二个mysqld进程）
sudo kill -TERM 4740  # 使用您查到的从库PID

# 等待5秒后检查是否结束
ps aux | grep mysqld

10. 创建Systemd服务（持久化）

创建并编辑服务文件

vim /etc/systemd/system/mysql-slave.service

编辑服务文件

[Unit]
Description=MySQL Slave Server
After=network.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf.slave
Restart=always
RestartSec=3

[Install]
WantedBy=multi-user.target
sudo systemctl daemon-reload
sudo systemctl start mysql-slave
sudo systemctl enable mysql-slave

#查看服务状态
systemctl status mysql-slave.service

#启动服务
systemctl start mysql-slave.service

#设置开机启动
systemctl enable mysql-slave.service

#再次查看服务状态
systemctl status mysql-slave.service

登录主库的命令

主库通常使用默认端口（3306），配置文件为 /etc/mysql/my.cnf。登录命令如下：

mysql -u root -p -h 127.0.0.1 -P 3306

• -u root: 使用root用户（或其他有权限的用户）。
• -p: 提示输入密码。
• -h 127.0.0.1: 指定主库IP（根据实际环境替换）。
• -P 3306: 主库默认端口。

登录从库的命令

从库使用自定义端口（3307），配置文件为 /etc/mysql/my.cnf.slave，数据目录为 /var/lib/mysql_slave，登录命令如下：

mysql -u root -p -h 127.0.0.1 -P 3307 --socket=/var/run/mysqld/mysqld_slave.sock

• -u root: 使用root用户（或其他有权限的用户）。
• -p: 提示输入密码。
• -h 127.0.0.1: 指定从库IP（根据实际环境替换）。
• -P 3307: 从库端口。
• --socket: 指定从库的socket文件路径。

注意事项

1. 密码：如果初始化时使用了 --initialize-insecure，root用户可能无密码，可直接回车；若使用 --initialize，需查看日志获取随机密码。
2. IP地址：根据实际部署环境，替换 127.0.0.1 为主库或从库的实际IP。
3. 权限：确保登录用户有足够权限（如root或repl用户）。
4. 服务状态：登录前确认主库和从库服务是否运行：
   • 主库：sudo systemctl status mysql
   • 从库：sudo systemctl status mysql-slave

验证登录

登录后，可通过以下命令确认当前连接的数据库实例：

SELECT @@server_id, @@port;

• 主库预期输出：server_id=1, port=3306
• 从库预期输出：server_id=2, port=3307

关键问题修正

1. 权限问题：

   • 必须使用 sudo -u mysql 运行mysqld
   • 数据目录需严格设置权限：chown -R mysql:mysql + chmod 750

2. 配置文件差异：

   • 主库和从库的 server-id 必须不同
   • 从库需指定不同的 port 和 datadir

3. 连接方式：

   • 从库连接主库时需使用主库的 实际IP（非localhost）
   • 若在同一机器，可用 127.0.0.1 但需确保主库 bind-address 允许

4. 错误排查：

   # 查看从库错误日志
   sudo tail -f /var/lib/mysql_slave/debian-3.err
   

常见报错

问题1

执行SHOW SLAVE STATUS\G时，常见Last_Error报错：

表明 MySQL 主从复制出现了问题，工作线程（worker）执行事务时失败。

                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 fai
led executing transaction 'ANONYMOUS' at source log mysql-bin.000002, end_log_pos 1153. See error log and/or performance_schema.replication_
applier_status_by_worker table for more details about this failure or others, if any.

解决方案

在从库执行

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;  -- 跳过 1 个事务
START SLAVE;

命令的作用

• STOP SLAVE; 停止从库的复制线程（IO 线程和 SQL 线程）。
• SET GLOBAL sql_slave_skip_counter = 1; 让从库跳过 ​​1 个事务​​（即跳过当前报错的事务）。 ⚠️ 注意：此操作会导致从库与主库数据不一致，需后续手动修复！
• START SLAVE; 重新启动复制线程。

再次验证

若报错那行空白说明已经恢复正常

SHOW SLAVE STATUS\G

问题2

执行SHOW SLAVE STATUS\G时，常见Last_Error报错：

表明从库（Slave）无法连接到主库（Master），原因是 caching_sha2_password 认证插件要求安全连接（SSL），但当前连接未启用 SSL。

                Last_IO_Error: Error connecting to source 'repl@127.0.0.1:3306'. This was attempt 676/86400, with a delay of 10 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决方案

改用传统认证插件

这边以修改repl用户为例子

在主库执行：

-- 查看用户列表及认证方式
SELECT user, host, plugin FROM mysql.user WHERE user='repl' AND host='%';

-- 修改认证方式（不记录到binlog）
SET sql_log_bin = 0;
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';
SET sql_log_bin = 1;
FLUSH PRIVILEGES;

-- 再次查看用户列表及认证方式
SELECT user, host, plugin FROM mysql.user WHERE user='repl' AND host='%';

-- 下面的表格不是命令，展示使用
-- 预期输出（使用mysql_native_password时）：
+------+------+-----------------------+
| user | host | plugin                |
+------+------+-----------------------+
| repl | %    | mysql_native_password |
+------+------+-----------------------+

在从库执行：

-- 先停止同步避免冲突
STOP SLAVE;

-- 重新配置从库连接
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='12345678';

START SLAVE;

再次验证

若报错那行空白说明已经恢复正常

SHOW SLAVE STATUS\G