5 / 5 ( 2 votes )

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)