MySQL get size of all databases in megabytes

Why would you want to get the size of all databases in my MySQL?

Reason for this could be many, in my case I wanted to make sure replication worked as expected, I wanted to see the size of my database be the same on all my MySQL servers. Also I was a little curious how big some of my databases was.

There’s not much to write on this matter, just thought somebody could use this query, I know I am going to use this query many times in the future again.

The query for showing databae sizes in MySQL

It’s one tiny query actually. Just fire up a mysql client, phpmyadmin, mysql in terminal or you can even do this in a script.

The query:

SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Sample output:

+----------------------------+-------------+
| Database name              | Size (MB)   |
+----------------------------+-------------+
| information_schema         |  0.15625000 |
| mysql                      |  1.12139893 |
| performance_schema         |  0.00000000 |
| phpmyadmin                 |  0.04900742 |
+----------------------------+-------------+
4 rows in set (0.01 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *