單表 8 億+ 日誌,按時間自動分表(如 sod_song_log_2014_1_22_13_18_20)。需求:
• Master 定期刪除 30 天前分表釋放空間。
• Slave 保留歷史分表供查詢分析。
• 刪除操作不進 Binlog,避免同步到 Slave。
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 點自動執行,無人值守。
SELECT ... INTO OUTFILE 導出備份,或確認 Slave 已完整同步該分表。DROP、SELECT information_schema 權限給執行帳號。