mysql5.1 mysiam_MySQL 数据库清理MyISAM Innodb表(支持MySQL5.1.6以上的版本)

数据中心有大量的 MyISAM log表需要定时清理,定为每周末,10G左右,5-10分钟完成.

event 调用这里就不写了,直接上存储过程....

DROP PROCEDURE IF EXISTS proc_optable;

tudou@gyyx

2012-02-22

CREATE PROCEDURE proc_optable()

BEGIN

DECLARE dono INT DEFAULT 0;

DECLARE o VARCHAR(2000);

DECLARE optable CURSOR

FOR

SELECT CONCAT('OPTIMIZE LOCAL TABLE `',TABLE_SCHEMA,'`.`',TABLE_NAME,'`;') from information_schema.`TABLES` WHERE TABLE_TYPE='BASE TABLE' AND ENGINE IN ('MyISAM','InnoDB','BDB');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET dono=1;

SET dono=0;

OPEN optable;

REPEAT

FETCH optable INTO o;

set @o=o;

PREPARE MSQL FROM @o;

EXECUTE MSQL;

UNTIL dono END REPEAT;

CLOSE optable;

END

分享到：

2012-02-22 10:59

浏览 536

评论