Regular check for changes of MySQL database
Below outlined script implements regular check of a MySQL database for modifications.
Modifications can be detected using two approaches - last modification timestamp of a table or row count in each table. Both approaches have their positives and negatives depending on the use case. Last modification timestamp check is faster with almost zero system resources involved and is suitable for mainly static tables (only inserts and deletes). Row count comparison on the other hand is suitable for tracking row count in frequently updated tables.
Sample usage
If we have a system that logs its errors in a database table. When you want to implement mail notifications upon new error you have to modify the system. When such modification is not possible or not wanted you can employ this script with a proper recurring execution interval to track new errors.
Another example is website that allows users to their own articles. If the employed content management system does not provide notification functionality out of the box then you can setup notification on receiving new content.
Third application - notifying for new order in an online shop - just setup monitoring on the orders table.
Script overview
The complete source of the script is here. It uses disk file to persist table state between checks. Here's a sample integration - we'll monitor database webshop1 authenticating with user webuser and password webpass. To persist state between checks we'll use /home/webuser/webshop1_state. Now all that is left is to setup this three lines:
/home/webuser/checker.php:
<?php
$checker = new DatabaseChangesChecker('localhost', 'webuser', 'webpass', 'webshop1', '/home/webuser/webshop1_state');
$checker->check_update_time();
echo $checker->perform_check();
?>
Add this to the crontab as well:
MAILTO:notify@example.com
*/20 * * * * /usr/bin/php /home/webuser/checker.php
If no changes are detected, then nothing is written to output and no mail arrives. When change is detected, the output is not empty and the cron daemon send email notification.
To suppress checks on a frequently modified table, for example visitor tracker, add the following line right after setup:
$checker->set_ignore_tables(array("visitor_stats"));
No comments yet
This page was last modified on 2024-04-20 01:27:53