Beta Draft: 2016-08-22
This utility displays disk space usage for one or more databases. The utility optionally displays disk usage for the binary log, slow query log, error log, general query log, relay log, and InnoDB tablespaces. The default is to only show database disk usage.
If the command-line lists no databases, the utility shows the disk space usage for all databases.
Sizes displayed without a unit indicator (such as MB) are in bytes.
The utility determines the location of the data directory by requesting it from the server. For a local server, the utility obtains size information directly from files in the data directory and InnoDB home directory. In this case, you must have file system access to read those directories. Disk space usage shown includes the sum of all storage engine- specific files such as the .MYI and .MYD files for MyISAM and the tablespace files for InnoDB.
If the file system read fails, or if the server is not local, the utility cannot determine exact file sizes. It is limited to information that can be obtained from the system tables, which therefore should be considered an estimate. For information read from the server, the account used to connect to the server must have the appropriate permissions to read any objects accessed during the operation.
If information requested requires file system access but is not available that way, the utility prints a message that the information is not accessible. This occurs, for example, if you request log usage but the server is not local and the log files cannot be examined directly.
To specify how to display output, use one of the following values
Display output in grid or table format like that of the mysql client command-line tool.
Display output in comma-separated values format.
Display output in tab-separated format.
Display output in single-column format like that of the
\Gcommand for the mysql client command-line tool.
To turn off the headers for
tab display format, specify the
mysqldiskusage accepts the following command-line options:
Display a help message and exit.
Display license information and exit.
Display all disk usage. This includes usage for databases, logs, and InnoDB tablespaces.
Display binary log usage.
Include empty databases.
Specify the output display format. Permitted format values are grid, csv, tab, and vertical. The default is grid.
Display InnoDB tablespace usage. This includes information about the shared InnoDB tablespace as well as .idb files for InnoDB tables with their own tablespace.
Display general query log, error log, and slow query log usage.
Do not display column headers. This option applies only for grid, csv, and tab output.
Suppress informational messages.
Display relay log usage.
Connection information for the server.
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.
Use login-paths from your
.mylogin.cnffile (encrypted, not visible). Example : <login-path>[:<port>][:<socket>]
Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : <configuration-file-path>[:<section>]
Specify the data on the command-line (unencrypted, visible). Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).
Specify how much information to display. Use this option multiple times to increase the amount of information. For example,
-vv= more verbose,
Display version information and exit.
the permitted values are not case sensitive. In addition, values
may be specified as any unambiguous prefix of a valid value. For
specifies the grid format. An error occurs if a prefix matches
more than one valid value.
You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges for all objects accessed during the operation.
The path to the MySQL client tools should be included in the
PATH environment variable in order to use the
authentication mechanism with login-paths. This will allow the
utility to use the my_print_defaults tools
which is required to read the login-path values from the login
configuration file (
To show only the disk space usage for the
in grid format (the default), use this command:
mysqldiskusage --server=root@localhost employees test# Source on localhost: ... connected. # Database totals: +------------+--------------+ | db_name | total | +------------+--------------+ | employees | 205,979,648 | | test | 4,096 | +------------+--------------+ Total database disk usage = 205,983,744 bytes or 196.00 MB #...done.
To see all disk usage for the server in CSV format, use this command:
mysqldiskusage --server=root@localhost --format=csv -a -vv# Source on localhost: ... connected. # Database totals: db_name,db_dir_size,data_size,misc_files,total test1,0,0,0,0 db3,0,0,0,0 db2,0,0,0,0 db1,0,0,0,0 backup_test,19410,1117,18293,19410 employees,242519463,205979648,242519463,448499111 mysql,867211,657669,191720,849389 t1,9849,1024,8825,9849 test,56162,4096,52066,56162 util_test_a,19625,2048,17577,19625 util_test_b,17347,0,17347,17347 util_test_c,19623,2048,17575,19623 Total database disk usage = 449,490,516 bytes or 428.00 MB # Log information. # The general_log is turned off on the server. # The slow_query_log is turned off on the server. # binary log information: Current binary log file = ./mysql-bin.000076 log_file,size /data/mysql-bin.000076,125 /data/mysql-bin.000077,125 /data/mysql-bin.000078,556 /data/mysql-bin.000079,168398223 /data/mysql-bin.index,76 Total size of binary logs = 168,399,105 bytes or 160.00 MB # Server is not an active slave - no relay log information. # InnoDB tablespace information: InnoDB_file,size,type,specification /data/ib_logfile0,5242880,log file, /data/ib_logfile1,5242880,log file, /data/ibdata1,220200960,shared tablespace,ibdata1:210M /data/ibdata2,10485760,shared tablespace,ibdata2:10M:autoextend /data/employees/departments.ibd,114688,file tablespace, /data/employees/dept_emp.ibd,30408704,file tablespace, /data/employees/dept_manager.ibd,131072,file tablespace, /data/employees/employees.ibd,23068672,file tablespace, /data/employees/salaries.ibd,146800640,file tablespace, /data/employees/titles.ibd,41943040,file tablespace, Total size of InnoDB files = 494,125,056 bytes or 471.00 MB #...done.
The user must have permissions to read the data directory or use an administrator, super user (sudo), or an account with elevated privileges to obtain access to the data directory.