MySQL定期自动删除表

此文转自ITPUB上一位大哥， 此为传送门

单位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 frominformation_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)
   
3. 
   
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)
    
11. 
    
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级别,不能犯迷糊