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)
BEGINDECLARE 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_tableSELECT * FROM source_db.source_tableWHERE 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'EXCEPTSELECT 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'EXCEPTSELECT 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語句和腳本可根據實際遷移需求進行調整，建議在測試環境驗證后再進行生產環境遷移。