How to List Sizes of All MySQL Databases.

If you want to know which databases are actually taking up how much space. This is for you!

It can be checked from the commandline by logging into the MySQL root console.

Log in and simply run the given query.

SELECT table_schema AS “Database name”, SUM(data_length + index_length) / 1024 / 1024 AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;

The result will come up like the following:

+———————————-+———————————+
| Data Base Name                       | Data Base Size in MB     |
+———————————-+———————————+

| continua_mood705                 | 24.70312500                 |
| cphulkd                                      | 1.28040123                  |
| cracksvi_database                   | 11.48291111                  |
| draniket_ashish                       | 0.00099564                 |
| draniket_saurabh                    | 0.00242996                 |
| draniket_vtig619                     | 12.06250000               |
| educxpre_wp810                     | 3.02036572                 |
| wqeper_dev                              | 226.32313919             |
| wqeper_smag                           | 314.68734741             |
| wqeper_smage621                   | 15.95507813               |
+———————————+————————– +

Along with knowing the above, one can pull out the free space available for one’s Data Bases in MySQL.

Log into the MySQL root console and simply run the given query.

SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”, sum( data_free )/ 1024 / 1024 “Free Space in MB” FROM information_schema.TABLES
GROUP BY table_schema ;

+————————————–+————————–+—————————-+

| Data Base Name                              | Data Base Size in MB | Free space in MB            |
+————————————–+————————–+—————————-+

| continua_mood705                        | 24.70312500                | 2218545.00000000      |
| cphulkd                                             | 1.28040123                   | 0.03804016                     |
| cracksvi_database                          | 11.48291111                   | 2296018.00000000      |
| draniket_ashish                              | 0.00099564                 | 0.00000000                    |
| draniket_saurabh                           | 0.00242996                 | 0.00000000                    |
| draniket_vtig619                            | 12.06250000                | 0.00000000                   |
| educxpre_wp810                            | 3.02036572                  | 0.00000000                    |
| wqeper_dev                                     | 226.32313919               | 2239674.00000000      |
| wqeper_smag                                  | 314.68734741                | 2225588.00310898       |
| wqeper_smage621                         | 15.95507813                  | 2197416.00000000       |
+————————————–+————————–+—————————-+