Setup Rsyslog with MySQL and LogAnalyzer on CentOS/RHEL 6/5

Rsyslog is an enhanced version of Linux syslog utility. It provides a very efficient way to setup centralized log server in hosting environment. This article will help you to save log files to MySQL database with easy steps. If you have already configured centralized log server, then you can simply setup database on your central log server.

This article is created in two parts, In this page you will find setup of Rsyslog integration with MySQL database. In the next article helps you to install and integrate LogAnalyzer with this setup.

Step 1: Install MySQL, Apache and PHP

First we need to setup LAMP environment on our server to complete Rsyslog, MySQL and LogAnalyzer setup. Use following commands to install required packages.

# yum install php php-mysql mysql-server httpd

After installing all packages start all services using following commands.

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

On first time installing MySQL we need to setup MySQL root user password to secure MySQL server access.

# mysqladmin -u root password 'MYSQLPASSWORD'

Step 2: Install Rsyslog Packages

Rsyslog is default installed on RHEL based systems from RHEL 6 release. Install Rsyslog service at central logging system as well as client systems. Use following commands to install Rsyslog service in earlier version of RHEL/CentOS systems.

# 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: Create Rsyslog Database and User

Rsyslog provides an sql script to create database for Rsyslog in MySQL. Use script to create MySQL database and tables using following command.

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

Above command will create an database with name Syslog in MySQL. Now we need to create MySQL user for accessing database.

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

Step 4: Setup Rsyslog with MySQL

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

# vim /etc/rsyslog.conf

and add following entries in configuration file.

 $ModLoad ommysql
 *.* :ommysql:127.0.0.1,Syslog,rsyslog,MYSQLPASSWORD

after adding above lines and restart Rsyslog service.

# service rsyslog restart

At this stage we have successfully configured Rsyslog with MySQL database server. All the logs are now saving in Syslog database. If you want to view logs on web interface following below link.

Continue to Part 2 - Setup LogAnalyzer with Rsyslog MySQL