A nice thing about MySQL is that it stores everything inside information_schema, and we can make use of it to calculate database size and table size as necessary when monitoring.
To query database size in MB of all databases inside, execute following command:
SELECT
TABLE_SCHEMA "Database Name",
SUM(DATA_LENGTH + INDEX_LENGTH ) / 1024 / 1024 "Database Size (MB)"
FROM
information_schema.TABLES
GROUP BY TABLE_SCHEMA;
The result will look like this:
+--------------------+--------------------+
| Database Name | Database Size (MB) |
+--------------------+--------------------+
| information_schema | 0.00986211 |
| mysql | 0.92094517 |
+--------------------+--------------------+
2 rows in set (0.01 sec)
Because the size is in bytes, so we have to divide by 1024 twice to get value in Megabyte. You can use ROUND() to round up the number.
Similarly, querying table size in a specific database is following the same syntax, just make a condition for table, like this:
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "game_db"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
As you see, you can order the result as well.
What if you want to query table size of all databases? Here it is.
SELECT
TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
Just remove the WHERE condition, that’s all you have to do.
