Get the total size of your MySQL databases and tables

It's always useful to know the size of your database tables on your MySQL server.

This will order them by the larger tables first.

SELECT table_schema as `Database`, table_name AS `Table`, FORMAT(TABLE_ROWS, 0) AS `Est. Row Count`, FORMAT(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`, AUTO_INCREMENT AS `Next Row ID` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;  

This will produce something similar to below.

DatabaseTableEst. Row CountSize in MBNext Row ID
test1email_log150,0572,729.923121634
testcrawl_log15,1802,577.5216222
test3email_outbound148,1081,426.891667484
test4request_log178,259675.942062604

Only observations were that the Est. Row Count was approximately 20k rows out for the first result.

To get the total size of all database tables, use the following.

SELECT FORMAT((SUM(data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES;  

Note: Using this technique I was able to free up around 15Gb of space on my local dev environment, truncating old tables that I no longer needed.