Skip to Content

How to Get the Size of a MySQL Database

This quick tutorial will show you how to get the sizes of all MySQL databases on a single connection, or the size of a specific database, in megabytes.

To do so, we'll need to access information_schema which provides information about the MySQL server, access privileges, database metadata, database and table names, column data types, and more.

Get All MySQL Database Sizes

To get the sizes of all databases within a connection, we can run the following query that will return all sizes in megabytes:

SELECT	table_schema AS db_name,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS db_size
FROM information_schema.tables
GROUP BY table_schema;

The result will look something like this:

db_name | db_size
-----------------
test1 | 2.3
test2 | 10.2
test3 | 0.9

Get the Size of One MySQL Database

If you're looking to get the size of only one database, we can expand the above query by specifying a table_schema:

SELECT	table_schema AS db_name,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS db_size
FROM information_schema.tables
WHERE table_schema = '[db-name]'
GROUP BY table_schema;

Where [db-name] is the name of our database. This will provide only one result:

db_name | db_size
-----------------
test1 | 2.3

Conclusion

With these quick examples, you learned how to return the sizes of all your MySQL databases in megabytes, or a single database by specifying a database name.

Posted by: Josh Rowe
Created: June 05, 2022

Comments

There are no comments yet. Start the conversation!

Add A Comment

Comment Etiquette: Wrap code in a <code> and </code>. Please keep comments on-topic, do not post spam, keep the conversation constructive, and be nice to each other.