MySQL Utilities (mysqldiskusage)
Reference: http://thinkit.co.jp/story/2014/02/10/4814
mysqldiskusage show disk usage for databases

こちらの、コマンドでディスク容量の確認出来ます。
selectでも算出できますが、実際のファイルサイズが確認出来るので
使いようによっては便利かもしれません。

mysqluc> mysqldiskusage --help
MySQL Utilities mysqldiskusage.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqldiskusage.exe --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>].
  -f FORMAT, --format=FORMAT
                        display the output in either grid (default), tab, csv,
                        or vertical format
  -h, --no-headers      do not show column headers
  -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.

mysqluc>


mysqluc> mysqldiskusage --server=root:password@localhost:3306
# Source on localhost: ... connected.
# Database totals:
+---------------------+-------------+
| db_name             |      total  |
+---------------------+-------------+
| copy_test           | 545,285     |
| mysql               | 1,785,056   |
| performance_schema  | 510,023     |
| sakila              | 30,495,478  |
| sys                 | 0           |
| test                | 545,224     |
| world               | 471,349     |
+---------------------+-------------+

Total database disk usage = 34,597,270 bytes or 32.99 MB

#...done.

mysqluc>


mysqluc> mysqldiskusage --server=root:password@localhost:3306 -a
# Source on localhost: ... connected.
# Database totals:
+---------------------+-------------+
| db_name             |      total  |
+---------------------+-------------+
| copy_test           | 545,285     |
| mysql               | 1,785,056   |
| performance_schema  | 510,023     |
| sakila              | 30,495,478  |
| sys                 | 0           |
| test                | 545,224     |
| world               | 471,349     |
+---------------------+-------------+

Total database disk usage = 34,597,270 bytes or 32.99 MB

# Log information.
# general_log information is not accessible. Check your permissions.
# slow_query_log information is not accessible. Check your permissions.
# log_error information is not accessible. Check your permissions.
# Binary log information:
Current binary log file = MySvr-bin.000020
+--------------------+----------+
| log_file           | size     |
+--------------------+----------+
| MySvr-bin.000001  | 143      |
| MySvr-bin.000002  | 4933143  |
| MySvr-bin.000003  | 729      |
| MySvr-bin.000004  | 2225     |
| MySvr-bin.000005  | 143      |
| MySvr-bin.000006  | 630      |
| MySvr-bin.000007  | 3584334  |
| MySvr-bin.000008  | 168      |
| MySvr-bin.000009  | 143      |
| MySvr-bin.000010  | 143      |
| MySvr-bin.000011  | 168      |
| MySvr-bin.000012  | 143      |
| MySvr-bin.000013  | 143      |
| MySvr-bin.000014  | 342      |
| MySvr-bin.000015  | 3482441  |
| MySvr-bin.000016  | 143      |
| MySvr-bin.000017  | 143      |
| MySvr-bin.000018  | 322      |
| MySvr-bin.000019  | 456      |
| MySvr-bin.000020  | 2443     |
| MySvr-bin.index   | 400      |
+--------------------+----------+

Total size of binary logs = 12,008,945 bytes or 11.45 MB

# Server is not an active slave - no relay log information.
# InnoDB tablespace information:
+--------------+-------------+
| innodb_file  |       size  |
+--------------+-------------+
| ib_logfile0  | 50,331,648  |
| ib_logfile1  | 50,331,648  |
| ibdata1      | 79,691,776  |
+--------------+-------------+

Total size of InnoDB files = 180,355,072 bytes or 172.00 MB

#...done.

mysqluc>

Comments are closed.

Post Navigation