用SQL命令查看Mysql数据库大小

用SQL命令查看Mysql数据库大小

---

除了可以直接进入后台查看数据文件大小，可以用SQL命令查看Mysql数据库大小

1、进入information_schema 数据库（存放其他的数据库的信息的数据库）
 mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cacti              |
| centreon           |
| centreon_status    |
| centreon_storage   |
| dumpfile           |
| mysql              |
| syslog             |
| test               |
+--------------------+
9 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>
mysql>
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
28 rows in set (0.00 sec)


2、查询所有数据的大小：
mysql> select concat(round(sum(data_length/1024/1024/1024),2),'GB') as data from tables;
+----------+
| data     |
+----------+
| 110.86GB |
+----------+
1 row in set (1.25 sec)



3、查看指定数据库的大小：
mysql> select concat(round(sum(data_length/1024/1024/1024),2),'GB') as data from tables where table_schema='centreon_status';
+--------+
| data   |
+--------+
| 4.12GB |
+--------+
1 row in set (0.56 sec)




4、查看指定数据库的某个表的大小
比如查看数据库home中 members 表的大小
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='centreon_status' and table_name='nagios_hosts';
+--------+
| data   |
+--------+
| 0.31MB |
+--------+
1 row in set (0.00 sec)

mysql>