MySQL database sizes

November 29, 2019 Stanly G Linux, MySQL 0 minutes, 35 seconds

How Big are your MySQL databases? Don't believe the results from du?

To get the size of each database:

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

To get the size of each table in a single database, replace "mydb":

SELECT table_name "Table Name", table_rows "Rows Count", 
round(((data_length + index_length)/1024/1024),2) "Table Size (MB)" 
FROM information_schema.TABLES WHERE table_schema = "mydb";

The "Rows Count" may be off, but it will be the right order of magnitude.

To get the size of all the table in all the databases:

SELECT table_schema "Database", table_name "Table Name", table_rows "Rows Count", 
round(((data_length + index_length)/1024/1024),2) "Table Size (MB)" 
FROM information_schema.TABLES ORDER BY table_rows ASC;

Change the ORDER BY clause as needed.