Python 批量遷移 MySQL 同庫大表資料：分批複製刪除腳本實作

Python 批量遷移 MySQL 同庫大表資料：分批複製刪除腳本實作

針對 MySQL 缺乏高效批量歸檔工具的痛點，實作一支 Python 腳本，支援同資料庫內大表按主鍵範圍分批 INSERT ... SELECT 複製到歷史表並刪除原資料，含進度顯示、暫停控制、環境檢查等生產級功能。

核心設計原則

• 前提：表有自增整數主鍵 id
• 策略：按 id 範圍分批 (預設 10,000 筆/批)
• 流程：複製 → 刪除 → 睡眠 (避免主從延遲/鎖衝突) → 下一批
• 安全：執行前檢查源/目標表結構一致、主鍵定義一致

關鍵配置參數

# 資料庫連線
Default_MySQL_Host = '192.168.166.169'
Default_MySQL_Port = 3358
Default_MySQL_User = "mysql_admin"
Default_MySQL_Password = 'mysql@Admin@Pwd'
Default_MySQL_Charset = "utf8"

# 遷移任務
Transfer_Database_Name = "db001"
Transfer_Source_Table_Name = "tb2001"
Transfer_Target_Table_Name = "tb2001_his"
Transfer_Condition = "dt < '2016-10-01'"  # 遷移條件
Transfer_Rows_Per_Batch = 10000           # 批次筆數
Sleep_Second_Per_Batch = 0.5              # 批次間隔秒數

核心邏輯流程

1. check_env()：驗證源/目標表欄位名、順序、主鍵定義完全一致
2. get_id_range()：查詢符合條件的 MIN(id), MAX(id), COUNT(*)
3. 迴圈分批處理：

   # 複製
   INSERT INTO target_table SELECT * FROM source_table 
   WHERE id >= {min_id} AND id < {max_id} AND {condition};
   
   # 刪除 (用目標表 id 反查源表，避免條件不一致)
   DELETE FROM source_table 
   WHERE id IN (SELECT id FROM target_table WHERE id >= {min_id} AND id < {max_id})
     AND id >= {min_id} AND id < {max_id};

4. 顯示進度百分比、剩餘筆數，寫入執行日誌

完整腳本結構

# 依賴: pip install mysqlclient
import MySQLdb, time, sys

# ... 配置區 ...

def get_mysql_connection(): ...
def mysql_exec(sql, params=None): ...
def mysql_exec_many(sql_list): ...  # 事務批次執行
def mysql_query(sql, params=None): ...
def get_column_info_list(table): ...  # DESC table
def check_env(): ...                  # 結構一致性檢查
def get_id_range(): ...               # 取得遷移範圍
def trans_data(min_id, max_id): ...   # 單批複製刪除
def loop_trans_data(): ...            # 主迴圈
def main(): ...

測試資料生成

CREATE TABLE tb2001 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c1 VARCHAR(200), dt DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tb2001_his LIKE tb2001;

-- 插入約 100 萬筆測試資料
INSERT INTO tb2001(c1,dt) SELECT 'abc', DATE_ADD(NOW(), INTERVAL FLOOR(RAND()*20000) HOUR) FROM mysql.user;
INSERT INTO tb2001(c1,dt) SELECT 'abc', DATE_ADD(NOW(), INTERVAL FLOOR(RAND()*20000) HOUR) FROM tb2001;
-- 重複執行上述 INSERT 10+ 次快速擴充

執行結果範例

**************************************
在伺服器 192.168.166.169 上執行腳本: INSERT INTO tb2001_his SELECT * FROM tb2001 WHERE ID>=1 AND ID<10001 AND dt < '2016-10-01' ;
在伺服器 192.168.166.169 上執行腳本: DELETE FROM tb2001 WHERE ID IN (SELECT ID FROM tb2001_his WHERE ID>=1 AND ID<10001) AND ID>=1 AND ID<10001;
執行結果:
影響行數：10000
影響行數：10000
當前遷移進度 10000/1000000, 剩餘 990000, 進度 1.00%
**************************************

優化方向

• 使用 pt-archiver (Percona Toolkit) 替代自製腳本，功能更完善
• 大表加欄位索引支援遷移條件 (dt 欄位建索引)
• 刪除改用 DELETE ... JOIN 或分區表 DROP PARTITION 秒級清理
• 支援斷點續傳：記錄最後成功 max_id 到檔案