MySQL数据库迁移指南

作为IT工程师，我将为您详细介绍MySQL数据库迁移的完整流程和最佳实践。

迁移前的准备工作

1. 评估现有环境

   • 记录当前MySQL版本
   • 统计数据库大小和表数量
   • 识别特殊配置参数

2. 选择迁移方法

   • 逻辑备份与恢复(使用mysqldump)
   • 物理备份与恢复(复制数据文件)
   • 使用主从复制
   • 使用第三方工具(如Percona XtraBackup)

3. 规划停机窗口

   • 评估业务可接受的停机时间
   • 安排在低峰期进行迁移

使用mysqldump进行逻辑迁移

基本迁移步骤

# 1. 在源服务器导出数据
mysqldump -u [username] -p[password] --all-databases > full_backup.sql

# 2. 将备份文件传输到目标服务器
scp full_backup.sql user@new_server:/path/to/backup/

# 3. 在目标服务器导入数据
mysql -u [username] -p[password] < full_backup.sql

高级选项

# 导出时排除某些表
mysqldump -u root -p --ignore-table=database.table1 --ignore-table=database.table2 database > backup.sql

# 只导出结构
mysqldump -u root -p --no-data database > schema.sql

# 只导出数据
mysqldump -u root -p --no-create-info database > data.sql

# 导出存储过程和函数
mysqldump -u root -p --routines --no-create-info --no-data --no-create-db --skip-opt database > routines.sql

使用物理文件迁移

适用于大型数据库，速度更快：

1. 停止MySQL服务
2. 复制数据目录(通常位于/var/lib/mysql)
3. 复制my.cnf配置文件
4. 确保文件权限正确

# 在源服务器
sudo systemctl stop mysql
tar -czvf mysql_data.tar.gz /var/lib/mysql

# 在目标服务器
sudo systemctl stop mysql
tar -xzvf mysql_data.tar.gz -C /var/lib/
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

使用主从复制进行迁移

1. 在目标服务器安装相同版本的MySQL
2. 配置主从复制
3. 等待数据同步完成
4. 将应用切换到新服务器

-- 在主服务器
CREATE USER 'repl'@'slave_ip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

-- 在从服务器
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=  107;
START SLAVE;
SHOW SLAVE STATUS\G

迁移后验证

1. 数据一致性检查

   • 比较关键表的记录数
   • 抽样检查数据内容
   • 验证外键关系

2. 性能测试

   • 执行典型查询检查响应时间
   • 检查索引使用情况

3. 应用测试

   • 确保所有应用连接正常
   • 验证事务处理能力

常见问题解决

1. 字符集问题

   SHOW VARIABLES LIKE 'character_set%';
   SHOW VARIABLES LIKE 'collation%';
   

   确保源和目标服务器字符集配置一致

2. 权限问题

   -- 导出权限
   mysql -u root -p --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user" | mysql -u root -p --skip-column-names -A | sed 's/$/;/g' > user_grants.sql
   
   -- 导入权限
   mysql -u root -p < user_grants.sql
   

3. 大表迁移优化

   • 使用--single-transaction选项减少锁表时间
   • 对大表分批导出导入

自动化迁移脚本示例

#!/bin/bash

# 配置参数
DB_USER="root"
DB_PASS="password"
BACKUP_DIR="/backup/mysql"
NEW_SERVER="new.server.com"
REMOTE_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)

# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE

# 导出所有数据库
echo "开始导出MySQL数据库..."
mysqldump -u$DB_USER -p$DB_PASS --all-databases --single-transaction --routines --triggers > $BACKUP_DIR/$DATE/full_backup.sql

# 导出用户权限
echo "导出用户权限..."
mysql -u$DB_USER -p$DB_PASS --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user" | mysql -u$DB_USER -p$DB_PASS --skip-column-names -A | sed 's/$/;/g' > $BACKUP_DIR/$DATE/user_grants.sql

# 传输到新服务器
echo "传输文件到新服务器..."
scp $BACKUP_DIR/$DATE/*.sql $NEW_SERVER:$REMOTE_DIR/

# 在新服务器导入(需提前配置SSH免密登录)
echo "在新服务器导入数据..."
ssh $NEW_SERVER "mysql -u$DB_USER -p$DB_PASS < $REMOTE_DIR/full_backup.sql && mysql -u$DB_USER -p$DB_PASS < $REMOTE_DIR/user_grants.sql"

echo "MySQL数据库迁移完成!"

最佳实践建议

1. 在非生产环境测试迁移流程
2. 迁移前进行完整备份
3. 记录迁移过程中的所有操作
4. 考虑使用GTID简化复制配置
5. 对于大型数据库，考虑使用专业工具如Percona XtraBackup
6. 迁移后监控数据库性能至少24小时

希望本指南能帮助您顺利完成MySQL数据库迁移。根据您的具体环境和需求，可能需要调整某些步骤。