MySQL8.0.25占用磁盘空间为0.00-0.99GB的表，全部进行碎片整理SHELL脚本MySQL占用磁盘空间为0.00-0.99GB的表，全部进行碎片整理SHELL脚本
提到碎片整理，做为MySQL DBA并不陌生，但如果一条一条手动执行，就太费劲了，下面写了一个SHELL脚本，可以针对全库中指定表占用空间的大小，来批量对表进行碎片整理：
注意：以下脚本在阿里云 RDS MySQL8.0.25环境下验证通过
[rsc@node100 Fragment_reorganize]$ cat Fragment_reorganize_for_volume_0.00_0.99.sh
#!/bin/bash
DBPATH="/server/shell_scripts/mysql/Fragment_reorganize"
MYUSER="root"
MYPASS="123456"
HOST="rm-abcdefghijkomapbde.mysql.rds.aliyuncs.com"
MYCMD="/usr/bin/mysql -u$MYUSER -p$MYPASS -h$HOST"
[ ! -d "$DBPATH" ]&& mkdir $DBPATH -p
[ -f "$DBPATH/Fragment_reorganize.log" ] || touch $DBPATH/Fragment_reorganize.log
$MYCMD -e "select TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql') AND data_free > 0 and TRUNCATE ( data_length / 1024 / 1024/1024, 2 )+TRUNCATE ( index_length / 1024 / 1024 /1024, 2 ) between 0.00 and 0.99
ORDER BY data_free DESC ;"| grep -v "TABLE_SCHEMA" >/tmp/Fragment_reorganize.log
OLDIFS="$IFS"
IFS=$'\n'
for i in `cat /tmp/Fragment_reorganize.log`
do
str1=$(echo "$i"|awk '{print $1}')
str2=$(echo "$i"|awk '{print $2}')
echo "`date`:Start to alter table '$str1'.'$str2' ' 'engine=InnoDB;" >>$DBPATH/Fragment_reorganize.log
/usr/bin/mysql -u'root' -p'123456' -h'rm-abcdefghijkomapbde.mysql.rds.aliyuncs.com' -e "alter table ${str1}.${str2} engine=InnoDB;" >>$DBPATH/Fragment_reorganize.log
echo "`date`:Finished alter table '$str1'.'$str2' ' 'engine=InnoDB;" >>$DBPATH/Fragment_reorganize.log
sleep 1
echo "`date`:Start analyze table '$str1'.'$str2';">>$DBPATH/Fragment_reorganize.log
/usr/bin/mysql -u'root' -p'123456' -h'rm-abcdefghijkomapbde.mysql.rds.aliyuncs.com' -e "analyze table ${str1}.${str2};" >>$DBPATH/Fragment_reorganize.log
echo "`date`:Finished analyze table '$str1'.'$str2';">>$DBPATH/Fragment_reorganize.log
sleep 1
done
FS="$OLDIFS"
