MySQL管理工具MySQL Utilities — mysqldiskusage (25)

默北 MySQL1 9,387字数 4931阅读16分26秒阅读模式

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

如果没有指定数据库名称,那么显示所有数据库占用的大小。没有显示单位指标的皆是字节单位。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

该工具通过请求服务来确定数据库目录所在的问题。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

在本地上,该工具是直接从数据目录和innodb家目录获取大小信息的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

磁盘空间使用包含存储引擎文件的综合。对于MyISAM包含 .MYI 和 .MYD 文件,对于InnoDB包含表空间文件。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

如果读取文件系统失败,或者服务不在本地,那么将不能确定文件大小。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

输出格式

指定以何种格式显示输出,通过–format 选项指定:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

  • grid (default)
  • csv
  • tab
  • vertical

不区分大小写,也可以指定任何明确的前缀的有效值。如--format=g 如果--format=grid。如果匹配多个就会出错。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

这里就不再累述的解释了。MySQL Utilities 工具基本上就是这几种格式输出的,前面文章都有解释的,不清楚可以看看前面的文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

如果要关闭 grid, csv 和 tab 输出格式的头部,可以指定 –no-headers 选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

选项

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.

实例

显示所有数据库大小文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

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

显示数据库,日志等大小文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

#  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格式显示文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

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

权限

用户必须要有读取数据目录权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
默北
  • 本文由 发表于 05/03/2015 01:00:23
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/mysql-utilities-mysqldiskusage/
评论  1  访客  1
    • 玩赚乐
      玩赚乐 0

      不错。

    评论已关闭!