Below is the simple script for monitoring & auditing mysql general logs.This can be set as per the need(eg. daily,horly).It will help in capturing both DDL & DML Operations on database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
#!/bin/bash Error_log_location=/var/log rot_log_path=/home/db_audit_log bkp_log_path=/BACKUP/database/db_audit_logs SERNAME="dbtut" SERVER="ip-10.0.0.1" MAIL_LIST="xxx@gmail.com" attach_path="/home/db_audit_log/mysql_audit_alert.log.gz" egrep -i 'alter table|update table|delete table' $Error_log_location/mysql_audit.log | sort -u > $rot_log_path/mysql_audit.txt sudo cat $Error_log_location/mysql_audit.log | gzip > $rot_log_path/mysql_audit_alert.log.gz sudo cat $Error_log_location/mysql_audit.log | gzip > $bkp_log_path/mysql_audit_archieved_`date +%Y-%m-%d-%H:%M:%S`.log.gz if [ $? -eq 0 ]; then sudo cat /dev/null > $Error_log_location/mysql_audit.log else echo "fail to empty the file" fi if [ -s "$rot_log_path/mysql_audit.txt" ]; then cat $rot_log_path/mysql_audit.txt | mutt -s "Daily Audit Log for Mysql database for $SERNAME ($SERVER) at `date` " ${MAIL_LIST} -a ${attach_path} fi if [ $? -eq 0 ]; then cd /tmp/ cd /home/np-db/dba/db_audit_log/ FILE_TYPE="*.log.gz" FILE_TYPE1="*.txt" find ${FILE_TYPE} -type f -delete find ${FILE_TYPE1} -type f -delete else echo "fail to mail the logs" fi |
