MySQL 定期自動刪除分表：主庫刪除、備庫保留的 binlog 過濾技巧

業務場景

• 單表 8 億+ 日誌，按時間自動分表 (如 sod_song_log_2014_1_22_13_18_20)
• 需自動刪除 30 天前的分表
• 核心要求：主庫刪除釋放空間，備庫**保留**供歷史查詢

核心技巧：SESSION 級 sql_log_bin=0

在當前會話設置 SET @@session.sql_log_bin=0，後續 DDL **不寫入 Binlog**，Slave 不會同步執行，從而實現「主刪備留」。

⚠️ 嚴禁 使用 SET GLOBAL sql_log_bin=0，會導致主庫所有寫入停止同步，引發主從數據不一致災難！

存儲過程實現

DELIMITER $$

CREATE PROCEDURE drop_old_log_tables()
BEGIN
  DECLARE t_name VARCHAR(64);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR
    SELECT TABLE_NAME 
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'EDMOND' 
      AND TABLE_NAME LIKE 'sod_song_log_%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO t_name;
    IF done THEN LEAVE read_loop; END IF;

    -- 從表名解析日期：sod_song_log_2014_1_22_13_18_20
    -- 簡化假設：表名格式固定，REPLACE 移除前綴後直接 DATEDIFF
    IF DATEDIFF(NOW(), REPLACE(t_name, 'sod_song_log_', '')) > 30 THEN
      SET @@session.sql_log_bin = 0;
      SET @sql = CONCAT('DROP TABLE ', t_name);
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      SET @@session.sql_log_bin = 1;
    END IF;
  END LOOP;
  CLOSE cur;
END$$

DELIMITER ;

執行驗證

mysql> CALL drop_old_log_tables();
Query OK, 0 rows affected (0.28 sec)

mysql> SHOW TABLES;
+---------------------------------+
| Tables_in_edmond                |
+---------------------------------+
| sod_song_log_2014_4_22_13_18_20 |  -- 僅保留最近表
+---------------------------------+

mysql> SELECT NOW();
+---------------------+
| 2014-04-22 17:58:24 |
+---------------------+

Binlog 中無任何 DROP TABLE 記錄，備庫分表完整保留。

生產化部署

1. 將存儲過程部署至主庫
2. 配置 Linux crontab 定時執行：

   # 每天凌晨 2 點執行
   0 2 * * * mysql -h -u -p -e "CALL drop_old_log_tables();"

3. 監控告警：檢查 Job 返回碼、執行耗時、備庫表數量未變

進階優化建議

• 表名日期解析改用正則或固定格式 YYYY_MM_DD，避免字符串替換錯誤
• 大表 DROP 可能引發自適應哈希索引刷新阻塞，考慮 RENAME TABLE t TO t_to_drop; DROP TABLE t_to_drop; 降低鎖持有時間
• 配合 innodb_file_per_table=ON 確保空間真正釋放給 OS
• 備庫若也需定期清理，可設置不同保留天數 (如主庫 30 天、備庫 90 天)