Category Archives: mysql

Configure Rsyslog with MySQL on centos 6/7

When build application logging is very important to trace and monitor system. One of most common solution for logging is rsyslog. In default Rsyslog will store log to file, log data to file has some disadvantages compare with database especially when amount data is huge.

Rsyslog provide plugin to work with MySQL, or mongo dB for store log. In this post I will guide how to configure Rsyslog with MySQL to make center logging server on centos 6/7.

On this post I assume that your centos box already install rsyslog and mysql.

1. Install rsyslog-mysql plugin

We run below command to install rsyslog mysql plugin.

sudo yum install rsyslog-mysql

2. Create database for rsyslog and user

Rsyslog provide script to create database and tables to store log. Use this script to create database.

mysql –u root –p </usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql

Now we already has a database name Syslog. We need create a user so that rsyslog can connect to mysql and use Syslog database (avoid using root user).

#mysql –u root –p
mysql> create user ‘syslog’@’localhost’ identified by ‘password_syslog’;
mysql> grant all on Syslog.* to ‘syslog’@’localhost’;

3. Setup Rsyslog with mysql

Now we need edit rsyslog configuration to make it work with mysql.
We need change file rsyslog.conf, to do that run below command.

sudo vi rsyslog.conf

we add below lines

$ModLoad ommysql
*.*       :ommysql:localhost,Syslog,syslog,password_syslog

We restart rsyslog so it can apply new changes

service rsyslog restart

Now we’ve already finished configure rsyslog to work with MySql also

how to configuration mysql to log slow query

In this tutorial, i will show you how to configure mysql to log mysql slow query. This is a important feature we always use when tuning application using mysql database.

We do following config in file my.cnf (in linux OS you can find it on /etc/my.cnf, if you using windows can check on this post to find this file):


slow_query_log to turn on log slow query
slow_query_log_file to configure file mysql will write log.

Import mysql:ERROR 2006 (HY000) at line 1197: MySQL server has gone away

1. Symptom

when import large file database into mysql you get follow error:

ERROR 2006 (HY000) at line 1197: MySQL server has gone away

2. Cause

You get above error cause mysql terminal connection between mysql client and mysql server cause reach to limit handle data transmission

3. Solution

Find mysql configuration file add and increase below parameter in [mysqld] section and restart mysql server:

For example: my.cnf