rsyslog將日志記錄于MySQL中

數據庫服務器：CentOS7 192.168.119.159

日志：CentOS6 192.168.119.129

1. 準備mysql數據庫 和 用戶賬戶

[root@localhost ~]# yum install mariadb-server

[root@localhost ~]# systemctl start mariadb

[root@localhost ~]# ss -ntl

State ??????Recv-Q Send-Q Local Address:Port ??????????????Peer Address:Port

LISTEN ?????0 ?????50 ???????????????*:3306 ?????????????????????????*:*

2. 在mysql server上授權rsyslog能連接至當前服務器

授權用戶

MariaDB [Syslog]> grant all on Syslog.* to syslog@’%’ identified by ‘1234’;

CentOS測試連接

[root@localhost ~]# mysql -h 192.168.119.159 -usyslog -p1234

Welcome to the MariaDB monitor. ?Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 5.5.56-MariaDB MariaDB Server

3. 在rsyslog服務器上安裝mysql模塊相關的程序包

[root@localhost ~]# yum info rsyslog-mysql

Description : The rsyslog-mysql package contains a dynamic shared object that will add

: MySQL database support to rsyslog.

[root@localhost ~]# yum install rsyslog-mysql

Installed:

rsyslog-mysql.x86_64 0:5.8.10-10.el6_6

4. 為rsyslog創建數據庫及表

[root@localhost ~]# rpm -ql rsyslog-mysql

/lib64/rsyslog/ommysql.so 模塊

/usr/share/doc/rsyslog-mysql-5.8.10

/usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql 腳本

腳本，需要在mysql的機器上執行，可以先復制到遠程主機，也可以創建賬戶，遠程連接執行

復制到遠程主機

[root@localhost ~]# scp /usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql 192.168.119.159:

遠程主機查看

[root@localhost ~]# ll

-rw-r–r–. 1 root root 1046 Feb ?1 11:06 createDB.sql

執行命令

本機執行

[root@localhost ~]# mysql < createDB.sql

或者

MariaDB [(none)]> source createDB.sql

或者遠程連接執行

遠程連接執行

mysql -usyslog?-h192.168.119.159?-p1234?< /usr/share/doc/rsyslog-7.4.7/mysql-createDB.sql

查看

MariaDB [Syslog]> show databases;

+——————–+

| Database ??????????|

+——————–+

| Syslog ????????????|

MariaDB [Syslog]> show tables;

+————————+

| Tables_in_Syslog ??????|

+————————+

| SystemEvents ??????????|

| SystemEventsProperties |

+————————+

5. 配置rsyslog將日志保存到mysql中

CentOS6修改配置文件

[root@localhost ~]# vim /etc/rsyslog.conf

$ModLoad ommysql

*.info;mail.none;authpriv.none;cron.none ???????????????/var/log/messages 本機的

*.info;mail.none;authpriv.none;cron.none ???????????????:ommysql:192.168.119.159,Syslog,syslog,1234

重啟服務

[root@localhost ~]# service rsyslog restart

CentOS6觸發日志

[root@localhost ~]# logger “This is a rsyslog mysql test”

查看本機的日志

[root@localhost ~]# tail -f /var/log/messages

Feb ?1 11:20:51 localhost root: This is a rsyslog mysql test

查看遠程的日志

[root@localhost ~]# mysql -h192.168.119.159 -usyslog -p1234

Welcome to the MariaDB monitor. ?Commands end with ; or \g.

Your MariaDB connection id is 6

Server version: 5.5.56-MariaDB MariaDB Server

MariaDB [(none)]> use Syslog;

MariaDB [Syslog]> select Message from SystemEvents;

+—————————————————————————————————–+

| Message ??????????????????????????????????????????????????????????????????????????????????????????? ?|

+—————————————————————————————————–+

| imklog 5.8.10, log source = /proc/kmsg started. ???????????????????????????????????????????????????? |

| ?[origin software=”rsyslogd” swVersion=”5.8.10″ x-pid=”2965″ x-info=”http://www.rsyslog.com”] start |

| ?This is a rsyslog mysql test ????????????????????????????????????????????????????????????????????? ?|

+—————————————————————————————————–+