View Sizes of MySQL Databases & Tables over SSH

Sometimes, your typical database GUI (such as phpMyAdmin) won’t be usable for managing really big databases and you’ll have to work directly on the command line (SSH) for this.

After connecting to your server, you need to log into mysql with something like the following (this could vary depending on your server OS and set up):-

mysql -u username -p

You will then be prompted to enter your MySQL password which you must do.

To view a list of databases and their sizes, use the following:-

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;

To view a list of tables in a database and sort them by size (highest first), use the following:-

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;

Make sure you replace DATABASE_NAME with the name of the database you want to investigate.