Setup Rsyslog with MySQL on CentOS/RHEL 7

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

See Also:

SETUP RSYSLOG WITH MYSQL ON CENTOS/RHEL 6/5

Step 1: Add MySQL Repository

First we need to add the MySQL repository to install MySQL packages. Use mention command to add MySQL repository.

# rpm -Uvh http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

Step 2: 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.

# systemctl start httpd
# systemctl start mysqld
# systemctl enable httpd
# systemctl enable mysqld 

Apply security on mysql and also set root user password.

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

Step 3: 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.

# systemctl stop syslog
# systemctl disable syslog
# systemctl start rsyslog
# systemctl enable rsyslog 

Step 4: 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 5: 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: Rsyslog Official Website

Enjoy it!