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 ;
Related Posts :
From command line we have the entire MySQL server on hands (if we have privileges too of course) ...
Do you get write laziness in the command line everything what you need to connect to a MySQL ser ...
If you are using phpBB maybe you need to show a individual phpBB post on an external page (o ...
Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields ...
bmon is a useful network monitoring tool, the most useful feature is that bmon store historical ...
Are you familiar with common_schema? It has a lot of built in views to provide information like this. For example, you can get disk usage of all databases like this:
select table_schema as “Database”,
count_tables as “Tables”,
round((total_size / 1024 / 1024),1) as “Size Mb”
from common_schema.data_size_per_schema;
Here’s the link to common_schema:
http://code.google.com/p/common-schema/
Nice proc.
The common_schema for MySQL has a similar solution (http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/data_dimension_views.html), and in addition many more diagnostic and administrative views / tools / utilities.