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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值