MySQL automatically deletes tables on a regular basis

More than 800 million units of the log table, after automatic table, you need to automatically delete 30 days before the creation of the log table.

But only in master offline these log tables, and slave still need to stay online for query.

Because of the master-slave structure, the @ @session. Sql_log_bin=0 is set before the drop table, so the behavior of the drop is not recorded to the Binlog, so the slave log table 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)

The 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 tabl E_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 (t_name, ' sod_song_log_ ', ')) >30 Then

SET @ @session. sql_log_bin=0;

Set @sqltext =concat (' drop table ', T_name, '; ');

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;

Implementation process, the results are 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 the Binlog.

With the Linux crontab can be implemented on a regular basis to automatically delete the table function.

Be sure not to set the Sql_log_bin to global level and not be confused.