Rsyslog Centralized collection of MySQL audit logs in Ubuntu

Service side

1. Install the latest version Rsyslog

sudo apt-get install Software-properties-common python-software-propertiessudo add-apt-repository ppa:adiscon/ v8-stable sudo apt-get updatesudo apt-get install Rsyslog

2, configuration directory storage MySQL audit log

vim/etc/rsyslog.d/50-default.conf# add:define logfiles$template mysql-audit, "/var/log/remote_log/%app-name%/% hostname%_%fromhost-ip%_log_%app-name%_% $YEAR%-% $MONTH%-% $DAY%.log "$template Remote,"/var/log/remote_log/% hostname%_%fromhost-ip%/log_%app-name%_% $YEAR%-% $MONTH%-% $DAY%.log "# Log all messages to the dynamically formed file.: App-name,isequal, "Mysql-audit"? Mysql-audit:fromhost-ip,!isequal, "127.0.0.1"? remote& stop

3, install MySQL and rsyslog-mysql module,

Apt-get Install Rsyslog-mysql mysql-server-y #安装过程中会自动创建表

4, Configure the/etc/rsyslog.d/50-default.conf, in order to keep the audit log of MySQL local copy, MySQL database write a copy

Vim/etc/rsyslog.d/50-default.conf$modload Ommysql #加载ommysql模块, write the log to Mysql$template Remote, "/var/log/remote_log/% hostname%_%fromhost-ip%/log_%app-name%_% $YEAR%-% $MONTH%-% $DAY%.log "$template mysql-audit,"/var/log/remote_log/% app-name%/%hostname%_%fromhost-ip%_log_%app-name%_% $YEAR%-% $MONTH%-% $DAY%.log ": app-name,isequal," Mysql-audit "? mysql-audit&: ommysql:localhost,syslog,rsyslog,123.com #在前一行的日志匹配动作之后, continue inserting the log into Mysql:fromhost-ip,!isequal, " 127.0.0.1 "? remote& stop #结束前面的匹配信息, including Mysql-audit matches.

　　

Client

1. Install the latest version of Syslog

sudo apt-get install Software-properties-common python-software-propertiessudo add-apt-repository ppa:adiscon/ v8-stable sudo apt-get updatesudo apt-get install Rsyslog

2.rsyslog Configuration (Note If the state file configuration is not required after upgrading to 8.30.0)

Create Profile/etc/rsyslog.d/mysql-audit.conf#mysql-audit.logmodule (load= "Imfile" pollinginterval= "Ten") #加载模块input (type= "Imfile" file= "/data/mysqldata/mysql_audit.log" #定义文件位置                tag= "Mysql-audit" #打tag                #StateFile = "/var/spool/ Rsyslog/mysql-audit.state "#inotify status                severity=" error "#log级别                facility=" LOCAL7 ") #rsyslog level local7.* @ 10.25.109.64:514 #传送log服务器 #end

3, modify the syslog record, filter out the MySQL log, do not book the machine syslog

: App-name,isequal, "Mysql-audit" Stop*.*;auth,authpriv.none          -/var/log/syslog

4. Restart Rsyslog and set file permissions

Touch/var/spool/rsyslog/mysql-audit.statechown syslog.adm/var/spool/rsyslog/mysql-audit.stateusermod-g MySQL Syslog/etc/init.d/rsyslog restart

　　

Rsyslog Centralized collection of MySQL audit logs in Ubuntu