Script to gather a fast summary on a MySQL database server.

footprint.sql

#Information about the server
select @@hostname as 'host',@@version_compile_os as 'OS',
@@version as 'Server Version',@@collation_server as 'Default collation',
@@version_compile_machine as 'Server Binary',
@@basedir as 'Binaries Folder', @@datadir as 'Data Folder';

#Information about the databases
#Number of databases
select count(schema_name) as 'Number of DB' from 
INFORMATION_SCHEMA.schemata 
where SCHEMA_NAME<>'information_schema'
and SCHEMA_NAME<>'performance_schema';

#Names ans collations
select schema_name as 'DB Name',DEFAULT_COLLATION_NAME as 'Collation' from 
INFORMATION_SCHEMA.schemata 
where SCHEMA_NAME<>'information_schema'
and SCHEMA_NAME<>'performance_schema';

#Database Size
SELECT table_schema as 'DB', 
sum(data_length + index_length) / ( 1024 *1024 ) as 'total size',
sum(data_free)/( 1024 * 1024) as 'Free Space in MB'
FROM information_schema.TABLES
where table_schema<>'information_schema'
and table_schema<>'performance_schema'
and not engine is null
group by table_schema;

#Database Size broken into MyISAM and innoDB
SELECT table_schema as 'DB', ENGINE,
sum(data_length + index_length) / ( 1024 *1024 ) as 'total size',
sum(data_free)/( 1024 * 1024) as 'Free Space in MB'
FROM information_schema.TABLES
where table_schema<>'information_schema'
and table_schema<>'performance_schema'
and not engine is null
group by table_schema, engine
order by table_schema, engine;

#Tables
SELECT concat( table_schema, '.', table_name ) table_name, 
concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
FROM information_schema.TABLES
ORDER BY ( data_length + index_length ) DESC;

#Check for used and unused databases
SELECT MAX(UPDATE_TIME), MIN(CREATE_TIME), TABLE_SCHEMA 
FROM information_schema.TABLES
where table_schema<>'information_schema'
and table_schema<>'performance_schema'
GROUP BY TABLE_SCHEMA 
ORDER BY 1, 2;