MySQL 5.7到8.0主从同步升级实战：兼容性处理与数据迁移指南

1. 项目概述：从5.7到8.0，一次平滑的主从同步升级

最近在帮一个线上业务做数据库架构的梳理，核心需求是把一个跑在MySQL 5.7上的从库，升级到MySQL 8.0，并重新建立与5.7主库的同步关系。这听起来像是个简单的版本升级加主从搭建，但实际操作起来，你会发现从5.7到8.0的跨越，远不止改个版本号那么简单。MySQL 8.0在性能、安全性和功能上带来了巨大提升，比如更好的JSON支持、窗口函数、原子DDL，以及默认的身份认证插件从mysql_native_password改成了caching_sha2_password。正是这些“提升”，让跨大版本的主从同步变得需要格外小心，一不留神就会踩进兼容性的坑里。

这个项目适合所有计划将MySQL从5.7迁移或升级到8.0的运维工程师和开发者。无论你是为了尝鲜8.0的新特性，还是因为某些新应用强制要求8.0环境，亦或是像我一样需要在一个混合版本的环境中维持数据同步，这篇记录都能给你提供一个经过实战检验的路线图。我会把重点放在那些官方文档可能一笔带过，但实际操作中却会让你耗费数小时的细节上，比如GTID的兼容性处理、用户权限的同步，以及字符集校验规则带来的潜在问题。

2. 升级前核心评估与准备工作

在动手之前，盲目操作是灾难的开始。从5.7到8.0的主从同步，不是简单的安装新版本然后CHANGE MASTER，必须进行一次全面的前置评估。

2.1 兼容性风险点深度剖析

首先，我们必须正视几个关键的兼容性断点，它们直接决定了同步能否成功建立并稳定运行。

1. 身份认证插件（Authentication Plugin）：这是最大的一个“坑”。MySQL 5.7默认使用mysql_native_password，而MySQL 8.0默认使用caching_sha2_password。如果主库（5.7）上用于复制的用户（例如repl）使用的是默认插件，那么8.0的从库将无法直接连接认证。错误信息通常会提示“Authentication plugin ‘caching_sha2_password‘ cannot be loaded”或类似的认证失败。
2. GTID（全局事务标识符）兼容性：GTID是5.6版本引入的，在5.7和8.0中都是核心复制特性，格式基本兼容。但是，你需要确保主从库的gtid_mode和enforce_gtid_consistency参数设置一致且正确。如果主库使用了GTID，从库也必须开启并正确设置。
3. SQL Mode（SQL模式）：MySQL 8.0的默认SQL Mode比5.7更严格，包含了ONLY_FULL_GROUP_BY等。如果主库的SQL Mode比较宽松，从库应用binlog时可能会因为更严格的语法检查而报错，导致复制中断。
4. 字符集与校验规则（Character Set and Collation）：MySQL 8.0的默认字符集从latin1改为了utf8mb4，默认校验规则从latin1_swedish_ci改为了utf8mb4_0900_ai_ci。虽然这通常不影响数据同步本身（因为binlog里记录的是实际的字节数据），但如果你的应用或某些查询依赖特定的排序规则，在从库上执行时可能会产生不同的结果。
5. 系统表与数据字典：MySQL 8.0重构了数据字典，系统表（如user、db等）的结构和存储引擎（改为InnoDB）都发生了变化。这意味着你不能直接拷贝mysql系统数据库的文件。用户和权限必须在主库准备好，并在从库上通过SQL语句或工具来同步。

2.2 环境与数据备份策略

评估完风险，下一步就是为整个操作创造一个安全的环境。

1. 从库环境准备：

   • 全新安装MySQL 8.0：强烈建议在一台新的服务器上安装MySQL 8.0，而不是在原5.7从库服务器上原地升级。这提供了清晰的回滚路径：如果新8.0从库同步失败，只需停掉它，原来的5.7从库可以立刻恢复服务。
   • 版本选择：选择MySQL 8.0的一个稳定版本，例如8.0.36。避免使用过新的小版本，以免引入未知Bug。
   • 基础配置：提前调整好新从库的server_id，确保其与主库和其他从库都不同。根据服务器硬件配置，预先优化innodb_buffer_pool_size等关键参数。

