Syslog-ng+Rsyslog收集日志：写入数据库MySQ, MS-SQL,SQLite, mSQL（六）

23fwerw

为了统计方便，我们要从日志中选择一些消息放到数据库。对数据库读写支持要在编译时就要加上参数，还要在配置文件中开启对应的模块。模块如果很多监控都需要到数据库模块，可以放到/etc/rsyslog.conf全局配置文件里，如果只是某个监控收集用到那就放到/etc/rsyslog.d/的对应局部配置文件里。

1.编译。

1	./configure --enable-mysql

2.模块、生成的模板。

1 2 3 4 5

ommysql # mysql输出模块 ompgsql # PostgreSQL的输出模块 omlibdbi # 通用数据库输出模块(Firebird/Interbase,MS-SQL,Sybase,SQLite,Ingres,Oracle,mSQL) $template insertpl,"insert into SystemEvents (Message, Facility, FromHost, FromIP, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', '%fromhost-ip%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL

3.使用
用法：local4.* :ommysql:server:port,yourdb,yourname,yourpass;
例子：local4.* :ommysql:127.0.0.1:3306,yourdb,yourname,yourpass;
4.全局配置文件示例。

1	vi /etc/rsyslog.conf    //编辑rsyslog全局文件

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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95

# rsyslog v5 configuration file # For more information see /usr/share/doc/rsyslog-*/rsyslog_conf.html # If you experience problems, see http://www.rsyslog.com/doc/troubleshoot.html #### MODULES #### $ModLoad imuxsock #provides support for local system                     #logging (e.g. via logger command)#Unix套接字，包括系统日志socket $ModLoad imklog   # provides kernel logging support(previously done by rklogd) #module(load"immark")  # provides --MARK-- message capability #mysql输出模块 $ModLoad ommysql #PostgreSQL的输出模块 $ModLoad ompgsql #通用数据库输出模块(Firebird/Interbase,MS SQL,Sybase,SQLite,Ingres,Oracle,mSQL) $ModLoad omlibdbi # Provides UDP syslog reception # UDP传输，只在rsyslog作服务器时候使用 #$ModLoad imudp #$UDPServerRun 514 #Provides TCP syslog reception # TCP传输，只在rsyslog作服务器时候使用 #$ModLoad imtcp # tcp接收信息的端口 #$InputTCPServerRun 514 #增加数据库插入语句 $template insertpl,"insert into SystemEvents (Message, Facility, FromHost, FromIP, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', '%fromhost-ip%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL #### GLOBAL DIRECTIVES #### # Use default timestamp format $ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat # File syncing capability is disabled by default. This feature is usually not required, # not useful and an extreme performance hit #$ActionFileEnableSync on # Include all config files in /etc/rsyslog.d/ #模块化局部配置文件存放，默认加载全部.conf后缀的文件 $IncludeConfig /etc/rsyslog.d/*.conf #### RULES #### # Log all kernel messages to the console. # Logging much else clutters up the screen. #kern.*     /dev/console # Log anything (except mail) of level info or higher. # Don't log private authentication messages! *.info;mail.none;authpriv.none;cron.none                /var/log/messages # The authpriv file has restricted access. authpriv.*  /var/log/secure # Log all the mail messages in one place. mail.*      /var/log/maillog # Log cron stuff cron.*      /var/log/cron # Everybody gets emergency messages *.emerg     :omusrmsg:* # Save news errors of level crit and higher in a special file. uucp,news.crit    /var/log/spooler # Save boot messages also to boot.log local7.*    /var/log/boot.log # ### begin forwarding rule ### # The statement between the begin ... end define a SINGLE forwarding # rule. They belong together, do NOT split them. If you create multiple # forwarding rules, duplicate the whole block! # Remote Logging (we use TCP for reliable delivery) # # An on-disk queue is created for this action. If the remote host is # down, messages are spooled to disk and sent when it is up again. #$WorkDirectory /var/lib/rsyslog # where to place spool files #$ActionQueueFileName fwdRule1 # unique name prefix for spool files #$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible) #$ActionQueueSaveOnShutdown on # save messages to disk on shutdown #$ActionQueueType LinkedList   # run asynchronously #$ActionResumeRetryCount -1    # infinite retries if host is down # remote host is: name/ip:port, e.g. 192.168.0.1:514, port optional #*.* @@remote-host:514 # ### end of the forwarding rule ### #日志如非本地存储，需指定远程收集日志服务器IP:Port *.*  :ommysql:192.168.1.251:514    #表示所有类型的日志都提交

重启日志服务，修改完配置文件一定要重启服务才能生效

1	# service rsyslog restart