SQL如何迁移数据库到另一个服务器_导出导入与跨网络管道直连

mysqldump加–single-transaction可保证InnoDB表在线一致性导出，避免FLUSH TABLES WITH READ LOCK阻塞写入；导出导入需统一utf8mb4字符集，并注意SQL mode、时区等隐式配置影响。

mysqldump 导出时加 --single-transaction 才能保证一致性

不加这个参数，导出大表可能卡在锁表上，或者导出过程中数据被改，导致目标库和源库不一致。尤其在有写入的生产库上，mysqldump 默认会执行 FLUSH TABLES WITH READ LOCK，阻塞所有写操作——这在高可用场景下不可接受。

使用场景：MySQL 5.6+、InnoDB 表、需要在线导出。

• --single-transaction 依赖事务隔离级别，只对 InnoDB 有效；MyISAM 表仍会锁表
• 必须搭配 --skip-lock-tables，否则 --single-transaction 会被忽略
• 如果导出包含视图或存储过程，额外加上 --routines --triggers --events
• 导出命令示例：

  mysqldump -h old-host -u user -p --single-transaction --skip-lock-tables --routines --triggers db_name > db.sql

mysql 命令导入失败常见于字符集不匹配

导入时报错 Error 1366 (HY000): Incorrect String value 或乱码，基本是源库导出时没指定字符集，或目标库默认编码不是 utf8mb4。

关键点不在导入命令本身，而在连接层和库表定义层：

• 导出时显式指定字符集：mysqldump --default-character-set=utf8mb4 -u user -p db_name > db.sql
• 导入前确认目标库已设为 utf8mb4：ALTER database db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
• 导入命令别用 source，用 mysql 客户端直连并指定编码：

  mysql -h new-host -u user -p --default-character-set=utf8mb4 db_name < db.sql

• 如果 SQL 文件头没有 SET NAMES utf8mb4，手动在文件开头加一行（或用 sed -i '1i SET NAMES utf8mb4;' db.sql）

跨网络直连管道迁移要防超时和中断

用 mysqldump | mysql 管道直传省去中间文件，但一旦网络抖动或连接断开，整个迁移就失败，且无法续传。

这不是“更高级”的方式，而是对稳定性要求更高的取舍：

• 必须加 --net-buffer-Length=1M 和 --max-allowed-packet=512M，否则大 BLOB 字段直接报错 Packets larger than max_allowed_packet
• ssh 隧道比裸连更稳：

  ssh -fN -L 3307:127.0.0.1:3306 user@old-host && mysqldump -h 127.0.0.1 -P 3307 -u user -p --single-transaction db_name | mysql -h new-host -u user -p db_name

• 管道中任何一环失败都不会提示具体哪步挂了，建议加 set -o pipefail 并捕获退出码
• 别在终端里直接跑长管道——用 nohup 或 screen，否则断连即终止

postgresql pg_dump/pg_restore 比 MySQL 更依赖版本兼容性

MySQL 的 SQL 导出基本跨版本兼容，但 PostgreSQL 的 pg_dump 输出的自定义格式（-Fc）不能用高版本 pg_restore 导入到低版本数据库，反之也不行。

实际迁移中容易卡在这里，尤其当新服务器装的是更新的 PG：

• 导出时务必用目标服务器同版本或更低版本的 pg_dump（查 pg_dump --version）
• 优先用纯文本 SQL 格式（-Fp），虽然慢，但兼容性最好：

  pg_dump -h old-host -U user -Fp -v db_name > db.sql

• 若必须用自定义格式做增量或大库迁移，加 --no-owner --no-privileges 避免权限和角色冲突
• 恢复前检查目标库是否启用了相同扩展，比如 postgis、pg_trgm，否则 CREATE EXTENSION 语句会报错

迁移最难的从来不是命令敲得对不对，而是你不知道哪一层的默认值正在悄悄改写你的数据——字符集、时区、SQL mode、search_path、甚至 ssh 的 TCPKeepAlive 设置。这些地方不出问题则已，一出就是半途而废、数据不一致、修复成本远高于重来。