mysql迁移到新硬件环境中的数据迁移策略

对 InnoDB 库且可短暂停写，应加 --single-transaction；它通过一致性快照避免锁表、不阻塞 DML，但需注意引擎兼容性、事务控制、超时设置及跨版本 SQL 模式差异。

mysqldump 导出时要不要加 --single-transaction

如果源库是 InnoDB 且业务可短暂停写，--single-transaction 是最稳妥的选择。它通过开启一致性快照避免锁表，导出过程不会阻塞 DML（但 DDL 仍会被阻塞）。
不过要注意：--single-transaction 对 MyISAM 表无效，会退化为表级锁；如果库中混用引擎，建议先用 SELECT ENGINE, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db'; 检查。
常见错误是没关掉自动提交又没加事务控制，导致大表导出中途失败后残留临时文件。

• 只对 InnoDB 生效，MyISAM 或混合引擎需搭配 --lock-tables 或停写
• 必须配合 --databases 或指定库名使用，单独用 --all-databases 时也生效
• 导出前确认 innodb_lock_wait_timeout 足够长，否则长事务可能中断 dump

目标 MySQL 版本高于源库时的兼容性风险

高版本 MySQL 默认启用更严格的 SQL 模式（如 STRICT_TRANS_TABLES、NO_ZERO_DATE），而旧库导出的 SQL 可能含隐式类型转换或非法日期（如 '0000-00-00'），直接导入会报错 Incorrect date value 或 Data truncated。
解决方式不是降级目标库，而是导出时加 --compatible=mysql40（或对应旧版本），或导入前在目标库临时设置：

SET sql_mode='';

导入完成后再恢复严格模式。

• 用 SELECT @@sql_mode; 分别查源库和目标库的默认模式差异
• mysqldump --no-create-info 配合 --skip-extended-insert 更易定位插入失败的具体行
• 5.7 升 8.0 时注意 mysql.user 表结构变化，不建议直接拷贝系统库

大库（>100GB）迁移时跳过 mysqldump 的替代方案

单纯靠 mysqldump 导入百 GB 级数据，I/O 和单线程瓶颈明显，常卡在“Waiting for table flush”或“Repair by sorting”。这时应优先考虑物理复制：
停写源库 → FLUSH TABLES WITH READ LOCK; → 记录 SHOW MASTER STATUS; → 直接拷贝 datadir 下对应库的文件夹（含 .ibd/.frm/.isl）→ 在目标机启动 MySQL 前替换数据目录并修正权限 → 启动后执行 ALTER TABLE ... IMPORT TABLESPACE;（仅限独立表空间）。

• 必须确保源/目标 MySQL 的 innodb_file_per_table 开启状态一致
• 8.0+ 使用数据字典，不再有 .frm 文件，需用 mysqlpump 或 mysqlbackup 替代
• 若无法停写，可用 Percona XtraBackup 做热备份，但要求目标机安装相同 major 版本的 Percona Server 或兼容版

迁移后验证主从延迟和数据一致性

即使导入成功，也不代表数据完全一致。尤其跨版本迁移后，CHECKSUM TABLE 可能因排序规则或隐式转换返回不同值。更可靠的方式是用 pt-table-checksum（Percona Toolkit）做行级比对，它会自动分块、避开锁表高峰，并生成校验结果表供查询。
主从场景下，还要检查 Seconds_Behind_Master 是否归零，以及 SHOW SLAVE STATUS\G 中的 Exec_Master_Log_Pos 是否与源库 SHOW MASTER STATUS 一致。

• 校验前先在目标库关闭 foreign_key_checks 和 unique_checks 加速
• 避免在校验期间执行 OPTIMIZE TABLE 或 ANALYZE TABLE，会干扰位置点
• 若发现不一致，用 pt-table-sync 修复，但务必先备份目标库

实际迁移中最容易被忽略的是字符集继承问题：mysqldump 默认按连接字符集导出，如果源库建表时没显式声明 CHAR

ACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci，而目标 MySQL 8.0 默认用新 collation，会导致排序行为突变。导出时加上 --default-character-set=utf8mb4 并在目标库创建库时显式指定，比事后修复成本低得多。