MySQL数据库主主同步

使用场景：两台服务器互为从主，数据库自动同步

事先准备

开启防火墙

Debian/Ubuntu 检查UFW 是否启用

sudo ufw status

如果显示 “Status: inactive” 说明防火墙未启用,需要安装UFW

sudo apt update
sudo apt install ufw

⚠️ 安全提醒：允许SSH连接（重要！防止被锁在系统外）

sudo ufw allow ssh

如果您的 SSH 端口是 5522

sudo ufw allow 5522/tcp

开放3306端口

sudo ufw allow 3306/tcp

仅允许特定IP访问3306端口（可选）

sudo ufw allow from 1.1.1.1 to any port 3306 proto tcp

启用UFW

sudo ufw enable

防火墙设置完毕

开启二进制日志

检查二进制日志是否开启

mysql -u root -p

输入root密码后进入MySQL命令行,然后执行以下命令

SHOW VARIABLES LIKE '%log_bin%';

如果看到 log_bin 的值为 ON，则表示已开启。

如何开启二进制日志？

编辑MySQL配置文件 /etc/my.cnf 在 [mysqld] 部分添加或确认以下配置：

[mysqld]
log-bin=mysql-bin        # 启用二进制日志
server-id=1              # 服务器唯一ID

宝塔面板用户可以在mysql面板开启

前提条件

⚠️ 在开始配置前：

务必备份所有数据库数据
确保两台服务器的MySQL数据完全一致
停止MySQL服务以防止新数据写入

互相授权

在服务器 A (1.1.1.1) 上执行

登录MySQL

mysql -u root -p  # 输入数据库密码进入

检查现有用户和权限（可选）

SELECT user, host FROM mysql.user;

查看特定用户的权限（服务器B的IP：2.2.2.2）

SHOW GRANTS FOR 'tongbu'@'2.2.2.2';

创建同步用户并授权
授权账号为tongbu，密码为123456 服务器B的ip：2.2.2.2
创建用户给服务器B连接使用

CREATE USER 'tongbu'@'2.2.2.2' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'2.2.2.2';
FLUSH PRIVILEGES;

输入exit退出

在服务器B (2.2.2.2) 上执行

登录MySQL

mysql -u root -p  # 输入数据库密码进入

创建同步用户并授权
授权账号为tongbu，密码为123456 服务器A的ip：1.1.1.1
创建用户给服务器A连接使用

CREATE USER 'tongbu'@'1.1.1.1' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'1.1.1.1';
FLUSH PRIVILEGES;

输入exit退出

连接测试

先退出数据库，从服务器A连接到服务器B：

mysql -h 2.2.2.2 -u tongbu -p
# 输入在服务器B上创建tongbu用户时设置的密码：123456

先退出数据库，从服务器B连接到服务器A：

mysql -h 1.1.1.1 -u tongbu -p
# 输入在服务器A上创建tongbu用户时设置的密码：123456

输入exit退出

配置my.cnf

假设要同步的数据库有：db1、db2，每行一个

⚠️注意：检查 server-id 是否已经存在，不要重复，且server-id数值不能和B服务器一样

进入服务器A，编辑my.cnf文件：vi /etc/my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin

# 指定需要同步的数据库
binlog-do-db=db1
binlog-do-db=db2

# 指定需要复制的数据库
replicate-do-db=db1
replicate-do-db=db2

auto_increment_increment=2
auto_increment_offset=1

进入服务器B，编辑my.cnf文件：vi /etc/my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin

# 指定需要同步的数据库
binlog-do-db=db1
binlog-do-db=db2

# 指定需要复制的数据库
replicate-do-db=db1
replicate-do-db=db2

auto_increment_increment=2
auto_increment_offset=2

获取日志信息

登录MySQL

mysql -u root -p  # 输入数据库密码进入

在两台服务器上分别执行：

SHOW MASTER STATUS;

得到File和Position。分别记录下来。

配置主主复制

假设:

服务器A：File是mysql-bin.000009，Position是153

服务器B：File是mysql-bin.000010，Position是154

那么

在服务器A上执行：

-- 1. 停止同步
STOP SLAVE;

-- 2. 重置服务器状态
RESET SLAVE;

-- 3. 重新配置主从关系
CHANGE MASTER TO
    MASTER_HOST='服务器B IP',
    MASTER_USER='tongbu',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000010',
    MASTER_LOG_POS=154;

-- 4. 启动同步
START SLAVE;

在服务器B上执行：

-- 1. 停止同步
STOP SLAVE;

-- 2. 重置服务器状态
RESET SLAVE;

-- 3. 重新配置主从关系
CHANGE MASTER TO
    MASTER_HOST='服务器A IP',
    MASTER_USER='tongbu',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000009',
    MASTER_LOG_POS=153;

-- 4. 启动同步
START SLAVE;

完成，输入exit退出

在两台服务器上分别执行（重启数据库）

service mysqld restart

验证复制状态

登录MySQL

mysql -u root -p  # 输入数据库密码进入

在两台服务器上分别执行：

SHOW SLAVE STATUS\G

检查以下两个参数是否都为Yes：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果都显示Yes，则表示主主复制配置成功。建议进行数据写入测试，确认同步功能正常。

故障排查

假设数据库db1报错，那么查询db1是否存在

SHOW DATABASES;

如果不存在，执行以下命令，创建数据库 db1

CREATE DATABASE `db1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

停止同步

STOP SLAVE;

授予权限

GRANT ALL PRIVILEGES ON db1.* 
TO 'tongbu'@'1.1.1.1' 
IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

重启同步

START SLAVE;

检查状态

SHOW SLAVE STATUS\G

删除同步用户

登录mysql

mysql -u root -p

停止同步

STOP SLAVE;

查询同步用户

SELECT user, host FROM mysql.user;

删除指定IP的用户

DROP USER 'tongbu'@'2.2.2.2';

如果有多个IP的同名用户，需要分别删除

DROP USER 'tongbu'@'1.1.1.1';

刷新权限

FLUSH PRIVILEGES;

验证用户是否删除成功

SELECT user, host FROM mysql.user;

清理配置文件（可选）编辑 /etc/my.cnf，删除或注释掉同步相关配置：

# [mysqld]
# server-id=1
# log-bin=mysql-bin
# binlog-do-db=xxx_data
# replicate-do-db=xxx_data
# auto_increment_increment=2
# auto_increment_offset=1

退出，重启MySQL使配置生效

service mysql restart