Determine MySQL Database Size

When you work with fairly large databases, it’s often useful to know the size of one of them. For example, should you wish to take a full dump – you need to know if you have enough space on the drive you wish to keep it, and, in order to do that, you’ll need to know the size of your database. Or, you may just want to know how much of your drive is being used up by the database so you’ll know if it should grow quickly, that you’ll have enough space.

MySQL database size in a one-liner?

I recently needed to know the size of a fairly large MySQL database. Some basic googling helped me get most of the way there, but in several of the quick one-liners there were typos or basic errors. After a little bit of playing around, I found the following one-liner helped me get to the answer;

SELECT table_schema "databse_name", sum( data_length + index_length ) / 1024 / 1024 "DB Size In MB" FROM information_schema.TABLES GROUP BY table_schema;

What does that do?

It’s fairly straight forward and it basically tells you just where and what things are stored in the information_schema database (which, by the way is worth exploring – it’s quite interesting what else is stored in there). Just make sure you change “database_name” in the above code to the, err…name of your database.

You can run that from the MySQL command line or from a program such as Sequel Pro.

Need it to be in Gigabytes? Just add another “/1024” after the existing two! So there you go – how to determine the MySQL database size, in a one-liner!

Don’t forget, this is the uncompressed database size. So, if you were to run a MySQL dump, it’s probably going to be smaller than that – as you’re gzip’ing it, right?