MySQL 定期自動刪除分區表：主庫刪除、從庫保留實戰方案

面對億級日志表自動分表後的歷史數據清理需求：主庫定期刪除 30 天前的分表，從庫保留供查詢分析。本文提供基於存儲過程 + sql_log_bin=0 的完整實施方案，配合 Linux crontab 實現全自動化運維。

一、核心技巧：會話級關閉二進制日志

在 DROP TABLE 前設置 SET @@session.sql_log_bin=0;，該 DDL 不會寫入 binlog，從而不會同步到從庫，實現「主刪從留」。切勿使用 GLOBAL 級別，否則影響所有會話複制。

二、存儲過程完整代碼

DELIMITER $$

CREATE PROCEDURE drop_old_log_tables()
BEGIN
    DECLARE t_name VARCHAR(64);
    DECLARE isFinished INT DEFAULT FALSE;
    DECLARE log_table_list 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 isFinished = TRUE;

    OPEN log_table_list;
    REPEAT
        FETCH log_table_list INTO t_name;
        IF isFinished = FALSE THEN
            -- 從表名解析日期：sod_song_log_2014_1_22_13_18_20
            IF DATEDIFF(NOW(), REPLACE(t_name, 'sod_song_log_', '')) > 30 THEN
                SET @@session.sql_log_bin = 0;
                SET @sqltext = CONCAT('DROP TABLE ', t_name, ';');
                PREPARE c_tab_stat FROM @sqltext;
                EXECUTE c_tab_stat;
                DEALLOCATE PREPARE c_tab_stat;
                SET @@session.sql_log_bin = 1;
            END IF;
        END IF;
    UNTIL isFinished END REPEAT;
    CLOSE log_table_list;
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();
+---------------------+
| NOW()               |
+---------------------+
| 2014-04-22 17:58:24 |
+---------------------+

僅保留 22 日當天的表，歷史 3 張表已刪除。binlog 中無任何 DROP TABLE 記錄，從庫數據完整保留。

四、定時任務部署

編輯 crontab（建議每天低峰期執行）：

0 3 * * * /usr/local/mysql/bin/mysql -uroot -p'password' -e "CALL drop_old_log_tables();" >/dev/null 2>&1

五、關鍵注意事項

• 表名日期格式固定：解析邏輯依賴 REPLACE(t_name, 'prefix_', '') 能直接轉為合法日期字符串。
• 權限控制：執行帳號需擁有 DROP 權限與 SUPER 權限（設置 sql_log_bin）。
• 監控告警：建議在存儲過程中加入日誌記錄表，記錄每次刪除的表名、時間、耗時，便於審計。
• 備份先行：若從庫同樣需要保留更久，可在從庫部署獨立清理策略。