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

参考 https://www.cnblogs.com/hunttown/p/13273006.html

查询所有数据库的总大小

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
+----------+
| data     |
+----------+
| 196.72MB |
+----------+
1 row in set (0.14 sec)
mysql> 
mysql> 
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_mb from TABLES;
+----------+
| data_mb  |
+----------+
| 196.72MB |
+----------+
1 row in set (0.09 sec)

mysql>

查询每个数据库的大小

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;  

查询各数据库容量

mysql> 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
    -> ;
+--------------------+-----------+------------------+------------------+
| 数据库             | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+--------------------+-----------+------------------+------------------+
| cms                |      3655 |             1.41 |             1.48 |
| confluence         |    763665 |            72.39 |           113.47 |
| information_schema |      NULL |             0.10 |             0.00 |
| jira               |      7840 |            12.22 |             4.44 |
| message_manage     |        17 |             0.08 |             0.01 |
| mysql              |     12035 |             4.38 |             0.20 |
| nacos_config       |         4 |             0.12 |             0.16 |
| performance_schema |   1360744 |             0.00 |             0.00 |
| sys                |         6 |             0.01 |             0.00 |
| xxl_job            |        29 |             0.08 |             0.06 |
+--------------------+-----------+------------------+------------------+
14 rows in set (0.09 sec)

mysql>

查询各数据表容量大小

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
where table_schema  = 'benefit_people_all'
order by data_length desc, index_length desc;