How to determine the size of a MySQL database via command-line

Introduction

The cPanel interface provides you with a feature in which the disk usage for each database is reported. However, many administrators may prefer to use the command-line, or sometimes you may need to check manually if you are troubleshooting the values reported in the interface.

Procedure

Many administrators may often perform a disk usage check manually via the command line using tools provided by the operating system.

Example:

du -sh /var/lib/mysql/DATABASE_NAME

However, this is not the most accurate way to determine disk usage. Additionally, depending on your database structure and tablespace history, the usage may be off my larger margins.

The best way to perform a disk usage operation is by using the information_schema to query the database directly. To do so, you can use the following command. Be sure to replace $DATABASE with the actual name of your database.

mysql -e 'SELECT table_schema "Database (table_schema)", sum((data_length+index_length)/1024/1024/1024) AS "DB size in GB" FROM information_schema.tables WHERE table_schema = "$DATABASE";'

This should provide you with an accurate representation of the disk usage utilized by MySQL.

Do note that when using InnoDB, the tablespace files (.idb) will sometimes use additional disk usage that is not counted in by MySQL. This often means that you may need to optimize your tables to reclaim unused disk space in the system by the IDB files.

After doing substantial insert, update, or delete operations on an InnoDB table 
that has its own .ibd file because it was created with the innodb_file_per_table 
option enabled. The table and indexes are reorganized, and disk space can be reclaimed 
for use by the operating system.

MySQL Documentation: 13.7.2.4 OPTIMIZE TABLE Statement