Monitoring MySQL database using PHP

I have a mysql database with 12,000 entries, what i want setup is the ability to monitor a column in the database and if/when the column is altered for any entry it sends me an email with the details.

EDIT: I have access to mysql db, but not the script which works with it. So it should monito开发者_运维技巧r it for changes...

You could create some triggers on the table, if your version of MySQL has them. A trigger can then invoke any function you care to create. A trigger has the advantage that any insertion or deletion or any update of the column will cause it to fire; you wouldn't have to change any other code to make it happen. See here for more... http://dev.mysql.com/doc/refman/5.0/en/triggers.html

• Create a trigger on update
• Create another table (lets call it cron_table), where the trigger will insert information of the updated row (may be old value, new value etc)
• Setup a cron, which will call a script which will check the cron_table and send email if any entry is found. Cron interval can be setup according to need.

--- If you could send email from trigger, there would be no need for a separate table and cron ---

try something similar to this , you can edit the function to send you and email if the query has insert and TABLE_NAME or COLUMN_NAME in it

set up one column to be a datetimestamp.

This will update on every change of the row. There you can run a sql query either via a cron job or after every few php queries to return you the list of changed rows since the last check.

Select   * from tbl_myentries where EntryUpdated > '$TimeSinceLastCheck'

you need to understand Data Manipulation Language (DML) triggers in my sql: use

CREATE TRIGGER salary_trigger
    BEFORE UPDATE ON table_name
    REFERENCING NEW ROW AS n, OLD ROW AS o
    FOR EACH ROW
    IF n.columnName <> o.columnname THEN

    END IF;
;

Create a trigger on a change on your column, then insert it to another table as log table.

Run cron job on your table that will send you an email.