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的專欄