数据库迁移与数据导入导出

把数据库从一台服务器搬到另一台,或升级到新版本,最稳妥的方式是逻辑迁移:先把数据导出成 SQL 或 CSV,再导入新库。它跨版本、跨机器兼容性最好,过程可见、可核对。本文以 Ubuntu/Debian 上的 MySQL/MariaDB 与 PostgreSQL 为例。

逻辑导出

MySQL/MariaDB 用 mysqldump,PostgreSQL 用 pgdump。导出时务必显式声明字符集,避免乱码:

# MySQL:--single-transaction 保证一致性快照且不锁表(InnoDB)
mysqldump --single-transaction --default-character-set=utf8mb4 \
  -u root -p mydb | gzip > mydb.sql.gz

# PostgreSQL:-Fc 自定义压缩格式,配合 pg_restore 可并行
pg_dump -Fc -U postgres mydb > mydb.dump

导入到新库:

gunzip < mydb.sql.gz | mysql -u root -p mydb_new
pg_restore -j 4 -U postgres -d mydb_new mydb.dump

跨版本注意事项

• 字符集:老库常是 utf8(其实只支持 3 字节),新库建议统一 utf8mb4,否则 emoji、部分中文会丢。建库时 CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4unicodeci;。
• 时区:确认两台机器 SELECT @@global.timezone; 一致,或全部用 UTC 存储。TIMESTAMP 会随会话时区偏移,DATETIME 不会。
• 存储引擎:确保新库仍是 InnoDB,别在导入时退回 MyISAM(无事务、不安全)。
• SQL 模式:新版 MySQL 默认开启严格模式,老数据的零日期 0000-00-00、超长字段可能报错,先按需调整 sqlmode。

大表:分批与管道直传

大表不必落地成文件,用管道从源直连目标,省磁盘、少一次 IO:

mysqldump --single-transaction --default-character-set=utf8mb4 mydb bigtable \
  | ssh user@newhost "mysql -u root -pPASS mydb_new"

对超大表,可只导某张表或按主键区间分批(--where="id BETWEEN 1 AND 1000000"),分几次跑,降低单次内存与网络压力。

CSV 导入导出

单表批量数据用 CSV 最快。PostgreSQL 的 COPY 与 MySQL 的 LOAD DATA 都走服务端批量通道,远快于逐行 INSERT:

-- MySQL 导入(需 local_infile 允许)
LOAD DATA LOCAL INFILE 'users.csv' INTO TABLE users
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 LINES;

-- PostgreSQL
\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true);

迁移前后核对

导完必须核对,别凭感觉。逐表比对行数:

SELECT COUNT(*) FROM users;

关键表再抽查校验和(如某金额字段 SUM())。行数一致只是必要条件,建议对核心表做一次业务层抽样比对。

停机窗口与只读切换

要保证零丢数据,导出瞬间之后写入的数据不能丢:

• 提前公告一个停机/只读窗口,选低峰时段。
• 切旧库为只读(MySQL SET GLOBAL readonly = ON;),阻止新写入。
• 导出 → 导入 → 核对行数。
• 应用配置指向新库,验证读写正常后再放开流量。

> 风险提醒:动手前先在新库跑一遍完整演练;mysqldump 直接管道到生产目标前,务必确认目标库名正确,避免覆盖现网数据;凭据别写进命令行(会进 shell history),用 /.my.cnf 或 /.pgpass。

小结

逻辑迁移=导出、导入、核对三步。显式声明 utf8mb4、统一时区、确认引擎是关键坑点;大表用管道直传或分批;CSV 批量走 LOAD DATA/COPY;上线前用只读窗口冻结旧库,靠行数与校验和确认一致,再切流量。