MySQL数据库迁移SQL语句指南

MySQL数据库迁移SQL语句指南

一、基础迁移方法

1. 使用mysqldump进行全量迁移

-- 导出源数据库（在命令行执行）
mysqldump -u [源用户名] -p[源密码] --single-transaction --routines --triggers --events 
--master-data=2 [数据库名] > migration_backup.sql

-- 导入目标数据库（在命令行执行）
mysql -u [目标用户名] -p[目标密码] [目标数据库名] < migration_backup.sql

2. 只迁移表结构

-- 导出表结构
mysqldump -u [用户名] -p[密码] --no-data [数据库名] > schema_only.sql

-- 导入表结构
mysql -u [用户名] -p[密码] [目标数据库] < schema_only.sql

二、大型数据库迁移策略

1. 分表迁移

-- 导出特定表
mysqldump -u [用户名] -p[密码] [数据库名] [表1] [表2] > tables_backup.sql

-- 批量导出所有表（生成导出命令）
SELECT CONCAT('mysqldump -u [用户名] -p[密码] [数据库名] ', table_name, ' > ', table_name, '.sql')
FROM information_schema.tables 
WHERE table_schema = '[数据库名]';

2. 分批迁移大数据表

-- 导出表中部分数据（按ID范围）
mysqldump -u [用户名] -p[密码] [数据库名] [表名] --where="id BETWEEN 1 AND 100000" > table_part1.sql

-- 使用LIMIT分批导出
SELECT * FROM large_table LIMIT 0, 100000 INTO OUTFILE '/tmp/part1.csv';

三、跨版本/跨服务器迁移

1. 使用SELECT INTO OUTFILE

-- 导出数据到文件
SELECT * FROM table_name INTO OUTFILE '/tmp/table_name.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导入数据
LOAD DATA INFILE '/tmp/table_name.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

2. 使用存储过程实现增量迁移

DELIMITER //
CREATE PROCEDURE incremental_migration(IN last_id INT)
BEGIN
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE max_id INT;
    
    SELECT COALESCE(MAX(id), 0) INTO max_id FROM target_db.target_table;
    
    INSERT INTO target_db.target_table
    SELECT * FROM source_db.source_table
    WHERE id > max_id AND id <= max_id + batch_size;
    
    -- 记录迁移位置
    INSERT INTO migration_log (table_name, last_migrated_id, batch_size, migrate_time)
    VALUES ('source_table', max_id + batch_size, batch_size, NOW());
END //
DELIMITER ;

四、数据库迁移验证SQL

1. 数据一致性检查

-- 检查表记录数是否一致
SELECT 
    (SELECT COUNT(*) FROM source_db.table1) AS source_count,
    (SELECT COUNT(*) FROM target_db.table1) AS target_count,
    (SELECT COUNT(*) FROM source_db.table1) - (SELECT COUNT(*) FROM target_db.table1) AS difference;

-- 检查数据校验和
SELECT 
    COUNT(*) AS total_rows,
    SUM(CRC32(CONCAT_WS(',', col1, col2, col3))) AS checksum
FROM source_db.table_name;

-- 与目标库比较
SELECT 
    (SELECT SUM(CRC32(CONCAT_WS(',', col1, col2, col3))) FROM source_db.table_name) AS source_checksum,
    (SELECT SUM(CRC32(CONCAT_WS(',', col1, col2, col3))) FROM target_db.table_name) AS target_checksum;

2. 索引和约束验证

-- 比较表结构
SELECT column_name, column_type, is_nullable, column_default 
FROM information_schema.columns 
WHERE table_schema = 'source_db' AND table_name = 'table_name'

EXCEPT

SELECT column_name, column_type, is_nullable, column_default 
FROM information_schema.columns 
WHERE table_schema = 'target_db' AND table_name = 'table_name';

-- 检查索引差异
SELECT index_name, column_name, non_unique 
FROM information_schema.statistics 
WHERE table_schema = 'source_db' AND table_name = 'table_name'

EXCEPT

SELECT index_name, column_name, non_unique 
FROM information_schema.statistics 
WHERE table_schema = 'target_db' AND table_name = 'table_name';

五、特殊场景迁移方案

1. 迁移存储过程和函数

-- 导出所有存储过程
mysqldump -u [用户名] -p[密码] --routines --no-create-info --no-data 
--no-create-db --skip-opt [数据库名] > routines.sql

-- 单独导出某个存储过程
SHOW CREATE PROCEDURE procedure_name\G

2. 迁移用户和权限

-- 导出用户权限
mysql -u root -p -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') 
FROM mysql.user WHERE user NOT IN ('root','mysql.sys')" | mysql -u root -p > all_grants.sql

-- 导入用户权限
mysql -u root -p < all_grants.sql

六、自动化迁移脚本示例

#!/bin/bash
# MySQL数据库迁移脚本

SOURCE_DB="source_db"
TARGET_DB="target_db"
SOURCE_USER="source_user"
SOURCE_PASS="source_pass"
TARGET_USER="target_user"
TARGET_PASS="target_pass"
BACKUP_DIR="/backup/migration"
DATE=$(date +%Y%m%d)

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

# 1. 导出源数据库
echo "导出源数据库..."
mysqldump -u $SOURCE_USER -p$SOURCE_PASS --single-transaction --routines --triggers --events $SOURCE_DB > $BACKUP_DIR/${SOURCE_DB}_${DATE}.sql

# 2. 创建目标数据库
echo "创建目标数据库..."
mysql -u $TARGET_USER -p$TARGET_PASS -e "CREATE DATABASE IF NOT EXISTS $TARGET_DB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"

# 3. 导入到目标数据库
echo "导入到目标数据库..."
mysql -u $TARGET_USER -p$TARGET_PASS $TARGET_DB < $BACKUP_DIR/${SOURCE_DB}_${DATE}.sql

# 4. 验证迁移
echo "验证迁移结果..."
mysql -u $TARGET_USER -p$TARGET_PASS -e "SELECT COUNT(*) AS tables_migrated FROM information_schema.tables WHERE table_schema = '$TARGET_DB'"

迁移注意事项

1. 版本兼容性：检查MySQL版本差异，特别是5.7到8.0的迁移
2. 字符集设置：确保源和目标数据库使用相同的字符集
3. 外键约束：迁移时暂时禁用外键检查 SET FOREIGN_KEY_CHECKS=0;
4. 大表迁移：考虑使用pt-archiver等工具分批迁移
5. 停机时间：评估业务影响，尽量在低峰期执行迁移

以上SQL语句和脚本可根据实际迁移需求进行调整，建议在测试环境验证后再进行生产环境迁移。