Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.