How to find the mysql database and tables size

  1. Check the size of all the databases:-

first login to mysql client using the root user and password.

SELECT table_schema AS “Database”,

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)”

FROM information_schema.TABLES

GROUP BY table_schema;

Another option which calculates overheads of a specific database:-

SELECT ROUND((SUM(data_length+index_length+data_free) + (COUNT(*) * 300 * 1024))/1048576+150, 2) AS MegaBytes FROM information_schema.TABLES WHERE table_schema = ‘DATABASE-NAME

  1. Check the size of the tables in a specific database 

 

SELECT table_name AS “Table”,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”
FROM information_schema.TABLES
WHERE table_schema = “database name
ORDER BY (data_length + index_length) DESC;

 

Where database name is name of specific database

 

  1. Shows size of all tables in all databases:-

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;