Differences
This shows you the differences between two versions of the page.
tech:mysql:check_all_database_sizes [2014/11/15 09:13] |
tech:mysql:check_all_database_sizes [2014/11/15 09:13] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Check the size of all MySQL databases ====== | ||
+ | The script below will get the sizes of all databases for a MySQL server. Make sure you "use" a database before you run the script. | ||
+ | <code sql> | ||
+ | CREATE TABLE z_db_size | ||
+ | SELECT TABLE_SCHEMA AS 'database_name', TABLE_NAME AS 'table_name', | ||
+ | ((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024) AS size_in_mb | ||
+ | FROM INFORMATION_SCHEMA.TABLES; | ||
+ | </code> | ||
+ | |||
+ | Will create temporary table z_db_size | ||
+ | |||
+ | <code sql> | ||
+ | select database_name, sum(size_in_mb) | ||
+ | from z_db_size | ||
+ | group by database_name | ||
+ | order by sum(size_in_mb); | ||
+ | </code> | ||
+ | |||
+ | Once report is captured, drop temporary table | ||
+ | <code sql> | ||
+ | drop table z_db_size; | ||
+ | </code> | ||