How to Centralize Linux Logs with rsyslog, MySQL, and Loganalyzer

Why Use Logging

In production environments a comprehensive logging system is needed to monitor host services and operations. Large networks often use ELK, but for small‑to‑medium architectures rsyslog is sufficient to collect and query logs for real‑time traffic analysis.

Article Goal

Use rsyslog to store logs from two hosts into a MySQL database and install Loganalyzer to display the MySQL logs via an httpd+php front‑end.

Experiment Environment

Hostnames and IPs:

syslog.anyisalin.com – 192.168.2.2 – log collection, MySQL

www.anyisalin.com – 192.168.2.3 – web server

server1.anyisalin.com – 192.168.2.4 – regular use

All hosts have SELinux and iptables disabled.

Experiment Steps

rsyslog Configuration on syslog Host

Execute the following on syslog.anyisalin.com:

[root@syslog ~]# yum install mysql-server rsyslog-mysql -y
Loaded plugins: fastestmirror
Setting up Install Process

    #省略
Installed:
  rsyslog-mysql.x86_64 0:5.8.10-10.el6_6
  mysql-server.x86_64 0:5.1.73-5.el6_7.1

Complete!

Enable UDP input and MySQL output in /etc/rsyslog.conf:

$ModLoad imudp
$UDPServerRun 514
$ModLoad ommysql

Replace the default log file destination with MySQL:

%s@./var\/log.*@:ommysql:192.168.2.2,Syslog,syslog,passwd@gi

Restart rsyslog:

[root@syslog ~]# service rsyslog restart
Shutting down system logger:          [  OK  ]
Starting system logger:               [  OK  ]

Create a MySQL user for rsyslog and grant privileges:

mysql> GRANT ALL ON Syslog.* TO 'syslog'@'%' IDENTIFIED BY 'passwd';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Import the rsyslog schema:

[root@syslog ~]# mysql -usyslog -ppasswd < /usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql

rsyslog Configuration on Server1 Host

On server1.anyisalin.com, modify /etc/rsyslog.conf to forward logs to the syslog host: %s@./var\/log.*@\@192.168.2.2@gi Restart rsyslog:

[root@server1 ~]# service rsyslog restart
Shutting down system logger:          [  OK  ]
Starting system logger:               [  OK  ]

Verify by querying the MySQL database on the syslog host:

mysql> select * from SystemEvents\G;
*************************** 1. row ***************************
ID: 1
CustomerID: NULL
ReceivedAt: 2016-03-23 17:28:21
DeviceReportedTime: 2016-03-23 17:28:21
Facility: 0
Priority: 6
FromHost: syslog
Message: imklog 5.8.10, log source = /proc/kmsg started.
...

Web Server Configuration

On www.anyisalin.com, install Apache, PHP, and required extensions:

[root@www ~]# yum install php-gd httpd php php-mysql -y | tail -n 10
 ...
Complete!

Extract Loganalyzer and configure it:

[root@www ~]# tar xf loganalyzer-3.6.4.tar.gz -C /var/www/html/
[root@www ~]# cd /var/www/html/
[root@www html]# cp -a loganalyzer-3.6.4/src/ log
[root@www html]# cp -a loganalyzer-3.6.4/contrib/*.sh log/
[root@www log]# chmod +x *.sh
[root@www log]# ./configure.sh
[root@www log]# ./secure.sh
[root@www log]# chmod 666 config.php

Start Apache:

[root@www log]# service httpd start
Starting httpd: httpd: Could not reliably determine the server's fully qualified domain name, using www.anyisalin.com for ServerName   [  OK  ]

Open a browser, navigate to the Loganalyzer installation page, follow the wizard, and finish the setup. After installation, the web interface displays logs from multiple hosts in a formatted, searchable view.

Conclusion

The resulting system provides an intuitive, visually appealing way to view logs without dealing with complex command‑line tools.