MySQL regularly deletes tables.

MySQL regularly deletes tables.

For a log table with more than 0.8 billion entries, the log table created 30 days ago must be automatically deleted after automatic table sharding.

However, these log tables are only removed from the Master, and Slave must be online for query.

Because of the Master-Slave structure, set @ session. SQL _log_bin = 0 before the Drop table, the Drop action is not recorded in the binlog, so the Slave log table will be retained.

The simulated 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 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 (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;

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 () |

+ --------------------- +

| 17:58:24 |

+ --------------------- +

1 row in set (0.00 sec)

The Drop action is not recorded in the binlog.

With Linux crontab, You can automatically Delete tables on a regular basis.

Do not set SQL _log_bin to the global level.