2. 全量备份与恢复演练：

   • 备份工具选择：使用mysqldump进行逻辑备份是跨版本最安全的方式。虽然xtrabackup速度更快，但在5.7到8.0的大版本跨越中，物理备份的兼容性风险更高。
   • 备份命令示例：

     # 在主库或原5.7从库上执行 mysqldump -h主库IP -u用户名 -p --all-databases --master-data=2 --single-transaction --routines --events --triggers --set-gtid-purged=ON > full_backup.sql

     • --master-data=2：在备份文件中以注释形式记录备份时刻的binlog位置和GTID信息，对搭建从库至关重要。
     • --single-transaction：对InnoDB表进行一致性备份，不锁表。
     • --set-gtid-purged=ON/AUTO：确保备份文件包含正确的GTID信息。
   • 恢复演练：务必在测试环境，用这份备份文件恢复到MySQL 8.0实例中，验证数据完整性和基本查询功能。这一步能提前发现潜在的字符集或数据类型兼容性问题。

注意：备份文件可能非常大，恢复耗时较长。务必估算好时间窗口，并在业务低峰期进行操作。

3. 分步实施：构建5.7到8.0的主从链路

准备工作万无一失后，我们开始核心的实施步骤。这个过程环环相扣，每一步的细节都决定了最终的成败。

3.1 步骤一：在主库（MySQL 5.7）上的关键操作

主库的配置相对简单，但有几个点必须精确执行。

1. 创建专用的复制账户：为了避免认证插件问题，我们显式指定使用mysql_native_password插件创建用户。

   -- 在主库执行 CREATE USER 'repl'@'从库IP段' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'从库IP段'; FLUSH PRIVILEGES;

   这里的关键是IDENTIFIED WITH mysql_native_password，强制使用了5.7的默认插件，确保了8.0从库可以兼容连接。

2. 确认主库状态并记录点位：在主库执行SHOW MASTER STATUS\G，记录下当前的File和Position。如果主库启用了GTID，则记录Executed_Gtid_Set的值。这个信息将用于从库的初始定位。

3.2 步骤二：在从库（MySQL 8.0）上的配置与数据灌入

这是操作最密集的部分。

1. 初始化MySQL 8.0并调整关键参数：安装完成后，编辑my.cnf配置文件，以下参数需重点关注：

   [mysqld] server-id = 2 # 必须唯一，且不同于主库 gtid_mode = ON # 如果主库开启，此处也必须为ON enforce_gtid_consistency = ON log_bin = mysql-bin binlog_format = ROW # 推荐使用ROW格式，兼容性最好 # 处理认证插件兼容性：允许使用mysql_native_password default_authentication_plugin=mysql_native_password # 可选：如果担心SQL Mode问题，可以先设置与主库一致，同步稳定后再调整 # sql_mode = '主库的sql_mode'

   修改配置后，重启MySQL 8.0服务。

2. 导入全量备份数据：将之前准备好的full_backup.sql文件拷贝到从库服务器，进行导入。

   mysql -u root -p < full_backup.sql

   这个过程可能会很长，取决于数据量大小。导入完成后，不要急于启动复制。

3. 应用备份中的GTID信息（如果使用GTID）：查看备份文件头部，找到SET @@GLOBAL.GTID_PURGED开头的行。在从库上，需要先重置gtid_purged，再应用这个集合，以确保从库知道哪些事务已经执行过了。

   -- 在从库执行 RESET MASTER; -- 警告：此操作会清空从库现有的binlog和GTID信息，仅适用于全新从库 -- 然后，从备份文件中找到类似下面的行，并执行 -- SET @@GLOBAL.GTID_PURGED = ‘主库的GTID集合’;

3.3 步骤三：建立复制链路并启动同步

数据就位后，开始建立主从关系。

1. 配置复制通道：在MySQL 8.0中，推荐使用CHANGE REPLICATION SOURCE TO语法（8.0.23+），但传统的CHANGE MASTER TO语法仍然兼容。这里使用新语法示例：

   -- 在从库执行 CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库IP', SOURCE_USER='repl', SOURCE_PASSWORD='StrongPassword123!', SOURCE_PORT=3306, SOURCE_AUTO_POSITION = 1, -- 如果使用GTID，设置为1 SOURCE_SSL = 0; -- 根据实际情况调整是否使用SSL

   • 如果不使用GTID，需要指定SOURCE_LOG_FILE和SOURCE_LOG_POS，即之前记录的File和Position。
   • SOURCE_AUTO_POSITION=1是GTID复制的精髓，它让从库自动根据GTID集合来定位同步起点。

