Contents
Need an expert to help you with
" How to Check MySQL Database Size" ?

Get Help

How to Check MySQL Database Size

How to Check MySQL Database Size
How to Check MySQL Database Size
    How to Check MySQL Database Size is not a daunting task for a new developer. MySQL is one of the most popular open-source databases in the world, often being used by some of the world’s biggest websites such as Facebook, Twitter, YouTube, and Wikipedia among others.

MySQL Uses

The uses of MySQL are vast and include powering small blogs, high-volume websites, business applications, and more. In this guide, we are going to show you how to check the size of the MySQL database and tables by using the MySQL shell.
Check out Our Best VPS Hosting and WordPress hosting for scaling your cloud-based applications and processes.
  Although this post was made for MySQL, you may still use the same commands for MariaDB.

Check MySQL Database Size

For checking the size of the MySQL databases, we can use a virtual database called ‘information_schema’ .  It has access to database metadata such as the size of the databases and tables.
  • Type in the MySQL shell as root user by running the command below.
mysql -u root -p
  • In order to print the size of every database in your MySQL, simply execute the command below.
SELECT

table_schema "Database Name",

ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Database Size in MB"

FROM

information_schema.TABLES

GROUP BY table_schema ;

You should receive an output similar to the one below:


+-----------------------+---------------------+
| Database Name         | Database Size in MB |
+-----------------------+---------------------+
| admin_110             |              251.28 |
| admin_120             |               18.99 |
| admin_25              |               11.86 |
| admin_562             |               28.45 |
| admin_877             |                4.20 |
| information_schema    |                0.14 |
| mysql                 |                0.69 |
| performance_schema    |                0.00 |
| user_174              |               23.72 |
| user_379              |                0.26 |
| user_604              |                0.64 |
| user_625              |                1.22 |
| user_820              |                0.73 |
| user_854              |                2.25 |
| user_901              |                1.10 |
| user_961              |                1.60 |
+-----------------------+---------------------+

Checking the size of all Tables in a specific MySQL database

In order to print the size of every table in a particular MySQL database, we are going to use the information from the virtual database ‘information_schema’.
SELECT

table_name as "Table Name",

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"

FROM

information_schema.TABLES

where

table_schema = 'admin_562';

You should receive an output similar to the one below:



+-----------------------+------------------+
| Table Name            | Table Size in MB |
+-----------------------+------------------+
| wp_commentmeta        |             0.05 |
| wp_comments           |            12.42 |
| wp_links              |             0.03 |
| wp_options            |             1.98 |
| wp_postmeta           |             0.19 |
| wp_posts              |             1.75 |
| wp_term_relationships |             0.06 |
| wp_term_taxonomy      |             0.05 |
| wp_termmeta           |             0.05 |
| wp_terms              |             0.05 |
| wp_usermeta           |             0.05 |
| wp_users              |             0.06 |
| wp_wfBadLeechers      |             0.02 |
| wp_wfBlockedIPLog     |             0.06 |
| wp_wfBlocks           |             0.03 |
| wp_wfBlocksAdv        |             0.02 |
| wp_wfConfig           |             3.28 |
| wp_wfCrawlers         |             0.02 |
| wp_wfFileMods         |             1.52 |
| wp_wfHits             |             2.20 |
| wp_wfHoover           |             0.03 |
| wp_wfIssues           |             2.52 |
| wp_wfKnownFileList    |             0.20 |
| wp_wfLeechers         |             0.05 |
| wp_wfLockedOut        |             0.02 |
| wp_wfLocs             |             0.02 |
| wp_wfLogins           |             0.08 |
| wp_wfNet404s          |             0.03 |
| wp_wfNotifications    |             0.02 |
| wp_wfPendingIssues    |             0.31 |
| wp_wfReverseCache     |             0.02 |
| wp_wfSNIPCache        |             0.06 |
| wp_wfScanners         |             0.02 |
| wp_wfStatus           |             1.17 |
| wp_wfThrottleLog      |             0.03 |
| wp_wfVulnScanners     |             0.02 |
+-----------------------+------------------+

Checking the size of a specific Table in a specific MySQL database

Finally, in order to check a particular table size, we are going to use its database name, the table name, and by querying the virtual database ‘information_schema’:
SELECT

table_name as "Table Name",

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"

FROM

information_schema.TABLES

where

table_schema = 'admin_877' and table_name = 'wp_wfConfig';
You should receive an output similar to the one below:
 +-------------+------------------+
| Table Name  | Table Size in MB |
+-------------+------------------+
| wp_wfConfig |             3.28 |
+-------------+------------------+
0 Comments
Add a comment
Login for Comment