If you manage a cPanel server and want to see the sizes of all MySQL databases, you can use a simple MySQL query. This is especially useful for identifying large databases that may need optimization or cleanup.
Finding the 20 Largest Databases
1. Access Your Server via SSH
- Log in to your WHM panel.
- Navigate to Terminal (WHM > Terminal), or connect via SSH if you prefer.
- Run the following MySQL command:
mysql -e "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
ORDER BY SUM(data_length + index_length) DESC
LIMIT 20;"
2. Understanding the Output
This query will return a list of databases with their sizes in MB, sorted from largest to smallest. The output will look something like this:
| Database | Size (MB) |
|---|---|
| big_database_1 | 1024.50 |
| big_database_2 | 950.75 |
| user_site_db | 820.20 |
3. Listing All Databases
If you want to see all databases, not just the largest 20, modify the query by removing LIMIT 20;:
mysql -e "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
ORDER BY SUM(data_length + index_length) DESC;"
Alternative Method: Using Disk Usage
If you prefer a file-based approach, you can check the size of database directories using:
du -sh /var/lib/mysql/* | sort -hr | head -20
However, this method is not always accurate for InnoDB databases, as they store data in shared tablespaces.
Need More Help?
If you need assistance managing your MySQL databases, our team at EncodeDotHost is here to help!
