MySQL 批量導出、TRUNCATE 與 LOAD DATA 還原自動化 SQL 生成腳本

MySQL 批量導出、TRUNCATE 與 LOAD DATA 還原自動化 SQL 生成腳本

針對指定庫、指定表清單，自動生成「導出 TSV → TRUNCATE 清空 → LOAD DATA 還原」三步驟 SQL，適用於表結構調整、碎片整理、跨例項遷移等場景。

核心 SQL：從 information_schema 動態生成執行腳本

SELECT CONCAT(
  'SELECT * FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` INTO OUTFILE ',
  '\'/work/opdir/repair/', TABLE_SCHEMA, '.', TABLE_NAME, '.tsv\'; ',
  'TRUNCATE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`; ',
  'LOAD DATA INFILE ',
  '\'/work/opdir/repair/', TABLE_SCHEMA, '.', TABLE_NAME, '.tsv\' ',
  'INTO TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
  'CHARACTER SET utf8mb4;'
) AS ExeSql
FROM information_schema.TABLES t
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA IN ('BCReport_Sync')  -- 目標庫，可擴展
  AND t.TABLE_NAME NOT LIKE '%Log%'
  AND t.TABLE_NAME NOT LIKE '%bak%'
  AND t.TABLE_NAME NOT LIKE '%History%'
  AND t.TABLE_NAME NOT LIKE '%Tmp_%'
  AND t.TABLE_NAME NOT LIKE '%Monitor%'
  AND t.TABLE_NAME NOT LIKE '%backup%'
  AND t.TABLE_NAME NOT LIKE '%_BK'
  AND t.TABLE_NAME NOT LIKE '%_Temp'
  AND t.TABLE_NAME NOT LIKE '%2013%'
  AND t.TABLE_NAME NOT LIKE '%2012%'
  AND t.TABLE_NAME NOT LIKE '%2014%'
  AND CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) IN (
    'AdConversions.DataInbound_TaskSchedule',
    'AdConversions.DeviceForComparison',
    'AdConversions.Fact_Realtime_Conversion',
    'AdDataCenter.Ad_Account_Mirror',
    'AdDataCenter.Ad_Account',
    'AdDataCenter.Ad_AdGroup_AdAction_Mobisage',
    'AdDataCenter.Ad_AdGroup_BidPrice',
    'AdDataCenter.Ad_AdGroup_Property',
    'AdDataCenter.Ad_AdGroup_WeekTimePeriod',
    'AdDataCenter.Ad_AdGroup',
    'AdDataCenter.Ad_Campaign_BudgetList',
    'AdDataCenter.Ad_Campaign_DockingProduct_Mapping',
    'AdDataCenter.Ad_Campaign_GeoTargeting',
    'AdDataCenter.Ad_Campaign',
    'AdDataCenter.Ad_Creative_Additional',
    'AdDataCenter.Ad_Creative_ImageAd',
    'AdDataCenter.Ad_Creative_Offerwall',
    'AdDataCenter.Ad_Creative_Template',
    'AdDataCenter.Ad_Creative',
    'AdDataCenter.Ad_Targeting_Mobisage_AppIncluded',
    'AdDataCenter.Ad_Targeting_Mobisage',
    'AdDataCenter.Ad_Targeting',
    'AdDataCenter.AdCreativeTemplate_SlotSize_Mapping',
    'AdDataCenter.AdSync_AdCreative',
    'AdDataCenter.App_MediaType_Mapping',
    'AdDataCenter.AppOfferwall_Category_Mapping',
    'AdDataCenter.Bill_AdAccount_Recharge_Daily',
    'AdDataCenter.Bill_AdAccount_Spend_Daily',
    'AdDataCenter.Dim_App',
    'AdDataCenter.Dim_Slot',
    'AdDataCenter.Docking_Product',
    'AdDataCenter.Offerwall_AppBuckleScale_Mapping',
    'AdDataCenter.Plugin_AdGroup',
    'AdDataCenter.Slot_Affiliate_Business_Ratio',
    'AdSync.Ad_Campaign_RealtimeControl',
    'BCSourceData_Sync.Data_TaskSchedule',
    'BCSourceData_Sync.Source_Fact_AccountHourlySpend',
    'BCSourceData_Sync.Source_Fact_AdCreative_App_Usage_ThirdPartPlatform'
  );

關鍵點解析

1. 過濾條件：排除日誌、備份、歷史、臨時、按年份分區等非核心表，僅處理白名單內表。
2. 檔案路徑：/work/opdir/repair/ 需提前建立，且 MySQL 進程擁有寫入權限（受 secure_file_priv 限制）。
3. 字符集：導入時顯式指定 CHARACTER SET utf8mb4，防止編碼錯位。
4. TRUNCATE vs DELETE：TRUNCATE 重建表、釋放空間、重置自增 ID，速度極快；但不可回滾、不觸發 Trigger。
5. 執行方式：將查詢結果導出為 SQL 檔，再 SOURCE 執行，或用 Shell 迴圈逐條執行。

前置檢查清單

• [ ] secure_file_priv 允許目標目錄或設為空（需重啟）。
• [ ] 目標目錄磁碟空間 ≥ 預估導出總大小 1.5 倍。
• [ ] 業務低峰期執行，或配合 FLUSH TABLES WITH READ LOCK 確保一致性（會阻塞寫入）。
• [ ] 核心表建議先做 mysqldump 邏輯備份，雙重保險。

擴展建議

• 大表 (>10GB) 改用 pt-archiver 或 mysqldump --tab 並行導出。
• 需保留自增值時，導出前記錄 SHOW TABLE STATUS 的 Auto_increment，導入後 ALTER TABLE t AUTO_INCREMENT = xxx。
• 整合到 CI/CD 流水線，標準化「結構變更 → 數據重組 → 驗證」流程。