用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>