当前mysql数据库使用空间_查看mysql数据库空间使用情况

如果想知道mysql 数据库中的每个表占用的空间、表记录的行数的话，可以打开mysql的information_schema数据库。在该库中有一个tables表，这个表主要字段分别是：

table_schema:数据库名

table_name：表名

engine:所使用的存储引擎

table_rows:记录数

data_length:数据大小

index_length:索引大小

1、查看指定数据库实例的大小，比如数据库zabbix2

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| monitor            |

| mysql              |

| test               |

| wikidb             |

| zabbix2            |

+--------------------+

6 rows in set (0.00 sec)

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> select sum(data_length/1024/1024) as data from tables where table_schema='ZABBIX2';

+----------------+

| data           |

+----------------+

| 57459.96875000 |

+----------------+

1 row in set (2.00 sec)

mysql> select round(sum(data_length/1024/1024),2) as data from tables where table_schema='ZABBIX2';

+----------+

| data     |

+----------+

| 57462.97 |

+----------+

1 row in set (1.69 sec)

mysql> select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables where table_schema='ZABBIX2';

+------------+

| data       |

+------------+

| 57463.97mb |

+------------+

1 row in set (1.46 sec)

concat 连接函数

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

2、查看指定数据库的表的大小，如数据库 zabbix2中的history_log表

mysql> select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables

-> where table_schema='ZABBIX2' and table_name='history_log';

+--------+

| data   |

+--------+

| 3.52mb |

+--------+

1 row in set (0.07 sec)

3、查看所有数据的大小

select  concat(round(sum(data_length/1024/1024)2),'MB') as data from tables;

mysql> select sum(data_length/1024/1024) as data from tables;

+----------------+

| data           |

+----------------+

| 57493.09162331 |

+----------------+

1 row in set (5.18 sec)

use information_schema select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables  where table_schema='a0117145552' and table_name='t_kd';