MySQL get disk usage of all databases
I was tired to get manually disk space used for all MySQL databases, I just created a stored procedure to get an overview of the database sizes in our MySQL server.
MySQL don’t have a command that allows us an overall summary of the databases, something like SHOW TABLE STATUS for databases. The “SHOW DATABASES” command lists only the current databases without any other information like how many space are using or how many tables there are, etc.
The procedure that I wrote this based on INFORMATION_SCHEMA database, which contains rich information of all existing databases.
I suggest, in a previous post, place all these procedures in a database called tools, but you can still choose different location.
So when call the procedure will have a global view of information that will be useful for maintenance tasks.
mysql> call tools.sp_overview(); +------------------------------+---------+--------+----------+---------+ | Database | Charset | Tables | Routines | Size Mb | +------------------------------+---------+--------+----------+---------+ | database1 | utf8 | 43 | 28 | 7.0 | | database2 | latin1 | 43 | 28 | 205.0 | | database3 | utf8 | 116 | 0 | 126.2 | | database4 | utf8 | 99 | 0 | 0.3 | | database5 | utf8 | 165 | 0 | 77.4 | | database6 | utf8 | 121 | 2 | 719.4 | | database7 | utf8 | 122 | 0 | 91.3 | | database8 | utf8 | 116 | 0 | 89.7 | | database9 | utf8 | 124 | 0 | 4.5 | | database10 | utf8 | 113 | 0 | 147.7 | | database11 | latin1 | 119 | 3 | 436.4 | | database12 | latin1 | 122 | 0 | 439.1 | | database13 | latin1 | 122 | 4 | 452.7 | | database14 | utf8 | 115 | 0 | 273.0 | | database15 | utf8 | 122 | 0 | 265.5 | | database16 | utf8 | 0 | 0 | NULL | | database17 | latin1 | 26 | 0 | 0.9 | | database18 | latin1 | 9 | 0 | 0.0 | | database19 | latin1 | 22 | 0 | 0.3 | +------------------------------+---------+--------+----------+---------+ 19 rows in set (3.01 sec)
The first time execution maybe you will get results in a few seconds.
There is the source code:
DELIMITER $$ DROP PROCEDURE IF EXISTS tools.sp_overview$$ CREATE PROCEDURE tools.sp_overview() BEGIN SELECT s.SCHEMA_NAME AS `Database`, s.DEFAULT_CHARACTER_SET_NAME AS `Charset`, COUNT(t.TABLE_NAME) AS `Tables`, (SELECT COUNT(*) FROM information_schema.ROUTINES AS r WHERE r.routine_schema = s.SCHEMA_NAME) AS `Routines`, round(SUM(t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576 ,1) AS `Size Mb` FROM information_schema.SCHEMATA AS s LEFT JOIN information_schema.TABLES t ON s.schema_name = t.table_schema WHERE s.SCHEMA_NAME NOT IN ('information_schema', 'performance_schema') GROUP BY s.SCHEMA_NAME; END$$ DELIMITER ;