shell脚本查询docker mysql每个库大小，并列出指定大小的表

user=
password=
docker_name=

#开始时间
time=`date +%Y年%m月%d日%H时%M分%S秒`
echo ${time}
#所有数据库大小
select_size_schema_sql="select table_schema as '数据库',sum(table_rows) as '记录数',sum(round(data_length/1024/1024, 0)) as '数据容量(MB)',sum(round(index_length/1024/1024, 0)) as '索引容量(MB)' from information_schema.tables group by table_schema having sum(round(data_length/1024/1024, 0))>=1 order by sum(data_length) desc, sum(index_length) desc;"

count=0
table_count=0
#执行sql
docker exec ${docker_name} mysql -u${user} -p${password} -e "${select_size_schema_sql}"|while read table_schema table_rows data_length index_length;
do
    if [ ${count} -ne 0 ];then
        if [ ${data_length} -gt 300 ];then
            #当前库所有数据表大小
            select_size_table_sql="select table_schema as '数据库',table_name as '表名',table_rows as '记录数',round(data_length/1024/1024, 0) as '数据容量(MB)',round(index_length/1024/1024, 0) as '索引容量(MB)'from information_schema.tables where table_schema='${table_schema}' and table_rows is not null order by data_length desc, index_length desc;"
            echo "${table_schema}库,列出大于20Mb的表："
            docker exec ${docker_name} mysql -u${user} -p${password} -e "${select_size_table_sql}"|while read table_schema table_name rows tableData_length tableIndex_length;
            do
                if [ ${table_count} -ne 0 ];then
                    if [ ${tableData_length} -gt 20 ];then
                        echo "表名："${table_name}",数据大小："${tableData_length}"MB,索引大小："${tableIndex_length}"MB,行数："${rows}"行"
                    fi
                    # docker exec ${docker_name} mysql -u${user} -p${password} -e "select count(1) from ${table_schema}.${table_name} where ${tableTimeName} < date_sub(now(), interval 1 month );"| sed -n '2p'
                    # delete_data_sql="delete from  ${table_schema}.${table_name} where ${tableTimeName} < date_sub(now(), interval 1 month );"
                    # docker exec ${docker_name} mysql -u${user} -p${password} -e "${delete_data_sql}"
                    # optimize table device_data #表名
                fi
                table_count=$[table_count+1]
            done
        fi
    fi
    count=$[count+1]
done