找出并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.

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