MySQL automatically deletes tables on a regular basis
Log tables with more than 800 million units need to automatically delete the log tables created 30 days ago after automatic sorting.
But only in Master offline these log tables, and Slave still needs to remain online, in case of query.
Http://blog.itpub.net/29254281/viewspace-1141985/
Because of the Master-Slave structure, set @ @ session.sql_log_bin=0 before the Drop table, so the behavior of Drop is not recorded to binlog, so the log table of Slave is retained.
The simulation environment is as follows
Mysql > show tables
+-+
| | Tables_in_edmond |
+-+
| | sod_song_log_2014_1_22_13_18_20 |
| | sod_song_log_2014_2_22_13_18_20 |
| | sod_song_log_2014_3_22_13_18_20 |
| | sod_song_log_2014_4_22_13_18_20 |
+-+
4 rows in set (0.00 sec) process is as follows:
Delimiter $$
CREATE procedure drop_table ()
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
If datediff (now (), replace (tymename _ camera _ sodname _ song _ song _ log _ cycle) > 30 then
Set @ @ session.sql_log_bin=0
Set @ sqltext=concat ('drop table', tweak _ name _ reference _ exchange')
PREPARE c_tab_stat from @ sqltext
Execute c_tab_stat
Set @ @ session.sql_log_bin=1
End if
End if
Until isFinished
End repeat
Close log_table_list
END $$
Delimiter; execution process, the result is as follows
Mysql > call drop_table ()
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 () from dual
+-+
| | now () |
+-+
| | 2014-04-22 17:58:24 |
+-+
1 row in set (0. 00 sec) and the behavior of this Drop is not recorded in binlog.
With Linux crontab, the function of automatically deleting tables on a regular basis can be realized.
Don't set sql_log_bin to global level, don't get confused.
