Get a summary footprint on a MySQL server instance

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:

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