如何在LNMP中进行数据库迁移

LNMP数据库迁移实操指南

一、迁移方案与准备

• 明确目标：在单机内做库表迁移、将数据库从 Web 服务器拆分到独立服务器、或在不同数据库引擎间迁移（如 MySQL → MariaDB 或 MySQL → PostgreSQL）。
• 选择方式：
  • 同构迁移（如 MySQL ↔ MariaDB）可直接用逻辑备份/导入；
  • 异构迁移（如 MySQL → PostgreSQL）建议使用 pgloader 等工具处理语法差异。
• 规划窗口与回滚：选择低峰时段，准备回滚方案（保留旧库只读或随时可切回）。
• 账号与权限：准备具有足够权限的数据库账号，并为应用创建最小权限的专用账号。
• 网络与防火墙：确保 Web 与数据库之间的 3306 端口可达（云服务器需配置安全组/防火墙放行）。

二、标准步骤（同构迁移：MySQL/MariaDB）

1. 全量备份
   • 推荐导出全部库或指定库：
     • 全库：mysqldump -u root -p --all-databases --routines --triggers --events --single-transaction --hex-blob --set-gtid-purged=OFF > backup.sql
     • 单库：mysqldump -u root -p --databases your_db --single-transaction --hex-blob > your_db.sql
   • 大库可压缩：mysqldump ... | gzip > backup.sql.gz。
2. 传输备份
   • 复制到新库服务器：scp backup.sql.gz db01:/root/。
3. 准备目标库
   • 登录新库：mysql -u root -p
   • 创建库（如迁移单库）：CREATE DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4. 导入数据
   • 解压后导入：mysql -u root -p your_db < backup.sql
   • 压缩包导入：gunzip < backup.sql.gz | mysql -u root -p your_db
5. 创建应用账号与授权（示例）
   • GRANT ALL ON your_db.* TO 'app_user'@'WEB_IP' IDENTIFIED BY 'StrongPass!'; FLUSH PRIVILEGES;
6. 修改应用配置
   • 例如 WordPress：编辑 wp-config.php
     • define('DB_NAME','your_db'); define('DB_USER','app_user'); define('DB_PASSWORD','StrongPass!'); define('DB_HOST','db01_ip_or_host');
7. 切换与验证
   • 重启服务：systemctl restart nginx php-fpm（如有需要）
   • 访问前台与后台，执行登录、发布、搜索等操作；核对数据一致性与错误日志。
8. 回滚预案
   • 若异常，立即将应用 DB_HOST 指回旧库，确认无误后再下线旧库。

三、跨服务器迁移与架构拆分要点

• 在数据库服务器（如 db01）安装并启动 MariaDB/MySQL，确认 3306 监听并允许应用来源 IP 访问。
• 从 Web 服务器导出全量或指定库，使用 scp 复制到 db01，在 db01 上导入。
• 在 db01 上为应用创建最小权限账号并授权来源网段（避免使用 root 远程）。
• 在 Web 服务器上停止本地 mysqld，修改应用配置中的 DB_HOST 为新库地址，测试连通与功能。
• 如需彻底拆分，可保留旧库只读一段时间，待新库稳定后再关闭。

四、异构迁移与常见问题处理

• 异构迁移（MySQL → PostgreSQL）
  • 建议先在同构环境完成结构与数据迁移，再用 pgloader 迁移到 PostgreSQL：
    • pgloader mysql://root@localhost/your_db postgresql://postgres@localhost/your_db
  • 注意语法/类型差异（如自增、布尔、索引、默认值等）可能需要应用侧或 SQL 调整。
• 常见问题与优化
  • 导入中断或报错：适当增大 max_allowed_packet、innodb_buffer_pool_size，必要时分批导入。
  • 字符集乱码：导出/导入统一使用 utf8mb4，命令中显式指定 --default-character-set=utf8mb4。
  • 大文件导入慢：使用压缩流导出/导入，减少磁盘 I/O 与网络传输量。
  • 远程连接被拒：检查用户主机白名单、密码、以及服务器防火墙/云安全组是否放行 3306。
  • 权限与安全：避免使用 root 远程；为应用创建最小权限账号；必要时限制来源网段。

五、一键命令清单（可直接复用）

• 备份单库并压缩
  • mysqldump -u root -p --databases your_db --single-transaction --hex-blob | gzip > your_db.sql.gz
• 复制到新库服务器
  • scp your_db.sql.gz db01:/root/
• 在 db01 创建库并导入
  • mysql -u root -p -e "CREATE DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
  • gunzip < /root/your_db.sql.gz | mysql -u root -p your_db
• 授权应用账号（示例）
  • mysql -u root -p -e "GRANT ALL ON your_db.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'Passw0rd!'; FLUSH PRIVILEGES;"
• 修改 WordPress 配置并重启
  • sed -i "s/define('DB_HOST', '.*');/define('DB_HOST', 'db01_ip');/" /usr/local/nginx/html/wp-config.php
  • systemctl restart nginx php-fpm