How to Setup Rsyslog with MySQL

Rsyslog is a powerful log management server for Linux that allows you to store, manage and view logs from various applications. In this article, we will look at how to setup Rsyslog with MySQL database server. It is basically an advanced version of syslog system in Linux. This is a 2-article series where we also cover how to setup Loganalyzer with Rsyslog and MySQL in the next article.

How to Setup Rsyslog with MySQL

Here are the steps to setup Rsyslog with MySQL.

1. Install Apache, MySQL & PHP

We need to install Apache, MySQL & PHP to be able to run both rsyslog and loganalyzer on our system. So open terminal and run the following commands.

# yum install php php-mysql mysql-server httpd

Run the following commands to start and enable Apache server & MySQL to auto start on system reboot.

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

If you are installing MySQL for first time, then you need to setup root password to secure MySQL installation.

# mysqladmin -u root password 'MYSQLPASSWORD'

2. Install Rsyslog packages

Rsyslog is already installed in RHEL 6+ systems. However, you can also install it with the following command.

# yum install rsyslog rsyslog-mysql

Once rsyslog is installed, run the following command to stop syslog and start rsyslog service.

# service syslog stop
# chkconfig syslog off

# service rsyslog start
# chkconfig rsyslog on

3. Create Rsyslog database user

Rsyslog will need a MySQL database for storing its data. It already provides a script that automatically creates the required database with tables for Rsyslog. You can run it with the following command.

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

Please update the name of your rsyslog folder as per your system. We have used the version 4.8.10. You can easily find full path of the createDB.sql script file using the following command

# find / -name createDB.sql

The above command will create a database named syslog. Next create database user with the following command. Replace PASSWORD with your database password of your choice.

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

4. Setup Rsyslog with MySQL

Next, we edit Rsyslog configuration file and enable MySQL module in Rsyslog. We also need to provide it with MySQL database user’s details for auto login. Open Rsyslog file in text editor.

# vim /etc/rsyslog.conf

Add the following lines to it. Replace PASSWORD with database password of your choice.

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

Save and close the file. Restart Rsyslog with MySQL. You will see that all MySQL logs are saving in Rsyslog.

# service rsyslog restart

That’s it. Now all your MySQL logs will be stored in syslog server. In our next article, we will cover how to install Loganalyzer with Rsyslog & MySQL so that you can easily.

How to Setup Rsyslog with MySQL