Working on a production/test servers would always need you to be ready with your rescue tools, be it third party tools or commands and scripts to help you troubleshoot and monitor the database servers. Usage of the third party tools will be pretty easy to manage or monitor your databases through a GUI. However how many of us DBA’s would monitor the databases from command line very often?

Here are few scripts that I found online which I would like to post for your quick reference. Please find attached the scripts and follow the usage instructions.

allschemas.sql

or copy the following to a notepad and save it as allschemas.sql

**************************************

Purpose: List a summary of all Instance Schemas including disk size

**************************************

SELECT NOW(), VERSION();

SELECT table_schema,
 SUM(data_length+index_length)/1024/1024 AS total_mb,
 SUM(data_length)/1024/1024 AS data_mb,
 SUM(index_length)/1024/1024 AS index_mb,
 COUNT(*) AS tables,
 CURDATE() AS today
FROM information_schema.tables
GROUP BY table_schema
ORDER BY 2 DESC;

# Set a session variable for the largest schema for later use
SELECT @schema := table_schema,
 SUM(data_length+index_length)/1024/1024 AS total_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY 2 DESC
LIMIT 1;

Once you copy, at the prompt

$ mysql -u[user] -p -v -v -v < allschemas.sql

It prompts you to enter the password.

Once done, it displays all you schema information in the below format

OUTPUT:

Similarly you can calculate the database size of an individual schema.

Download the attached script and follow the usage.

perschema.sql

or copy the following to a notepad and save it as perschema.sql

**************************************

Purpose : Lists summary of a specific schema

**************************************

SELECT NOW(), VERSION();

# Per Schema Queries

SET @schema = IFNULL(@schema,DATABASE());

# One Line Schema Summary
SELECT table_schema,
 SUM(data_length+index_length)/1024/1024 AS total_mb,
 SUM(data_length)/1024/1024 AS data_mb,
 SUM(index_length)/1024/1024 AS index_mb,
 COUNT(*) AS tables,
 CURDATE() AS today
FROM information_schema.tables
WHERE table_schema=@schema
GROUP BY table_schema;

# Schema Engine/Collation Summary
SELECT table_schema,engine,table_collation,
 COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema=@schema
GROUP BY table_schema,engine,table_collation;


# Schema Table Usage
SELECT table_schema,table_name,engine,row_format, table_rows, avg_row_length,
 (data_length+index_length)/1024/1024 as total_mb, 
 (data_length)/1024/1024 as data_mb, 
 (index_length)/1024/1024 as index_mb,
 CURDATE() AS today
FROM information_schema.tables 
WHERE table_schema=@schema
ORDER BY 7 DESC;

# Schema Table BLOB/TEXT Usage
select table_schema,table_name,column_name,data_type 
from information_schema.columns 
where table_schema= @schema
and ( data_type LIKE '%TEXT' OR data_type like '%BLOB');

set @schema = NULL;

Once copied to the /root directory, at the prompt

$ mysql -u[user] -p -v -v -v [table-schema] < perschema.sql

Replace the user and table-schema appropriately. This would display you the individual summary of the database schemas.

You can also schedule this as CRON jobs to run at a certain intervals. Watch the space for the CRON job.

-Death

About these ads