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