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)
Comments