Ozzie.eu

Love to code, although it bugs me.

Get a summary footprint on a MySQL server instance

Ozzie 5:22 PM , No comments
Landing on an enterprise with ongoing projects mean that servers are often handed to IT staff without complete knowledge of  what's inside.
I've built a simple script, scraping from here and there, to gather a summary of relevant information.
Once you've gained remote access to the MySQL instance, you can execute the queries to identify the following information regarding the target database server:
  • The host name, what operating system it runs on, the MySQL version installed, default collation of the instance, the installation directory and the data directory;
  • How many user databases it hosts, what they're called and the collation used;
  • The size of each database as a whole and broken down by storage engine;
  • All the tables and their space used ;
  • The creation and last modification on each database.
The script is commented and follows the same order as the above topics:
#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;
view raw footprint.sql hosted with ❤ by GitHub

Running this on my test server, I find the following result for the host, operating system and directory locations:

The following result tells me how many databases there are on the instance:


If relevant, I can choose to see the database names and respective collations:

Then, the script gets the size on each database, in MB:

It breaks that size on the different storage engines:

Still on the size reporting, we get each table's size on records as well as indexes:

Finally, it checks for the creation and update dates of the existing databases to help determine if it's important to have a recent backup of them or not:

This script only gives a summary of the overall instance but has enough details to determine the versions of the software, volume of information and usage of the databases.

Photo credit: tableatny@Flickr

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )