Setup Rsyslog with MySQL on CentOS/RHEL 6/5

RSYSLOG is a super fast system to process logs and events. It provides a very efficient way to setup a centralized log server. This article will help you to save log files to the MySQL database.

See Also:

• HOW TO CONFIGURE LOGANALYZER WITH RSYSLOG AND MYSQL ON CENTOS/RHEL 7/6/5
• SETUP RSYSLOG WITH MYSQL ON CENTOS/RHEL 7

Step 1: Install MySQL, Apache and PHP (LAMP)

First we need to install LAMP on our server to setup rsyslog with MySQL. Use following commands to install required packages.

# yum install php php-mysql mysql-server httpd

After installing all the required packages start all services using following commands.

# service httpd start
# service mysqld start
# chkconfig httpd on
# chkconfig mysqld on 

Apply security on MySQL and also set root user password.

# mysqladmin -u root password 'Password' 
OR
# mysql_secure_installation 

Step 2: Install Rsyslog

By default rsyslog is installed on RHEL based system. If rsyslog is not installed user following commands to install rsyslog packeges.

# yum install rsyslog rsyslog-mysql

After installing start rsyslog service and make sure syslog is stopped on server.

# service syslog stop
# chkconfig syslog off
# service rsyslog start
# chkconfig rsyslog on 

Step 3: Configure Rsyslog Database

There is default sql script is available to create the database. Use mention command to create MySQL database.

# mysql -u root -p < /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql

After creating the rsyslog database, we need to create MySQL user with grant access for accessing database.

# mysql -u root -p
mysql> GRANT ALL ON Syslog.* TO 'rsyslog'@'localhost' IDENTIFIED BY 'Password';
mysql> FLUSH PRIVILEGES;
mysql> exit 

Step 4: Enable MySQL Module

Now we need to enable MySQL module using Rsyslog configuration file, Also update Rsyslog configuration with MySQL connection details.

vim /etc/rsyslog.conf

Uncomment the following lines.

 module(load="imudp") # needs to be done just once
 input(type="imudp" port="514")

 module(load="imtcp") # needs to be done just once
 input(type="imtcp" port="514")

Add following entries in configuration file.

$ModLoad ommysql
. :ommysql:127.0.0.1,Syslog,rsyslog,Password

Now restart Rsyslog service.

# service rsyslog restart

We have successfully configured Rsyslog with MySQL database server. Now, all the logs will save in Syslog database. If you want to view logs on web interface following below link.

Continue to Part 2 – Setup LogAnalyzer with Rsyslog MySQL

Reference: Python Official Website

Enjoy it!