找出并optimization表

 

---找出有磁盘碎片的表
mysql > select TABLE_NAME,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA not in ( ' information_schema ' , ' mysql ' ) and Data_free > 0 ;
---单独优化表
mysql > optimize table tablename;

 

 

上次说到用 OPTIMIZE TABLE 优化 mysql 表,这次就用到了，一个一个修复太麻烦，就写了一个脚本，挺简单的，省了很多事

具体步骤：

1.输入MySQL的root密码

2.输入MySQL数据文件的全路径

3.优化日志是/tmp/optimize.log

4.剩下的就不用管了:)

# !/bin/sh

# Author: sunss
# Date: 2010-11-11
#
echo " Please input MySQL's root password! "
read pass
echo " Please input your mysql's data directory! "
read data_path
if [ - z $data_path ];then
echo " You didn't do what I'v told you! "
exit 1
fi

if [ ! - d $data_path ];then
echo $data_path " isn't a directory! "
exit 1
fi

cd $data_path
for f1 in $ (ls)
do
if [ - d $f1 ];then
if [ " mysql " != " $f1 " - a " test " != " $f1 " ];then # in
cd $f1
echo " I'm in " $f1
for f2 in $ (ls * .frm)
do
_file_name =$ {f2 % .frm}
echo `date` >> / tmp / optimize.log
mysql - u root - p $pass - e " optimize table " $f1 . $_file_name 2 >& 1 >> / tmp / optimize.log # optimize table
done
cd ..
fi
fi
done

一般优化表后的提示是OK，如下：
Table           Op        Msg_type  Msg_text
dashi.hx_focus  optimize  status    OK      

如果在日志里发现：
2010年 11月 11日 星期四 19:13:18 CST
Table   Op  Msg_type    Msg_text
tool.re_keyword optimize    status  Table is already up to date

但这个表发现提示：Table is already up to date 。

查了下：Table is already up to date means that the storage engine for the table indicated that there was no need to check the table.

这个表明修复的挺好，不需要再检查了

转载于:https://www.cnblogs.com/sunss/archive/2010/11/11/1874693.html