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

数据中心有大量的MyISAM log表需要定时清理,定为每周末,10G左右,5-10分钟完成.
event 调用这里就不写了,直接上存储过程....
[sql] view plaincopyprint?
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 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   


摘自 ylqmf的专栏