# 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