Mail alert from MySQL when databases are edited

How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits.

I have tried Triggers but its not working.

Server specifications:

• Ubuntu 10.04 64 Bit

• MySQL Server 5.1.41

script which i have tried

#!/bin/sh

  tail -f /var/log/mysql/mysql.log | egrep 'INSERT|UPDATE|DELETE|root'
      | mail -s 'backend update' sushanth@example.com

but its not working

MySQL has no particular SMTP setup, mechanisms, or drivers whatsoever built in.

However, there are two basic things you can do the kind of monitoring you want.

Option 1 : You Could Monitor the Binary Logs

If binary logs are enabled, you could write a shell script to call mysql and do SHOW MASTER STATUS; If either the filename or filesize changes, something changed. Once detected, you could send out an email expressing that something changed !!!

Try something like this:

FIRST_READ=1
while [ 1 -eq 1 ]
do
    mysql -h... -u... -p... --skip-column-names -A -e"SHOW MASTER STATUS" > /tmp/ms.txt
    currfile=`cat /tmp/ms.txt | awk '{print $1}'`
    currsize=`cat /tmp/ms.txt | awk '{print $2}'`
    if [ ${FIRST_READ} -eq 0 ]
    then
        SOMETHING_CHANGED=2
        if [ "${prevfile}" == "${currfile}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
        if [ "${prevsize}" == "${currsize}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
        if [ ${SOMETHING_CHANGED} -gt 0 ]
        then
            echo "Something Changed" | mail -s "Something Changed Subject" abc@xyz.com
        fi
    fi
    FIRST_READ=0
    prevfile=${currfile}
    prevsize=${currsize}
    sleep 10
done

Option 2 : You Could Monitor information_schema.tables

You could loop through every table and check its UPDATE_TIME column in information_schema.tables

First collect all table names prepended with database. Then, loop through all the table names and check that entry in information_schema.tables.

Try the following (Any table that changed in the last 10 minutes):

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine IS NOT NULL" > /tmp/TableNamesToPoll.txt

while [ 1 -eq 1 ]
do
    for DBTB in `cat /tmp/TableNamesToPoll.txt`
    do
        DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
        TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
        NEWUPDATE=`mysql -h... -u... -p... --skip-column-names -A -e"SELECT IFNULL(update_time,NOW() - INTERVAL 100 YEAR) > (NOW() - INTERVAL 10 MINUTE) UpdatedRecently FROM information_schema.tables WHERE table_schema='${DB}' AND table_name='${TB}'"`
        if [ ${NEWUPDATE} -eq 1 ]
        then
            echo "Something Changed in ${DBTB}" | mail -s "Something Changed Subject" abc@xyz.com
        fi
    done
    sleep 5
done

These are just skeleton scripts to detect changes. For option 1, you can do mysqlbinlog against the current binary log and see the SQL that was executed in whatever timeframe you need. For option 2, you can change the SQL to retrieve the datetime stamp of the last update for a given table.

UPDATE 2011-06-29 06:30 EDT

Option 3 : You Could Monitor the general log

Interestingly, you could activate the general log. What's even more intriguing is that you can activate it a MySQL Table. The template to the general log as a table already exists in /var/lib/mysql/mysql as general_log.CSV. Here are the steps:

Step 01) Add these to /etc/my.cnf

[mysqld]
log-output=TABLE
log

Step 02) service mysql restart (general_log is a CSV table after restart)

Step 03) Run these commands to convert the general_Log from CSV to MyISAM

SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

Step 04) Move the general_log file to a huge disk volume that can accommodate a fast growing log table

Example: If you have the following disk layout

[root@iml-db10 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg1-root  117G  3.2G  108G   3% /
/dev/mapper/vg2-data01
                      1.7T  688G  877G  44% /data
/dev/sdc1             3.6T   36G  3.4T   2% /backup
/dev/sda1              99M   18M   77M  19% /boot
tmpfs                  95G     0   95G   0% /dev/shm
none                   16G   51M   16G   1% /var/tmpfs

Perform these steps to move the general log table:

mkdir /backup/general_log
mv /var/lib/mysql/mysql/general_log.MY* /backup/general_log/.
chown -R mysql:mysql /backup/general_log
ln -s /backup/general_log/general_log.MYD /var/lib/mysql/mysql/general_log.MYD
ln -s /backup/general_log/general_log.MYI /var/lib/mysql/mysql/general_log.MYI

When do make sure the symlinks exist

[root@db1]# ls -l /var/lib/mysql/mysql/general*
-rw-rw---- 1 mysql mysql 8776 Jun 25 15:53 /var/lib/mysql/mysql/general_log.frm
lrwxrwxrwx 1 root  root    35 Jun 25 18:33 /var/lib/mysql/mysql/general_log.MYD -> /backup/general_log/general_log.MYD
lrwxrwxrwx 1 root  root    35 Jun 25 18:32 /var/lib/mysql/mysql/general_log.MYI -> /backup/general_log/general_log.MYI

Step 05) Run this SQL command

SET GLOBAL general_log = 'ON';

That's it. You should have the general_log as a MyISAM table

```
mysql> show create table mysql.general_log\G
1. row
Tab

StackExchange Database Administrators Q#3159, answer score: 3