MySQL is the most popular open-source database in the world, powering small blogs, high-volume websites, business applications and more. MySQL is used by some of the world’s most popular websites such as Facebook, Twitter, Youtube, Wikipedia ..etc. In this blog post, we will show you how to check the size of the MySQL databases and tables trough the MySQL shell. This post is written for MySQL but the exact same commands are also applicable for MariaDB.
Check the size of all MySQL databases
To check the size of MySQL databases and tables we can use the virtual database called information_schema which provides access to database metadata, including the size of the databases and tables.
Enter the MySQL shell as user root by executing the following command:
mysql -u root -p
To print the size of all your databases run the following command:
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 ;
The output should be similar to the following:
+-----------------------+---------------------+
| 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 |
+-----------------------+---------------------+
Check the size of all Tables in a specific MySQL database
To print the size of all tables in a specific MySQL database, same as before we will 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';
The output should be similar to the following:
+-----------------------+------------------+
| 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 |
+-----------------------+------------------+
Check the size of a specific Table in a specific MySQL database
In the last example we will show you how to check the of a specific table size by the database name and the table name, also 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';
The output should be similar to the following:
+-------------+------------------+
| Table Name | Table Size in MB |
+-------------+------------------+
| wp_wfConfig | 3.28 |
+-------------+------------------+
If you use one of our Outsourced Linux Server Support Services, you can simply ask our expert Linux admins to check the size of your MySQL databases and tables for
you. They are available 24×7 and will take care of your request immediately.
PS. If you liked this post, on how to check the size of MySQL databases and tables, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.