Home > Database > Mysql Tutorial > 优化Mysql数据表的shell脚本_MySQL

优化Mysql数据表的shell脚本_MySQL

WBOY
Release: 2016-06-01 13:35:43
Original
972 people have browsed it

bitsCN.com 由于公司数据库中的数据量较大,定期对公司的mysql数据库中的数据表进行优化操作(关于optimize的描述如下所示),数据库中有300多张数据表,手工去操作显然不太现实,用脚本来执行效率还是很不错的,脚本如下:

mysql手册中关于 OPTIMIZE 的描述:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用

OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新

利用未使用的空间,并整理数据文件的碎片。

使用方法:sh optimize.sh word

[root@shellec shell]#

#!/bin/sh

time_log=/opt/optimize_time

 

sum=$#

if [ "$sum" -eq 0 ]

then

echo "Error: no parameter chosed"

exit 1

fi

 

for i in $*;do

echo "optimize database $i starting ..."

tables=$(/usr/bin/mysql $i -udevuser -pdevuser -e "show tables" | grep -v "Tables" > /opt/$i)

tablelist=$(cat /opt/$i)

 

echo "optimize database $i starting ................" >> $time_log

echo "$i start at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log

 

for list in $tablelist

do

echo $list

/usr/bin/mysql $i -utaobao -padmin -e "optimize table $list"

done

 

echo "$i end at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log

echo >> $time_log

done

bitsCN.com
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template