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)