WangKai's Book

Appearance

MySQL 8.0 主从同步 & 双主配置 ​

环境说明 ​

角色IP
Master（主）192.168.0.1
Slave（从）192.168.0.2

双主模式下两台互为主从，192.168.0.1 和 192.168.0.2 均需配置。

一、主从同步 ​

1. 主库配置（192.168.0.1） ​

编辑 /etc/my.cnf：

ini
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_do_db = your_database
expire_logs_days = 7

重启 MySQL：

bash
systemctl restart mysqld

创建复制账号（使用 mysql_native_password）：

sql
CREATE USER 'repl'@'192.168.0.2'
  IDENTIFIED WITH mysql_native_password BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2';
FLUSH PRIVILEGES;

查看主库 binlog 状态：

sql
SHOW MASTER STATUS;

记录 File 和 Position 的值备用。

2. 从库配置（192.168.0.2） ​

编辑 /etc/my.cnf：

ini
[mysqld]
server-id = 2
relay_log = relay-bin
read_only = 1

重启 MySQL：

bash
systemctl restart mysqld

配置同步并启动：

sql
CHANGE MASTER TO
  MASTER_HOST = '192.168.0.1',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'Repl@123456',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 157,
  GET_MASTER_PUBLIC_KEY = 1;
START SLAVE;

验证同步状态：

sql
SHOW SLAVE STATUS\G

正常值：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

主库执行脚本（master_setup.sh） ​

bash
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="your_root_password"

mysql -u$MYSQL_USER -p$MYSQL_PASS <<EOF
CREATE USER 'repl'@'192.168.0.2'
  IDENTIFIED WITH mysql_native_password BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
EOF

从库执行脚本（slave_setup.sh） ​

bash
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="your_root_password"
MASTER_LOG_FILE="mysql-bin.000001"
MASTER_LOG_POS=157

mysql -u$MYSQL_USER -p$MYSQL_PASS <<EOF
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST = '192.168.0.1',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'Repl@123456',
  MASTER_LOG_FILE = '$MASTER_LOG_FILE',
  MASTER_LOG_POS = $MASTER_LOG_POS,
  GET_MASTER_PUBLIC_KEY = 1;
START SLAVE;
SHOW SLAVE STATUS\G
EOF

二、双主同步 ​

1. 两台都开启 binlog，配置自增错开 ​

192.168.0.1 的 /etc/my.cnf：

ini
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
auto_increment_increment = 2
auto_increment_offset = 1

192.168.0.2 的 /etc/my.cnf：

ini
[mysqld]
server-id = 2
log_bin = mysql-bin
binlog_format = ROW
relay_log = relay-bin
read_only = 0
auto_increment_increment = 2
auto_increment_offset = 2

重启两台 MySQL：

bash
systemctl restart mysqld

2. 在 192.168.0.2 创建复制账号 ​

sql
CREATE USER 'repl'@'192.168.0.1'
  IDENTIFIED WITH mysql_native_password BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.1';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

3. 在 192.168.0.1 配置指向 192.168.0.2 ​

sql
CHANGE MASTER TO
  MASTER_HOST = '192.168.0.2',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'Repl@123456',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 157,
  GET_MASTER_PUBLIC_KEY = 1;
START SLAVE;
SHOW SLAVE STATUS\G

双主执行脚本 ​

node2_master_setup.sh（在 192.168.0.2 执行）：

bash
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="your_root_password"

mysql -u$MYSQL_USER -p$MYSQL_PASS <<EOF
CREATE USER 'repl'@'192.168.0.1'
  IDENTIFIED WITH mysql_native_password BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.1';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
EOF

node1_slave_setup.sh（在 192.168.0.1 执行）：

bash
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="your_root_password"
MASTER_LOG_FILE="mysql-bin.000001"
MASTER_LOG_POS=157

mysql -u$MYSQL_USER -p$MYSQL_PASS <<EOF
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST = '192.168.0.2',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'Repl@123456',
  MASTER_LOG_FILE = '$MASTER_LOG_FILE',
  MASTER_LOG_POS = $MASTER_LOG_POS,
  GET_MASTER_PUBLIC_KEY = 1;
START SLAVE;
SHOW SLAVE STATUS\G
EOF

三、常见问题 ​

Q：Slave_IO_Running: No 检查主库防火墙是否放开 3306，以及复制账号的 host 是否匹配。

Q：主键冲突 双主必须配置 auto_increment_increment 和 auto_increment_offset，两节点 offset 不能相同。

Q：升级 GTID 模式 MySQL 8.0 推荐用 GTID 替代 binlog position，更不容易出错，后续可升级。

WangKai's Book

Copyright © 2026