mysqldiskusage 用于显示一个或多个数据库所使用的磁盘空间大小，也可以显示二进制日志、慢查询日志、错误日志、查询日志、二进制中继日志和innodb表空间所使用的大小。默认情况下，只显示数据库占用磁盘空间大小。
如果没有指定数据库名称，那么显示所有数据库占用的大小。没有显示单位指标的皆是字节单位。
该工具通过请求服务来确定数据库目录所在的问题。
在本地上，该工具是直接从数据目录和innodb家目录获取大小信息的。
磁盘空间使用包含存储引擎文件的综合。对于MyISAM包含 .MYI 和 .MYD 文件，对于InnoDB包含表空间文件。
如果读取文件系统失败，或者服务不在本地，那么将不能确定文件大小。
输出格式
指定以何种格式显示输出，通过–format 选项指定：
- grid (default)
- csv
- tab
- vertical
不区分大小写，也可以指定任何明确的前缀的有效值。如–format=g 如果–format=grid。如果匹配多个就会出错。
这里就不再累述的解释了。MySQL Utilities 工具基本上就是这几种格式输出的，前面文章都有解释的，不清楚可以看看前面的文章。
如果要关闭 grid, csv 和 tab 输出格式的头部，可以指定 –no-headers 选项。
选项
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
MySQL Utilities mysqldiskusage version 1.5.3
License type: GPLv2
Usage: mysqldiskusage —server=user:pass@host:port:socket db1 —all
mysqldiskusage – show disk usage for databases
Options:
—version show program‘s version number and exit
–help display a help message and exit
–license display program’s license and exit
—server=SERVER connection information for the server in the form:
<user>[:<password>]@<host>[:<port>][:<socket>] or
<login–path>[:<port>][:<socket>] or <config–
path>[<[group]>].
—ssl–ca=SSL_CA The path to a file that contains a list of trusted SSL
CAs.
—ssl–cert=SSL_CERT The name of the SSL certificate file to use for
establishing a secure connection.
—ssl–key=SSL_KEY The name of the SSL key file to use for establishing a
secure connection.
–f FORMAT, —format=FORMAT
display the output in either grid (default), tab, csv,
or vertical format
–h, —no–headers do not show column headers (only applies to formats:
grid, tab, csv).
–b, —binlog include binary log usage
–r, —relaylog include relay log usage
–l, —logs include general and slow log usage，查询日志、错误日志和慢查询日志
–i, —innodb include InnoDB tablespace usage，包括共享表空间和独自的表空间
–m, —empty include empty databases
–a, —all show all usage including empty databases，包括所有库、日志、表空间
–v, —verbose control how much information is displayed. e.g., –v =
verbose, –vv = more verbose, –vvv = debug
–q, —quiet turn off all messages for quiet execution.
|
实例
显示所有数据库大小
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
# mysqldiskusage –server=instance_3306
# Source on localhost: … connected.
# Database totals:
+——————————–+——————+
| db_name | total |
+——————————–+——————+
| mysql | 1,577,145 |
| performance_schema | 489,543 |
| ttlsa_com | 2,118,031 |
| ttlsa_com_copy | 160,237 |
+——————————–+——————+
Total database disk usage = 4,344,956 bytes or 4.14 MB
#…done.
|
显示数据库，日志等大小
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
# mysqldiskusage –server=instance_3306 –format=g -a -vvv
# Source on localhost: … connected.
# Database totals:
+——————————–+———————+——————+——————–+——————+
| db_name | db_dir_size | data_size | misc_files | total |
+——————————–+———————+——————+——————–+——————+
| test | 127,469 | 32,768 | 127,469 | 160,237 |
| mysql | 1,513,087 | 841,500 | 735,645 | 1,577,145 |
| performance_schema | 489,543 | 0 | 489,543 | 489,543 |
| ttlsa_com | 1,741,199 | 376,832 | 1,741,199 | 2,118,031 |
| ttlsa_com_copy | 127,469 | 32,768 | 127,469 | 160,237 |
+——————————–+———————+——————+——————–+——————+
Total database disk usage = 4,344,956 bytes or 4.14 MB
# Log information.
# The general_log is turned off on the server.
# The slow_query_log is turned off on the server.
+——————————————+————–+
| log_name | size |
+——————————————+————–+
| localhost.localdomain.err | 17,612 |
+——————————————+————–+
Total size of logs = 17,612 bytes or 17.20 KB
# Binary log information:
Current binary log file = mysql–bin–3306.000002
+————————————+————–+
| log_file | size |
+————————————+————–+
| mysql–bin–3306.000001 | 143 |
| mysql–bin–3306.000002 | 276714 |
| mysql–bin–3306.index | 48 |
+————————————+————–+
Total size of binary logs = 276,905 bytes or 270.42 KB
# Server is not an active slave – no relay log information.
# InnoDB tablespace information:
+———————+——————–+——————————+————————————–+
| innodb_file | size | type | specificaton |
+———————+——————–+——————————+————————————–+
| ib_logfile0 | 50,331,648 | log file | |
| ib_logfile1 | 50,331,648 | log file | |
| ibdata1 | 79,691,776 | shared tablespace | ibdata1:12M:autoextend |
+———————+——————–+——————————+————————————–+
Total size of InnoDB files = 180,355,072 bytes or 172.00 MB
#…done.
|
以tab格式显示
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
# mysqldiskusage –server=instance_3306 –format=t -a -vvv
# Source on localhost: … connected.
# Database totals:
db_name db_dir_size data_size misc_files total
test 0 0 0 0
mysql 1513087 841500 735645 1577145
performance_schema 489543 0 489543 489543
ttlsa_com 1741199 376832 1741199 2118031
ttlsa_com_copy 127469 32768 127469 160237
Total database disk usage = 4,344,956 bytes or 4.14 MB
# Log information.
# The general_log is turned off on the server.
# The slow_query_log is turned off on the server.
log_name size
localhost.localdomain.err 17612
Total size of logs = 17,612 bytes or 17.20 KB
# Binary log information:
Current binary log file = mysql–bin–3306.000002
log_file size
mysql–bin–3306.000001 143
mysql–bin–3306.000002 276714
mysql–bin–3306.index 48
Total size of binary logs = 276,905 bytes or 270.42 KB
# Server is not an active slave – no relay log information.
# InnoDB tablespace information:
innodb_file size type specificaton
ib_logfile0 50331648 log file
ib_logfile1 50331648 log file
ibdata1 79691776 shared tablespace ibdata1:12M:autoextend
Total size of InnoDB files = 180,355,072 bytes or 172.00 MB
#…done.
|
权限
用户必须要有读取数据目录权限。
文章转载来自：ttlsa.com
