Check MySQL database size using SQL query

SQL queries for checking space used by MySQL databases and tables Check size of all database Run the following query to check the size of each MySQL database.

SELECT TABLE_SCHEMA "Data Base Name", round(sum( data_length + index_length ) / 1024 / 1024, 2) "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| DB1                |                 0.88 |
| DB2                |                 7.73 |
| ezylinux.com       |                10.81 |
| information_schema |                 0.01 |
| performance_schema |                 0.00 |
| rail               |                 0.11 |
| DEV                |              1154.80 |
+--------------------+----------------------+

SELECT TABLE_SCHEMA "Data Base Name", round(sum( data_length) / 1024 / 1024, 2) "Data Base Size in MB",round(sum( index_length ) / 1024 / 1024, 2) "Index Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

+--------------------+----------------------+-----------------------+
| Data Base Name     | Data Base Size in MB | Index Base Size in MB |
+--------------------+----------------------+-----------------------+
| CDb                |               321.58 |                366.41 |
| DB1                |                 0.95 |                  0.13 |
| daily              |                 5.95 |                  0.24 |
| information_schema |                 0.00 |                  0.01 |
| mysql              |                 0.55 |                  0.10 |
| performance_schema |                 0.00 |                  0.00 |
| Ezylinux_db        |              5880.41 |               3307.18 |
| the_db             |              1213.91 |                  6.54 |
+--------------------+----------------------+-----------------------+
8 rows in set (7.73 sec)

Check table size of specific database To check the size of each table in a particular database, run the following query, replacing YOUR_DATABASE with the database name.

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like 'YOUR_DATABASE';

+--------------+-----------------------+---------+
|   Database   | Table                 | Size    |
+--------------+-----------------------+---------+
| YOUDATABASE  | posts                 | 0.02 MB |
| YOUDATABASE  | rails_admin_histories | 0.03 MB |
| YOUDATABASE  | schema_migrations     | 0.02 MB |
| YOUDATABASE  | users                 | 0.05 MB |
+--------------+-----------------------+---------+