mysql 批量optimize_mysql批量进行optimize table操作

数据库运行一段时间后，有可能会有磁盘磁片产生，此时我们需要进行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”