If you want to find out the size that MySQL databases use, you can issue the following query to list all the databases, with their respective sizes in megabytes;
mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------+
| database | size in MB |
+--------------------+----------------+
| test1 | 14651.58056259 |
| test2 | 1262.16237235 |
| test3 | 32151.64254152 |
| information_schema | 0.00390625 |
+--------------------+----------------+
4 rows in set (0.02 sec)
If you have large databases (as the example above), you probably want to show the result in gigabytes;
mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------------+
| database | size in GB |
+--------------------+-----------------+
| test1 | 14.308184143156 |
| test2 | 1.232580441748 |
| test3 | 31.398088419453 |
| information_schema | 0.000003814697 |
+--------------------+-----------------+
4 rows in set (0.06 sec)
And you can apply WHERE-parameters as in a normal SELECT. So, if you want to show the size of only a specific database, for example ‘test3’, you could use the following query;
mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES WHERE table_schema='test3' GROUP BY table_schema;
+------------+-----------------+
| database | size in GB |
+------------+-----------------+
| test3 | 31.398088419453 |
+------------+-----------------+
1 row in set (0.03 sec)
Great info. Very helpful. Thanks
just what i was looking for – thanx :)
do you know, how to show the creation date of the dbs instead of size, too? thx
got it, just change the line you provided like the following:
SELECT table_schema “database”, CREATE_TIME FROM information_schema.TABLES GROUP BY table_schema;