mysqldiskusage – to see database disk usage by MySQL Workbench

As we know, MySQL workbench is excellent GUI tool for managing MySQL DB servers, creating ERDs (Data Modelling) and for sql development. But with this GUI tool, we are getting some command-line utilities too like mysqldiskusage, mysqlindexcheck, mysqlfailover, mysqldiff, mysqldbcompare etc.,

Here, I’m describing mysqldiskusage utility, which is not only displays mysql db usage but also displays usage of binary log, slow query log, error log, general query log, relay log, and InnoDB tablespaces.

For displaying output, it will give you four options.

  1. grid (default) : Display output in grid or table format like that mysql monitor.
  2. CSV : Display output in comma-separated values format.
  3. tab   : Display output in tab-separated format.
  4. Vertical : Display output in single-column format like G command.

For single DB usage , we can run command like:

 root@nilnandan:~# mysqldiskusage --server=neel@localhost nil
# Source on localhost: ... connected.
# Database totals:
+----------+---------------+
| db_name  |        total  |
+----------+---------------+
| nil      |    251681651  |
+----------+---------------+

Total database disk usage = 25,16,81,651 bytes or 240.00 MB

#...done.
root@nilnandan:~#

If you’ll not mention db name than it will give usage for all DBs. For the logs/tablespace usage, you can give specific options like for binlog/relaylog usage –binlog/–relaylogs , for all logs, –logs  and for innodb tablespace usage, –innodb. There is also one option –all. If you will use it than it will give usage for dbs, all logs, and tablespaces.

root@nilnandan:~# mysqldiskusage --server=neel@localhost nil --all
# Source on localhost: ... connected.
# Database totals:
+----------+---------------+
| db_name  |        total  |
+----------+---------------+
| nil      |    251681651  |
+----------+---------------+

Total database disk usage = 25,16,81,651 bytes or 240.00 MB

# Log information.
# The general_log is turned off on the server.
+-----------------+---------+
| log_name        |   size  |
+-----------------+---------+
| mysql-slow.log  |    182  |
| error.log       | 38,283  |
+-----------------+---------+

Total size of logs = 38,465 bytes or 37.00 KB

# Binary logging is turned off on the server.
# Server is not an active slave - no relay log information.
# InnoDB tablespace information:
+--------------+---------------+
| innodb_file  |         size  |
+--------------+---------------+
| ib_logfile0  |    52,42,880  |
| ib_logfile1  |    52,42,880  |
| ibdata1      | 28,73,09,824  |
+--------------+---------------+

Total size of InnoDB files = 29,77,95,584 bytes or 284.00 MB

InnoDB freespace = 3,67,00,160 bytes or 35.00 MB

#...done.
root@nilnandan:~#

NOTE:  You must provide connection parameters like usr@host in –server option and it must have enough privileges to checkout file system and all required information.

More details are here.

2 thoughts on “mysqldiskusage – to see database disk usage by MySQL Workbench

  1. No magic here, this tool just queries INFORMATION_SCHEMA.TABLES, so in case you have many tables on your server (like hundreds of thousends) it will be expensive in terms of system resources and very slow.

    • I agree with you my dear friend but all people are not using “hundreds of thousands” tables. :) This is helpful for those people who doesn’t have that much big databases. By the way, every utility is not for everyone. Its only for those who actually needed. :)

Leave a Reply