howto:syslog-ng_to_mysql [SQLS Wiki]

Introduction

I assume you know what syslog is and why you have it. This will out line taking the syslog messages and putting them into a MySQL database. This was configured on a Gentoo linux system. There may be some minor differences if you are using a different dristibution.

Requirements

* Linux * Syslog-NG * MySQL

Database creation

I'm not going into a lot of detail here. You need to create a database and a table for the syslog records to go into. You may modify this as you need but a very basic setup that would match this configuration is shown below.

host mysql # mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1733
Server version: 5.0.44-log Gentoo Linux mysql-5.0.44

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database syslog;
Query OK, 1 row affected (0.00 sec)

mysql> use syslog;
Database changed
mysql> 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, datetime datetime default NULL, program varchar(15) default NULL, msg text, seq bigint(20) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY program (program), KEY datetime (datetime), KEY priority (priority), KEY facility (facility) ) TYPE=MyISAM;

Query OK, 0 rows affected (0.00 sec)
mysql>

——————————————————————————-

Configure Syslog-NG

First, create the pipe with the below command.

mkfifo /var/log/mysql.pipe

Now edit the /etc/syslog-ng/syslog-ng.conf file. You need to add the mysql destination and choose what will be logged to it. In the example below all syslog messages will be sent to MySQL and the /var/log/messages log file. Note that there exists –user and –password options for MySQL. You need to specifiy a user/password that has access to write to the database. You might create a mysql user “syslog” and give it insert only access.

<pre>

source all { 
	unix-stream("/dev/log"); 
	pipe("/proc/kmsg"); 
	internal();
	udp(); 
	tcp();  
};
destination d_mysql { 
	program("/usr/bin/mysql --user=USERNAME --password=PASSWORD syslog < /var/log/mysql.pipe"); 
	pipe ("/var/log/mysql.pipe" template("INSERT INTO logs (host, facility, priority, level, tag, datetime, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC','$PROGRAM', '$MSG' );\n") template-escape(yes)); 
};
log { source(all); destination(d_mysql); };

</pre> Now lets modify Syslog-NG's init script so it starts up -after- MySQL. Edit the file /etc/init.d/syslog-ng and find the -need- line inside the -depend- section. Add mysql to the end of it. Here an example of my config file.

depend() {
	# Make networking dependency conditional on configuration
	case $(sed 's/#.*//' /etc/syslog-ng/syslog-ng.conf) in
		*source*tcp*|*source*udp*|*destination*tcp*|*destination*udp*)
			need net ;;
	esac

	need clock hostname localmount mysql
	provide logger
}

If you have not already. Setup Syslog-NG to start at boot.

rc-update add syslog-ng default

Now start/restart Syslog-NG

/etc/init.d/syslog-ng restart

——————————————————————————-

Database Maintenance

Now your MySQL databse could get really big over time. Really big. So you might want some system that will prune the records in that database. You can create a cron job that checks the database and removes records older then any time you specifiy.

Troubleshooting

If the mysql process is not being started by syslog-ng then open your syslog.conf, copy out the program line and try it in your shell, eg:

/usr/bin/mysql –user=syslog –password=PASSWORD syslog < /var/log/mysql.pipe

you will see an error, or if it works then you should just be put to a blank line, ctrl+c to end it.

Finally, if you seriously need help. You can go to my Contact tab and send me an E-Mail and I'll try to help you. But please provide all the details of your problem, error messages, linux distribution you have, versions, etc.. I may or may not answer! Or I might take a day or two to do so.