Linux查看MYSQL数据库容量大小命令

首先了解一下 “information_schema” 这张表：https://www.cnblogs.com/hunttown/p/13272680.html

 

一、查询所有数据库的总大小

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_mb from TABLES;

 

二、查询每个数据库的大小

mysql> use information_schema;
mysql> SELECT table_schema,CONCAT(ROUND(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024,2),'MB') AS total_mb FROM TABLES GROUP BY table_schema;  

 

三、查询各数据库容量

select
    table_schema as '数据库',
    sum(table_rows) as '记录数',
    sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
    sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema

 

四、查询各数据表容量大小

select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;