In continuation with yesterdays post, you can create a CRON JOB to run at intervals to monitor the disk space on the database servers. I have created a stored procedure which will be called by the CRON job at certain intervals. Not necessarily you would want to check the database size activity every hour, you can customize it accordingly.

Here are the steps you can follow,

Firstly you need to create a SP to calculate the database size of all the schemas, I have attached the SP for your reference.

usp_allschemas.sql

You can copy the same from below,

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysql`.`usp_allschemas` $$

CREATE PROCEDURE `mysql`.`usp_allschemas` ()

BEGIN

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;

END $$

DELIMITER ;

I have created this stored procedure in mysql default database. Now once you have created the SP, it’s time to create a CRON JOB.

NOTE: In case your MySQL version is 5.1.12 or higher, you can use EVENTS to schedule your SP to run at Interval.

CRON JOB:

$ CRON –e

60,120,180,240,300,360 ****/root/Call_SP.sh 2>&1>> /root/call_sp.log

This job will run every hour, similarly you can change it as per your requirement.

Creating a shell to call the SP from the database.

$ vi Call_SP.sh

mysql -h ‘IPADDRESS’ -u root –p’password’ mydatabase -e “call usp_allschemas()”

Replace the IPADDRESS with the IP and Password with your working password and database and sp names respectively and its done.

Similarly you can create and schedule the CRON Job for perschema disk space monitoring as discussed in my previous post.

Thanks to the online resources provided by Mr Ronald Bradford , I have made my own modifications to automate his work.

You can vist his site for reference  http://ronaldbradford.com/ , to check out more MySQL solutions.

-Death

About these ads