將rsyslog取得的log儲存至MySQL , step by step

1.安裝rsyslog-mysql (參考http://tecadmin.net/setup-rsyslog-with-mysql-and-loganalyzer/)

yum install rsyslog rsyslog-mysql

2.建立rsyslog使用的MySQL帳號

CREATE USER 'syslog'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'syslog'@'localhost';

3.建立rsyslog使用的database

create database syslogdb;
grant all on syslogdb.* to 'syslog'@'127.0.0.1' identified by 'password';

4.建立紀錄log的table

CREATE TABLE mysyslog

(

       ID int unsigned not null auto_increment primary key,

       ReceivedAt datetime NULL,

       DeviceReportedTime datetime NULL,

       FromHost varchar(60) NULL,

       FromIP varchar(60) NULL,

       ProgramName varchar(60) NULL,

       SyslogFacilityText  varchar(60) NULL,

       Message text,

       Facility smallint NULL,

       Priority smallint NULL   

);

5.編輯rsyslog設定檔 (/etc/rsyslog.conf)

增加設定

#load module

$ModLoad ommysql

#define template

$template mysqlTpl,"insert into mysyslog (ReceivedAt,DeviceReportedTime,FromHost,FromIP,ProgramName,SyslogFacilityText,Message,Facility,Priority) values ('%timegenerated:::date-mysql%','%timereported:::date-mysql%','%HOSTNAME%','%fromhost-ip%','%programname%','%syslogfacility-text%','%msg%',%syslogfacility%,'%syslogpriority%')",SQL

#send log to mysql

*.* :ommysql:127.0.0.1,syslogdb,syslog,password;mysqlTpl

ps.自訂template可使用的欄位請參考http://www.rsyslog.com/doc/property_replacer.html

6.重啟rsyslog

service rsyslog restart