萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 定期自動刪除分表:Master 刪除、Slave 保留的 Binlog 閃避術

業務背景

單表 8 億+ 日誌,按時間自動分表(如 sod_song_log_2014_1_22_13_18_20)。需求:
• Master 定期刪除 30 天前分表釋放空間。
• Slave 保留歷史分表供查詢分析。
• 刪除操作不進 Binlog,避免同步到 Slave。

核心技巧:SESSION 級 sql_log_bin=0

SET @@session.sql_log_bin = 0; 使當前會話的 DDL/DML 不寫入 Binlog,**僅影響當前連線**,不影響全域複寫。
切記勿用 GLOBAL 級別,否則全實例停止寫 Binlog,導致主從同步中斷!

存儲過程自動化實現

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;
  REPEAT
    FETCH cur INTO t_name;
    IF NOT done THEN
      -- 從表名解析日期:sod_song_log_2014_1_22_13_18_20 → 2014-1-22
      SET @dt = REPLACE(t_name, 'sod_song_log_', '');
      SET @dt = REPLACE(@dt, '_', '-');  -- 簡化處理,實際需按命名規則精確解析
      IF DATEDIFF(NOW(), @dt) > 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 IF;
  UNTIL done END REPEAT;
  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 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();  -- 2014-04-22 17:58:24
-- 僅保留 22 號表,1~3 號表已刪除,Binlog 無 DROP 記錄

定時任務接入

# /etc/crontab
0 2 * * * mysql -uroot -p'xxx' -e "CALL drop_old_log_tables();" >/dev/null 2>&1

每日凌晨 2 點自動執行,無人值守。

避坑提醒

  1. SESSION 級關閉 Binlog,絕不 GLOBAL。
  2. 表名日期解析需嚴謹匹配命名規範,避免誤刪。
  3. 執行前先 SELECT ... INTO OUTFILE 導出備份,或確認 Slave 已完整同步該分表。
  4. 權限最小化:僅授予 DROP、SELECT information_schema 權限給執行帳號。
copyright © 萬盛學電腦網 all rights reserved