MySQL – How to get size of MySQL database and table size ?

Hello,

Today I was trying to get the database size on one of the MySQL / MariaDB servers. I found the below MySQL queries which can do the job.

You can use the same approach as Amazon RDS or Azure Managed Database.

Solution

Connect to the database with the below command from a shell.

mysql -hHOSTNAME -uroot -p

Once connected, run the below MySQL query to show the database sizes in MB.

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

We can run the below query to show the database size in GB.

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

Bonus :

Run the below querey to show the table size in ascending order across all the databases on the server.

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

I hope this post was helpful.