2. 启动复制并监控状态：

   START REPLICA; -- MySQL 8.0 新语法，等同于 START SLAVE

   启动后，立即检查复制状态：

   SHOW REPLICA STATUS\G -- 等同于 SHOW SLAVE STATUS\G

   你需要重点关注以下几个字段：

   • Replica_IO_Running和Replica_SQL_Running：必须都为Yes。
   • Last_IO_Error和Last_SQL_Error：任何错误信息都会在这里显示。
   • Seconds_Behind_Master：表示复制延迟，初始会比较大，随着数据追平会逐渐减少到0附近。
   • Retrieved_Gtid_Set和Executed_Gtid_Set：查看GTID的接收和执行情况。

4. 同步建立后的验证与监控要点

复制状态显示正常，并不代表万事大吉，必须进行业务层面的验证。

4.1 数据一致性校验

这是最核心的验证环节。不能只相信状态报告。

1. 行数对比：对核心业务表，在主库和从库分别执行SELECT COUNT(*)，对比结果是否一致。这只是一种快速检查，无法发现数据内容的不一致。
2. 使用专业工具进行校验：推荐使用Percona Toolkit中的pt-table-checksum。它会在主库上对数据块计算校验和，并通过复制将计算过程应用到从库，最后对比结果。但请注意，在5.7主库和8.0从库的环境下，直接使用该工具可能需要额外的兼容性测试。更稳妥的方法是，在业务低峰期，对少量最关键的表进行手动校验查询。
3. 业务查询验证：编写几个典型的业务查询语句（特别是涉及关联、聚合和排序的），分别在主库和从库执行，对比结果集是否完全相同。这可以验证SQL模式、字符集校验规则是否导致了不同的执行结果。

4.2 性能与稳定性监控

同步建立后，需要一段时间的观察期。

1. 监控复制延迟：持续观察Seconds_Behind_Master。如果延迟持续存在且不减少，可能原因有：从库服务器性能不足（I/O或CPU瓶颈）、网络带宽问题、或者从库上有大查询阻塞了SQL线程。
2. 监控错误日志：定期检查MySQL 8.0从库的错误日志（error.log），捕捉任何潜在的警告或错误信息。8.0的日志格式可能更详细，有助于提前发现问题。
3. 压力测试：在测试环境，模拟业务读写压力，观察主从同步是否稳定，延迟是否在可接受范围内。重点关注DDL操作（如加索引、改表结构）的同步情况，因为MySQL 8.0支持原子DDL，其行为与5.7可能存在细微差别。

5. 常见故障场景与排查实战记录

在实际操作中，几乎不可能一帆风顺。下面是我遇到或常见的几个问题及解决方法。

5.1 错误1：复制用户认证失败

• 现象：SHOW REPLICA STATUS\G显示Last_IO_Error: error connecting to master ... Authentication plugin ‘caching_sha2_password‘ reported error: Authentication requires secure connection.
• 根因：主库（5.7）的复制用户虽然用了mysql_native_password创建，但可能从库（8.0）尝试使用其他方式认证，或者网络要求SSL。
• 解决方案：
  1. 确认主库用户创建语句是否正确（如3.1步骤所示）。
  2. 在从库配置中，尝试明确指定SOURCE_SSL=0。
  3. 如果主库强制要求SSL，则需要在从库配置SOURCE_SSL=1并提供相关证书，但这在5.7环境中较少见。最根本的还是在主库创建用户时指定插件并简化认证要求。

5.2 错误2：SQL线程因SQL模式错误中断

• 现象：Replica_SQL_Running: No，Last_SQL_Error: ... Error ‘Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ...‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
• 根因：MySQL 8.0默认开启了ONLY_FULL_GROUP_BY的SQL模式，而主库5.7的binlog里记录的SQL可能不符合此严格模式。
• 解决方案：
  1. 临时解决：在从库上动态修改SQL Mode，移除ONLY_FULL_GROUP_BY。

     SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

     然后重启SQL线程：STOP REPLICA SQL_THREAD; START REPLICA SQL_THREAD;
  2. 根本解决：建议先在主库（5.7）上找出引发错误的SQL语句并进行优化，使其符合更严格的SQL标准。这才是治本之策。修改从库的SQL Mode只是权宜之计，可能掩盖其他潜在问题。

5.3 错误3：GTID同步间隙（Gap）问题

