MySQL like most other relational databases provides us with useful metadata about the database. The official MySQL documentation refers to the INFORMATION_SCHEMA metadata as tables.
We can get all the information from INFORMATION_SCHEMA tables, like views, user_privilieges, columns, and tables. For our purposes, we will focus on the tables metadata, which we can query to actually extract the size of various tables in the system.
SQL Script
Script #1: This script lists out the entires databases sizes.
SELECT table_schema AS "Database Name",
Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 1) AS "Size (GB)"
FROM information_schema.tables
GROUP BY table_schema
Script #2: This script list out the size of a particular database.
SELECT table_schema AS "Database Name",
Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 1) AS "Size (GB)"
FROM information_schema.tables
WHERE table_schema = "<database name>"
Script #3: Now if you want to check the size of the individual tables then the script is for you.
SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS "Size (MB)"
FROM information_schema.tables
WHERE TABLE_SCHEMA = "<database name>"
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
