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.