Tag Archives: MySQL

MySQL get size of all databases in megabytes

Leave a reply

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)

MySQL data on secondary hard disk

9 Replies

Having your MySQL data on a separate partition is important, why?

Having your MySQL data on a separate partition is important because when your root partition fills up and no space is left, your system won’t be able to boot and might crash when running, you then have to boot from a recovery disk to remove files before you can boot on your normal system again.
This is critical for servers that you can’t allow to be down for an hour or two!

The solution

The solution is easy, you just move your MySQL data to a separate partition, and this problem won’t occur any longer, the only thing that happens when you run out of space, is that the MySQL can’t write any new data to the database, which is better than the entire system crashing and being unable to boot.
In this guide i will show you how to make the changes needed to get this problem resolved.
Continue reading →