1.mysql 查看实例下,每个库的大小
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;
2.mysql 查看具体某个库里面的某个表的大小。
select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema=\"${db}\" AND table_name=\"${table}\"
3.查看mysql 实例下,所有库下的每个表的大小。
#!/bin/bash
>allsize.txt
for n in `cat mysqlbackuplist.txt`
do
echo ========= ${n} ========= >>allsize.txt
for db in `mysql -uroot -ppassword -h$n -e 'show databases;' |awk 'NR>1{print $1}'|egrep -v 'schema|tmp|sys|innodb|mysql|tigasedb'`
do
>${db}size.txt
echo ========= ${db} =========
echo ========= ${db} ========= >>allsize.txt
for table in `mysql -uroot -ppassword -h$n -e "use ${db}; show tables"`
do
selectsql="select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema=\"${db}\" AND table_name=\"${table}\";"
mysql -uroot -ppassword -h$n -e "use information_schema; $selectsql" |awk -v table=$table 'NR>1{print table " " $1}' >>${db}size.txt
done
cat ${db}size.txt|sort -k 2 -rn >> allsize.txt
done
done
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。