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                                                                        |

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