<\/p>\n
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.<\/p>\n
#!\/bin\/bash\r\n\r\nError_log_location=\/var\/log\r\nrot_log_path=\/home\/db_audit_log\r\nbkp_log_path=\/BACKUP\/database\/db_audit_logs\r\n\r\nSERNAME=\"dbtut\"\r\nSERVER=\"ip-10.0.0.1\"\r\nMAIL_LIST=\"xxx@gmail.com\"\r\nattach_path=\"\/home\/db_audit_log\/mysql_audit_alert.log.gz\"\r\n\r\negrep -i 'alter table|update table|delete table' $Error_log_location\/mysql_audit.log | sort -u > $rot_log_path\/mysql_audit.txt\r\nsudo cat $Error_log_location\/mysql_audit.log | gzip > $rot_log_path\/mysql_audit_alert.log.gz\r\nsudo cat $Error_log_location\/mysql_audit.log | gzip > $bkp_log_path\/mysql_audit_archieved_`date +%Y-%m-%d-%H:%M:%S`.log.gz\r\n\r\nif [ $? -eq 0 ]; then\r\n\r\nsudo cat \/dev\/null > $Error_log_location\/mysql_audit.log\r\n\r\nelse\r\n\r\necho \"fail to empty the file\"\r\n\r\nfi\r\n\r\nif [ -s \"$rot_log_path\/mysql_audit.txt\" ]; then\r\ncat $rot_log_path\/mysql_audit.txt | mutt -s \"Daily Audit Log for Mysql database for $SERNAME ($SERVER) at `date` \" ${MAIL_LIST} -a ${attach_path}\r\nfi\r\n\r\nif [ $? -eq 0 ]; then\r\n\r\ncd \/tmp\/\r\ncd \/home\/np-db\/dba\/db_audit_log\/\r\n\r\nFILE_TYPE=\"*.log.gz\"\r\nFILE_TYPE1=\"*.txt\"\r\nfind ${FILE_TYPE} -type f -delete\r\nfind ${FILE_TYPE1} -type f -delete\r\n\r\nelse\r\n\r\necho \"fail to mail the logs\"\r\n\r\nfi\r\n\r\n<\/pre>\n\n <\/p>\n
<\/div>\n