Contents

How to Check MySQL Database Size

06 Mar 2018 4 111  0
How to Check MySQL Database Size
How to Check MySQL Database Size

 

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. 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.

Although this post was made for MySQL, you may still use the same commands for MariaDB.

Checking the size of all MySQL databases

In order to check the size of the MySQL databases, we can use a virtual database called ‘information_schema’ which 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

Leave a Reply