資料庫運作一段時間後，有可能會有磁盤磁片産生，此時我們需要進行optimize table操作
# 擷取需要optimize的表：如下為擷取總大小小于80G的表進行操作：
mysql -utroot -S /tmp/mysql_3306.sock -sNe "select concat(table_schema,'.',table_name), table_schema,table_name,ENGINE, sum(data_length/1024/1024) as data_mb , sum(index_length/1024/1024) as index_mb, sum((data_length+index_length)/1024/1024) as all_mb, sum(table_rows) from information_schema.tables where table_schema not in('mysql','performance_schema','information_schema') and engine in ('innodb','myisam') group by table_schema,table_name having all_mb<=80000 order by all_mb asc ;"
将上面擷取到的表名寫入一個檔案，如optimiz_table.tables
寫個腳本來循環跑
#!/bin/bash
set -eux
set -o pipefail
table_file='optimize_table.tables'
log_file='optimize_table.log'
while read line
do
sql="optimize table $line"
echo "`date +"%F %T"`: begin $sql" | tee -a "$log_file"
mysql -uroot -S /tmp/mysql_3306.sock -e "$sql" 2>&1 | tee -a "$log_file"
echo "`date +"%F %T"`: sucess $sql" | tee -a "$log_file"
sleep 1
done < "$table_file"