mysql 磁盘管理工具_MySQL管理工具MySQL Utilities — mysqldiskusage (25)

mysqldiskusage 用于显示一个或多个数据库所使用的磁盘空间大小，也可以显示二进制日志、慢查询日志、错误日志、查询日志、二进制中继日志和innodb表空间所使用的大小。默认情况下，只显示数据库占用磁盘空间大小。

如果没有指定数据库名称，那么显示所有数据库占用的大小。没有显示单位指标的皆是字节单位。

该工具通过请求服务来确定数据库目录所在的问题。

在本地上，该工具是直接从数据目录和innodb家目录获取大小信息的。

磁盘空间使用包含存储引擎文件的综合。对于MyISAM包含 .MYI 和 .MYD 文件，对于InnoDB包含表空间文件。

如果读取文件系统失败，或者服务不在本地，那么将不能确定文件大小。

输出格式

指定以何种格式显示输出，通过–format 选项指定：

grid (default)

csv

tab

vertical

不区分大小写，也可以指定任何明确的前缀的有效值。如--format=g 如果--format=grid。如果匹配多个就会出错。

这里就不再累述的解释了。MySQL Utilities 工具基本上就是这几种格式输出的，前面文章都有解释的，不清楚可以看看前面的文章。

如果要关闭 grid, csv 和 tab 输出格式的头部，可以指定 –no-headers 选项。

选项

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:

[:]@[:][:] or

[:][:] or

path>[].

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

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

MySQLUtilitiesmysqldiskusageversion1.5.3

Licensetype:GPLv2

Usage:mysqldiskusage--server=user:pass@host:port:socketdb1--all

mysqldiskusage-showdiskusagefordatabases

Options:

--versionshowprogram's version number and exit

--help                display a help message and exit

--license             display program'slicenseandexit

--server=SERVERconnectioninformationfortheserverintheform:

[:]@[:][:]or

[:][:]or

path>[].

--ssl-ca=SSL_CAThepathtoafilethatcontainsalistoftrustedSSL

CAs.

--ssl-cert=SSL_CERTThenameoftheSSLcertificatefiletousefor

establishingasecureconnection.

--ssl-key=SSL_KEYThenameoftheSSLkeyfiletouseforestablishinga

secureconnection.

-fFORMAT,--format=FORMAT

displaytheoutputineithergrid(default),tab,csv,

orverticalformat

-h,--no-headersdonotshowcolumnheaders(onlyappliestoformats:

grid,tab,csv).

-b,--binlogincludebinarylogusage

-r,--relaylogincluderelaylogusage

-l,--logsincludegeneralandslowlogusage，查询日志、错误日志和慢查询日志

-i,--innodbincludeInnoDBtablespaceusage，包括共享表空间和独自的表空间

-m,--emptyincludeemptydatabases

-a,--allshowallusageincludingemptydatabases，包括所有库、日志、表空间

-v,--verbosecontrolhowmuchinformationisdisplayed.e.g.,-v=

verbose,-vv=moreverbose,-vvv=debug

-q,--quietturnoffallmessagesforquietexecution.

实例

显示所有数据库大小

# 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

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

+---------------------+------------+

Totaldatabasediskusage=4,344,956bytesor4.14MB

#...done.

显示数据库，日志等大小

# 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.

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|

+---------------------+--------------+------------+-------------+------------+

Totaldatabasediskusage=4,344,956bytesor4.14MB

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

+----------------------------+---------+

Totalsizeoflogs=17,612bytesor17.20KB

# Binary log information:

Currentbinarylogfile=mysql-bin-3306.000002

+------------------------+---------+

|log_file|size|

+------------------------+---------+

|mysql-bin-3306.000001|143|

|mysql-bin-3306.000002|276714|

|mysql-bin-3306.index|48|

+------------------------+---------+

Totalsizeofbinarylogs=276,905bytesor270.42KB

# Server is not an active slave - no relay log information.

# InnoDB tablespace information:

+--------------+-------------+--------------------+-------------------------+

|innodb_file|size|type|specificaton|

+--------------+-------------+--------------------+-------------------------+

|ib_logfile0|50,331,648|logfile||

|ib_logfile1|50,331,648|logfile||

|ibdata1|79,691,776|sharedtablespace|ibdata1:12M:autoextend|

+--------------+-------------+--------------------+-------------------------+

TotalsizeofInnoDBfiles=180,355,072bytesor172.00MB

#...done.

以tab格式显示

# 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.

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_namedb_dir_sizedata_sizemisc_filestotal

test0000

mysql15130878415007356451577145

performance_schema4895430489543489543

ttlsa_com174119937683217411992118031

ttlsa_com_copy12746932768127469160237

Totaldatabasediskusage=4,344,956bytesor4.14MB

# Log information.

# The general_log is turned off on the server.

# The slow_query_log is turned off on the server.

log_namesize

localhost.localdomain.err17612

Totalsizeoflogs=17,612bytesor17.20KB

# Binary log information:

Currentbinarylogfile=mysql-bin-3306.000002

log_filesize

mysql-bin-3306.000001143

mysql-bin-3306.000002276714

mysql-bin-3306.index48

Totalsizeofbinarylogs=276,905bytesor270.42KB

# Server is not an active slave - no relay log information.

# InnoDB tablespace information:

innodb_filesizetypespecificaton

ib_logfile050331648logfile

ib_logfile150331648logfile

ibdata179691776sharedtablespaceibdata1:12M:autoextend

TotalsizeofInnoDBfiles=180,355,072bytesor172.00MB

#...done.

权限

用户必须要有读取数据目录权限。