mysql文件碎片整理_MySQL表碎片整理

# cat optimize_table.sh

#!/bin/sh

socket=/tmp/mysql3306.sock

time=`date +"%Y-%m-%d"`

SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'employees'"

optimize_table_name=$(/usr/local/mysql/bin/mysql -S $socket -e "$SQL"|grep -v "TABLE_NAME")

echo "Begin Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>/tmp/optimize_table_$time.log

for table_list in $optimize_table_name

do

echo `date +"%Y-%m-%d %H:%M:%S"` "alter table $table_list engine=innodb ...">>/tmp/optimize_table_$time.log

/usr/local/mysql/bin/mysql -S $socket -e "alter table $table_list engine=innoDB"

done

echo "End Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>>/tmp/optimize_table_$time.log

输出内容

# cat optimize_table_2018-08-30.log

Begin Optimize Table at: 2018-08-30 08:43:21

2018-08-30 08:43:21 alter table employees.departments engine=innodb ...

2018-08-30 08:43:21 alter table employees.dept_emp engine=innodb ...

2018-08-30 08:43:27 alter table employees.dept_manager engine=innodb ...

2018-08-30 08:43:27 alter table employees.employees engine=innodb ...

2018-08-30 08:43:32 alter table employees.salaries engine=innodb ...

2018-08-30 08:44:02 alter table employees.t1 engine=innodb ...

2018-08-30 08:44:17 alter table employees.titles engine=innodb ...

2018-08-30 08:44:28 alter table employees.ttt engine=innodb ...

End Optimize Table at: 2018-08-30 08:44:28