centos下mysql数据迁移技巧

CentOS 下 MySQL 数据迁移技巧

一迁移方式选型

逻辑迁移（推荐优先）：使用 mysqldump 导出为 SQL 脚本，在目标机器导入。优点是跨版本、跨平台、可选择性迁移、操作可控；缺点是大数据量时导入时间较长。适合大多数升级、迁移与克隆场景。
物理迁移：直接拷贝数据目录（如 /var/lib/mysql）到新位置或新机器。优点是速度快、一致性高；要求 MySQL 版本尽量一致、停机窗口可控，并注意文件权限与配置调整。适合同版本整机迁移或磁盘扩容。

二标准流程与命令示例

步骤1 版本与环境检查
在源与目标分别执行：mysql --version，确认版本兼容；规划目标机器的 datadir、字符集、端口与防火墙策略。
步骤2 源库备份（逻辑迁移）
全库导出：mysqldump -u <用户> -p --all-databases > alldb_backup.sql
单库导出：mysqldump -u <用户> -p --databases <db1> <db2> > backup.sql
为减少锁等待，可按需添加 --single-transaction（InnoDB）或配合 --lock-tables 的策略。
步骤3 传输到目标机器
scp alldb_backup.sql <目标IP>:/path/to/destination 或 rsync -avP alldb_backup.sql <目标IP>:/path/。
步骤4 目标库导入
全库导入：mysql -u <用户> -p < alldb_backup.sql
单库导入：mysql -u <用户> -p <db1> < backup.sql
步骤5 基本校验
SHOW DATABASES; 对比库数量；抽样 USE <db>; SHOW TABLES; SELECT COUNT(*) FROM <tbl> LIMIT 1; 校验数据量与结构。

三物理迁移与目录切换要点

步骤1 停库：systemctl stop mysqld
步骤2 拷贝数据：如从 /var/lib/mysql 迁至 /data/mysql
cp -a /var/lib/mysql/* /data/mysql/
chown -R mysql:mysql /data/mysql
步骤3 调整配置 /etc/my.cnf
```
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
pid-file=/data/mysql/hostname.pid
```
如为 RPM 第三方包，可能读取 /usr/my.cnf；YUM 安装常见为 /etc/my.cnf。
步骤4 处理 socket 与权限
确保应用与本地 socket 路径一致；必要时在 [client] 段也配置 socket=/data/mysql/mysql.sock。
步骤5 启动与验证
systemctl start mysqld 或 service mysqld start，登录验证 SHOW DATABASES;。
步骤6 忘记 root 密码的恢复（仅在必要时）
mysqld_safe --skip-grant-tables & 后登录更新密码并 FLUSH PRIVILEGES;，随后重启。

四常见问题与处理

权限与连接失败：确认导入用户具备足够权限（如 GRANT ...），以及目标机防火墙/SELinux 策略放行 3306。
字符集乱码：导出与导入统一字符集，例如 mysql -u root -p --default-character-set=utf8mb4 < backup.sql。
版本差异：跨大版本不建议物理拷贝，优先逻辑迁移；必要时评估升级路径与兼容性。
大文件导入超时/中断：在目标端本地导入、分批导入或使用 mysqlimport；必要时调大 max_allowed_packet 与 net_read/write_timeout。
仅迁移部分库表：导出时精确指定库表，导入到已存在的空库；避免误覆盖。
物理迁移后表不可访问（如 1146）：除拷贝各库目录外，需确保共享表空间文件（如 ibdata1）与系统库一致，再重启；操作前务必完整备份。

五迁移前后验证清单

版本与参数：源/目标 MySQL 版本一致或兼容；datadir、socket、character_set_server、collation_server 等关键参数一致。
数据一致性：对比库/表数量与抽样记录数；对核心业务表做 COUNT(*)、SUM() 或校验和比对。
权限与账户：验证应用账户、远程账户、复制账户是否完整迁移并可正常登录。
应用连通性：更新应用 DB_HOST/PORT/SOCKET，在灰度环境验证读写与事务。
备份留档：迁移完成后立即做一次全量备份，保留回滚点。

最新问答