MySQL定期自動刪除表

壹頁書發表於2014-04-22
MySql
單位8億多條的日誌表，經過自動分表之後，需要自動刪除30天前建立的日誌表。
但是隻是在Master下線這些日誌表，而Slave還需要保持線上，以備查詢。
http://blog.itpub.net/29254281/viewspace-1141985/

由於Master-Slave結構，在Drop表之前，設定@@session.sql_log_bin=0,那麼Drop的行為就沒有記錄到binlog，所以Slave的日誌表就會被保留。

模擬環境如下,

  1. mysql> show tables;
  2. +---------------------------------+
  3. | Tables_in_edmond |
  4. +---------------------------------+
  5. | sod_song_log_2014_1_22_13_18_20 |
  6. | sod_song_log_2014_2_22_13_18_20 |
  7. | sod_song_log_2014_3_22_13_18_20 |
  8. | sod_song_log_2014_4_22_13_18_20 |
  9. +---------------------------------+
  10. 4 rows in set (0.00 sec)
過程如下：

  1. delimiter $$
  2. CREATE procedure drop_table()
  3. BEGIN
  4.             declare t_name varchar(64);
  5.             declare isFinished int default false;
  6.             declare log_table_list cursor for (select table_name from information_schema.tables where table_schema = 'EDMOND' and table_name like 'sod_song_log_%');
  7.             declare continue handler for not found set isFinished=true;
  8.             open log_table_list;
  9.             repeat
  10.                 fetch log_table_list into t_name;
  11.                 if isFinished = false then
  12.                     if datediff(now(),replace(t_name,'sod_song_log_',''))>30 then
  13.                         set @@session.sql_log_bin=0;
  14.                         set @sqltext=concat('drop table ',t_name,';');
  15.                         PREPARE c_tab_stat from @sqltext;
  16.                         execute c_tab_stat;
  17.                         set @@session.sql_log_bin=1;
  18.                     end if;
  19.                 end if;
  20.                 until isFinished
  21.             end repeat;
  22.             close log_table_list;
  23. END $$
  24. delimiter ;
執行過程,結果如下

  1. mysql> call drop_table();
  2. Query OK, 0 rows affected (0.28 sec)

  4. mysql> show tables;
  5. +---------------------------------+
  6. | Tables_in_edmond |
  7. +---------------------------------+
  8. | sod_song_log_2014_4_22_13_18_20 |
  9. +---------------------------------+
  10. 1 row in set (0.00 sec)

  12. mysql> select now() from dual;
  13. +---------------------+
  14. | now() |
  15. +---------------------+
  16. | 2014-04-22 17:58:24 |
  17. +---------------------+
  18. 1 row in set (0.00 sec)
並且binlog中沒有記錄這個Drop的行為。
配合Linux crontab即可實現定期自動刪除表的功能。

一定不要把sql_log_bin設定為global級別,不能犯迷糊

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1147167/，如需轉載，請註明出處，否則將追究法律責任。

相關文章