felipe-alfaro.org

This short article describes how to configure syslog-ng in order to store the logs into a MySQL backend. This adds more flexibility when performing log analysis, log searching and correlation.

Installing MySQL

MySQL can be compiled from source and installed using the FreeBSD ports collection:

# cd /usr/ports/databases/mysql41-server/
# make install distclean

Configuring MySQL

Use the following commands to create the MySQL database directory and install a configuration file:

# mkdir /var/db/mysql
# cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf

Optionally, edit /var/db/mysql/my.cnf to adjust some parameters like:

• Disabling networking support.

  This can be done by uncommenting the skip-networking option from the configuration file.

  This will prevent MySQL from listening on port 3306/tcp. Since we are using MySQL locally, we can use UNIX sockets instead of true networking.

• Reducing memory usage.

  By adjusting innodb_buffer_pool_size and innodb_additional_mem_pool_size to values suited to the amount of RAM available to FreeBSD

Next, add the following lines into /etc/rc.conf so that MySQL will get launched during system startup:

# MySQL
mysql_enable="YES"
mysql_limits="YES"
mysql_dbdir="/var/db/mysql"
mysql_args=""

Starting MySQL

Use the following command to start MySQL:

# /usr/local/etc/rc.d/mysql-server.sh start

Creating the MySQL database

The logs will get stored into a table named logs on database syslog.
To create the database and table, create a file named syslog.sql with the following SQL commands:

CREATE DATABASE syslog;
USE syslog;
CREATE TABLE logs (
	host varchar(32) default NULL,
	facility varchar(10) default NULL,
	priority varchar(10) default NULL,
	level varchar(10) default NULL,
	tag varchar(10) default NULL,
	timestamp datetime default NULL,
	program varchar(15) default NULL,
	msg text,
	seq int(10) unsigned NOT NULL auto_increment,
	PRIMARY KEY (seq),
	KEY host (host),
	KEY seq (seq),
	KEY program (program),
	KEY timestamp (timestamp),
	KEY priority (priority),
	KEY facility (facility)
);

Then, process those SQL commands using MySQL client:

# mysql -u root -p 
Setting up the communication channel
syslog-ng will issue INSERT INTO SQL commands into a UNIX pipe for every log received and processed. Those SQL commands will be retrieved from the UNIX pipe and will be injected into MySQL.
This UNIX pipe will act as the communication channel between syslog-ng and MySQL. To create the UNIX pipe:
# mkfifo /tmp/mysql.pipe

Also, we will create a startup script used to keep feeding SQL commands sent to the UNIX pipe to MySQL called /usr/local/etc/rc.d/040.mysql-syslog.sh:
( while [ -e /tmp/mysql.pipe ]
  do
        /usr/local/bin/mysql -u root --password= syslog 

This script will get invoked at startup and will keep feeding the SQL commands generated by the mysql syslog-ng destination into the MySQL database.
However, we must make sure this startup script is invoked after MySQL has been started. Thus, in FreeBSD I recommend renaming the MySQL startup script:
# mv /usr/local/etc/rc.d/mysql-server.sh \
     /usr/local/etc/rc.d/030.mysql-server.sh
Setting up syslog-ng
Modify /usr/local/etc/syslog-ng/syslog-ng.conf to add a new source called net used to retrieve logs via the network:
source net { udp(); };
Next, add a new destination for MySQL:
destination mysql { pipe("/tmp/mysql.pipe"
template("INSERT INTO logs (host, facility, priority, level, tag,
timestamp, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY',
'$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$PRORAM',
'$MSG' );\n") template-escape(yes) ); };

Finally, configure syslog-ng so all logging is sent to the MySQL destination. Since syslog-ng allows multiple destinations, this makes perfectly possible to keep locally-generated log events stored in text files alongside the MySQL database.

log { source(net); destination(mysql); };
log { source(src); destination(mysql); };


Finally, we send the SIGHUP signal to syslog-ng to instruct it to re-read its configuration file and reconfigure accordingly:
# pkill -HUP syslog-ng