as_dbserver_check: Checking / alarming Alive state of Database server(s)

Sometimes your ISP's MySQL or other DB database servers may go down. This can be critical for your business, and you surely want to know about it ASAP.
This module's task is doing what you want: it checks out alive or 'dead' state of all registered DBMS servers (different kinds of DBMS supported). If some server seems to get down, you (or your web-admin) receive email notification, and your ISP support team receives another one (message for ISP can contain your client info like agreement number or Client ID, that support team usualy wants to know).

You schedule a call of your checking script with appropriate time interval (say 10 minutes), and since this moment your DB servers are monitored by you, not only by your ISP techicians.

When DB server return to operational mode, you receive respective "I'm back" notification.
Supported Databases are: The only used and tested by me is MySQL. The rest databases can be tested by anyone, and if something wrong please let me know.

One more thing: may be you have an engine for sending SMS from your site and would like to get instant SMS messages about DB server problems. Then you can "register" your function that will use SMS engine and send alert messages to your mobile device.

Installation on the site

Simple code example

require_once('as_dbserver_check.php');

$checker = new CDbChecker('My tiny site','','My client ID is 7777, agreement 10101/2009');
$checker->SetEmails('myownmail@acme.com','support@my-isp.com');

$checker->AddServer(DBTYPE_MYSQL,'localhost','username','password');
$checker->CheckDbState();

Using the class CDbChecker

All checking jobs are performed in CheckDbState() method, so ALL needed parameters must be set before calling it.
Here is a full method list with descriptions.

CDbChecker($title='', $homefolder='', $isptext='') - class constructor.

$title can contain any short description of your site - it's inserted in email message that will be sent to you or defined admins in your company.

$homefolder - folder on the local server, that will hold "flag" files. These flags created to avoid repetitive "spamming" when server is down for a long time (for example: your checking runs every 5 minutes, and DB server down 3 hour. To avoid sending "alarm" every 5 minutes, small "flag" file is created. When DB server goes online, this file will be deleted, and you'll receive a message "server is UP"). No need to say, this folder must be writable from PHP scripts. Default value - current folder, './'

$isptext is additional text body for your ISP support. It can contain your client id or anything else you want. This text will be prepended to the email message.

SetEmails($adminemail,$ispemail='', $emailcset='') registers two email addresses: you company's web-admin (may be you) and your ISP support address. When some DB server down detected, the alarm message is sent to both addresses (if both passed). And when server gets up, the message is sent only to you or your admin ($adminemail address).

AddServer($dbtype,$address,$login='',$passwd='',$connfunc=false,$downtime='') - method registers one database server to check out.

$dbtype is one of database types from DBTYPE_MYSQL,DBTYPE_MSQL,DBTYPE_POSTGRESQL,DBTYPE_ORACLE,DBTYPE_MSSQL,DBTYPE_DB2, or it can be any other code (non-listed DB type).

$address is a server address. In MySQL case it can be in "host:port" form.
For PostgreSQL it can be a full connect string like "host=localhost port=5432 dbname=mary" (new pg_connect format)- in that case parameters $login and $password not used. Otherwise connection string will be constructed from passed parameters. So if your server has non-standard port, you should pass full connect string $address.

$connfunc is optional "make connection" function's name, that is used for "undefined" data types. Besides it will be used (if passed) for MS SQL databases, when mssql extension not loaded. The same is correct for Oracle databases. $connfunc should return non-empty value if connection successfully established. Any empty returned value (0, false) is treated as "connect fail".

$downtime can be used if your ISP turns down databases for service tasks every day at the same time. For example since 02 to 04 AM your DB can be down due to ISP service jobs. So checking database state at this interval must be paused. In that case pass non-empty string in $downtime in format "hh:mm-hh:mm", holding beginning and ending time (hh-hours, mm-minutes). In our case it should be "02:00-04:00" string.
Of course, this pause can be defined in cron schedule string. But in that case it will pause the whole job, instead of "per-connection" apply.

SetSMSEngine($smsfunc='') registers your function that can prepare and send SMS messages. In fact, it can be any function that will be called if some server state changes detected (DB server goes down or up). This function must receive a text to send in first parameter.

Please note: CDbChecker knows nothing about SMS formats, supported character sets or maximal message length, it just calls function whose name was registered !

CheckDbState() finally this is called to do the job. All registered DB connections are checked, if some state has changed since last check, respective message will be composed. "Server is Down alarms" and all "Server is UP" are gathered in two independent text block, so two independent messages can be called: one about servers that get down, and another one if some servers get up. If defined, "SMS sending" function will be called with each of these messages as parameter.
Method returns true if all registered DB servers are OK, and false if something is down.
Distributed under BSD License

Version History

1.00.001 (06/18/2009)

Copyright © Alexander Selifonov, www.selifan.ru