---找出有磁盘碎片的表
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.
这个表明修复的挺好,不需要再检查了