mysql8.0.32到mysql9.2的主从复制实战操作踩坑记

现场环境为一个已运行两年的旧项目，主库位于windows server，从库为新增的一个docker上，版本不同；

前置动作：为了保持主从两个库之间的数据初始化相同，我首先把需要同步的库的数据，从主库全部导出到了从库中，这里遇到第一个坑:

主外键，触发器等会导致插入数据的顺序不对而导入失败，我使用的方案是：先导出主库的建表脚本，然后手动去掉所有的外键，然后再导入数据；

1.主库

修改 my.ini（通常位于 C:\ProgramData\MySQL\MySQL Server 8.0\）

[mysqld]
server-id = 1 # 主库唯一ID
log_bin = mysql-bin # 启用二进制日志
binlog_format = ROW # 推荐使用ROW格式
binlog_row_image = FULL # 记录完整的行数据
expire_logs_days = 7 # 日志保留7天
max_binlog_size = 100M # 每个binlog文件最大100MB

binlog-ignore-db=mysql #想忽略不记录binlog的数据库，也不会同步到从库

重启mysql服务，配置生效；

net stop mysql
net start mysql

创建复制用户

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

查看主库状态（记录 File 和 Position）

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+

2 从库操作：

编辑 /etc/mysql/my.cnf：

[mysqld]
server-id = 2 # 从库唯一ID（不能与主库相同）
read_only = 1 # 从库只读
relay_log = relay-log # 中继日志
log_bin = mysql-bin # 从库也记录binlog（可选）

重启mysql服务配置生效;

配置复制（使用新版语法)

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='主库IP',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='replpasswd',
  SOURCE_LOG_FILE='mysql-bin.000001',  # 主库SHOW MASTER STATUS的File
  SOURCE_LOG_POS=154;                  # 主库SHOW MASTER STATUS的Position

 

配置复制（旧版版语法）

CHANGE MASTER TO
 MASTER_HOST='0.0.0.0',
 MASTER_USER='root',
 MASTER_PASSWORD='root',
 MASTER_LOG_FILE='mysql-bin.000001',  -- 替换为实际的 binlog 文件名
 MASTER_LOG_POS=154,                -- 替换为实际的 position
 GET_MASTER_PUBLIC_KEY=1;

启动主从:

START REPLICA;  # 替代旧的 START SLAVE

查看复制状态:

SHOW REPLICA STATUS;  # 替代旧的 SHOW SLAVE STATUS

关键字段：

• Replica_IO_Running：是否连接主库（Yes/No）

• Replica_SQL_Running：是否执行SQL（Yes/No）

• Last_IO_Error：I/O线程错误信息

• 启用GTID（避免位置依赖）：

• # 主库和从库的my.cnf
  [mysqld]
  gtid_mode = ON
  enforce_gtid_consistency = ON

• binlog-ignore-db 用于指定不记录到二进制日志的数据库。换句话说，配置后这些数据库的所有更改都不会被写入binlog，因此也不会被复制到从库

• 1. 区分大小写：数据库名称匹配是区分大小写的

  2. 多个数据库：需要为每个要忽略的数据库单独配置一行

  3. 与replicate-ignore-db的区别：

     • binlog-ignore-db在主库端过滤，完全不记录到binlog

     • replicate-ignore-db在从库端过滤，binlog中有记录但从库不执行

  4. 通配符不支持：不能使用通配符如*，必须明确指定数据库名