最新消息:其实接手服务器,最最最重要的还是要看下数据库、文件备份情况!!!非常重要!!!

命令行查看mysql数据库、表大小

application shang 1443浏览 0评论

说明:
数据库实例报空间使用达到80%,就需要确定空间具体用了多少。正常数据库实例占用空间主要包括数据大小、索引大小、binlog。

具体:
1. 查看该数据库实例下所有库大小,得到的结果是以MB为单位(除1024为KB,再除1024为MB),下同

> use information_schema
> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 as index_length,sum(data_length+index_length)/1024/1024 as sum from tables;
+--------------------+--------------+--------------+--------------+
| table_schema       | data_length  | index_length | sum          |
+--------------------+--------------+--------------+--------------+
| information_schema | 554.23122311 | 163.24804688 | 717.47926998 |
+--------------------+--------------+--------------+--------------+
1 row in set (0.32 sec)

 
2. 查看该数据库实例下各个库大小

> use information_schema
> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 as index_length,sum(data_length+index_length)/1024/1024 as sum from tables group by table_schema;
+--------------------+--------------+--------------+--------------+
| table_schema       | data_length  | index_length | sum          |
+--------------------+--------------+--------------+--------------+
| coolnull1          |   0.49992847 |   0.54492188 |   1.04485035 |
| coolnull           |  13.79830647 |   0.74121094 |  14.53951740 |
| coolnull2          |   0.00114059 |   0.05078125 |   0.05192184 |
| coolnull3          | 101.22271824 |   1.88183594 | 103.10455418 |
| coolnull4          |  14.25625229 |   2.78710938 |  17.04336166 |
| information_schema |   0.00000000 |   0.00781250 |   0.00781250 |
| mysql              |   0.51842022 |   0.08691406 |   0.60533428 |
| coolnull5          |   0.79851532 |   0.05175781 |   0.85027313 |
| coolnull6          |  16.85469151 |   1.04882813 |  17.90351963 |
| zabbix             | 404.59375000 | 153.34375000 | 557.93750000 |
| zabbix_proxy       |   1.68750000 |   2.70312500 |   4.39062500 |
+--------------------+--------------+--------------+--------------+
11 rows in set (0.44 sec)

 
3. 查看coolnull库各表大小

> use information_schema
> select table_name,data_length/1024/1024 as data_length,index_length/1024/1024 as index_length,(data_length+index_length)/1024/1024 as sum from tables where table_schema='coolnull';
+-----------------------+-------------+--------------+------------+
| table_name            | data_length | index_length | sum        |
+-----------------------+-------------+--------------+------------+
| wp_commentmeta        |  5.99062729 |   0.20312500 | 6.19375229 |
| wp_comments           |  2.07605743 |   0.07031250 | 2.14636993 |
| wp_links              |  0.00066376 |   0.00292969 | 0.00359344 |
| wp_options            |  0.60661697 |   0.01953125 | 0.62614822 |
| wp_postmeta           |  0.18944931 |   0.08496094 | 0.27441025 |
| wp_posts              |  4.82567596 |   0.16503906 | 4.99071503 |
| wp_term_relationships |  0.03670979 |   0.08691406 | 0.12362385 |
| wp_term_taxonomy      |  0.03935623 |   0.03417969 | 0.07353592 |
| wp_terms              |  0.03020859 |   0.06054688 | 0.09075546 |
| wp_usermeta           |  0.00271606 |   0.00976563 | 0.01248169 |
| wp_users              |  0.00022507 |   0.00390625 | 0.00413132 |
+-----------------------+-------------+--------------+------------+
11 rows in set (0.00 sec)

 
附录:
附录1:information_schema.tables表结构

> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |数据库名
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |表名
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |表使用的存储引擎
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |表记录数
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |数据大小
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |索引大小
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(80)         | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

转载请注明:酷喃|coolnull| » 命令行查看mysql数据库、表大小

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址