• 现象：复制中断，错误提示某个GTID事务无法执行，因为前置事务缺失。
• 根因：这通常发生在备份恢复环节。例如，从库通过mysqldump恢复后，其gtid_purged集合设置不正确，或者恢复过程中有事务被跳过。
• 解决方案：
  1. 仔细核对主库的Executed_Gtid_Set和从库的gtid_purged、Executed_Gtid_Set。
  2. 如果确定缺失的事务是无关紧要的（例如，是在某个测试数据库上执行的），可以在从库上“空投”这个GTID，标记为已执行。

     SET GTID_NEXT='缺失的GTID'; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC';

     警告：此操作需极度谨慎，必须100%确认该事务的内容可以跳过，否则会导致数据不一致。
  3. 更安全的方法是，重新从主库做一个精确的、包含缺失GTID时间点的备份，并在从库上完全重建。

5.4 错误4：表不存在或表结构不匹配错误

• 现象：SQL线程在应用某个DDL或DML语句时，报错表不存在或列不匹配。
• 根因：可能在复制开始后，有人在从库上手动执行了DDL，或者备份恢复的数据与主库binlog点位不完全对应。
• 解决方案：
  1. 绝对禁止在从库上直接进行写操作（包括DDL）。
  2. 如果错误已经发生，且涉及的表不重要，可以考虑在从库上手动创建或修改该表，使其结构与主库一致（通过SHOW CREATE TABLE在主库查看），然后跳过这个错误的事务。

     STOP REPLICA; SET GLOBAL sql_slave_skip_counter = 1; -- 跳过1个事件，慎用！ START REPLICA;

     或者，如果使用GTID，采用空投GTID的方式跳过。
  3. 最彻底的方法是重建从库。

6. 高阶考量与长期运维建议

当主从同步稳定运行后，还有一些更深层次的问题需要考虑，以确保长期的健康度。

6.1 数据一致性保障机制

主从异步复制本身不保证强一致性。对于金融等关键业务，需要考虑：

• 半同步复制（Semisynchronous Replication）：MySQL 5.7/8.0都支持。它确保主库提交的事务至少被一个从库接收并写入relay log后，才返回成功给客户端。这大大降低了主库宕机导致数据丢失的风险。可以在8.0从库上启用半同步从库插件。
• 定期校验：即使初始校验通过，长期运行后也可能因软硬件错误导致静默数据损坏。应使用pt-table-checksum等工具建立定期（如每周）校验机制。
• 延迟监控告警：对Seconds_Behind_Master设置合理的监控阈值（如30秒），超过即告警，及时排查网络或从库性能问题。

6.2 从库读流量分担与故障切换

搭建8.0从库的目标之一往往是分担主库的读压力。

• 应用层配置：在应用程序的数据库连接配置中，设置读写分离。将写请求定向到主库，读请求定向到8.0从库。可以使用中间件（如MyCat、ProxySQL）或框架自带功能（如Spring Boot的AbstractRoutingDataSource）实现。
• 从库性能优化：由于从库主要承担读操作，可以针对性地优化：增加更多的只读索引（但需注意DDL同步的影响）、使用不同的查询缓存策略、甚至可以使用像MyRocks这样的存储引擎（如果混合引擎环境支持）来优化读性能。
• 故障切换预案：制定清晰的故障切换（Failover）流程。如果主库宕机，如何将8.0从库提升为新主库？这涉及应用连接地址的切换、其他从库（如果存在）的复制源修改、以及可能的数据补偿。工具如Orchestrator、MHA可以辅助自动化，但预案必须经过演练。

6.3 向MySQL 8.0全量迁移的铺垫

本次操作是构建了一个5.7主 -> 8.0从的混合环境。这可以作为一个完美的过渡阶段。

• 灰度验证：将一部分非核心或只读业务流量切到8.0从库，验证其兼容性和性能。在这个阶段，可以充分测试8.0的新特性（如窗口函数、通用表表达式CTE）在现有业务查询上的应用。
• 最终切换：当对8.0从库的稳定性充满信心后，可以计划将主库也升级到8.0。一个经典的方案是：先将8.0从库提升为新的主库（版本8.0），然后将原5.7主库和其余从库作为新主库的从库，逐步完成全集群升级。这需要更详细的计划和时间窗口，但本次5.7到8.0的主从同步实践，为整个升级路径扫清了最大的技术障碍。