HOWTO setup syslog-ng to log to mysql

From Chaos

Jump to: navigation, search
This article is part of the HOWTO series.

Contents

Summary

This HOWTO explains how to setup syslog-ng to log to mysql. It also goes through the initial configuration of the following aspects:

Details

Prerequisites

1. You will need to download, compile, and install the following source packages:

2. This HOWTO also assumes that your syslog-ng, MySQL, and Apache+PHP services are all on the same host.

Setup MySQL (database, user, and tables)

1. The PHP front end has the database template for MySQL that you will need to use. It is called dbsetup.sql, and can be imported using the following syntax. Make sure to change the password fields in the SQL file before importing:

root@localhost:~# mysql -u root -p < dbsetup.sql

Setup the syslog-ng.conf file

1. I recommend changing the default source definition to something like the following (this will enable TCP and UDP syslog traffic to port 514 on the host to automatically be sent to MySQL. Change x.x.x.x to the IP of your system.): 

source src {
        unix-dgram("/var/run/log");
        unix-dgram("/var/run/logpriv" perm(0600));
        udp(
                ip(x.x.x.x)
                port(514)
        );
        tcp(
                ip(x.x.x.x)
                port(514)
        );
        internal();
        file("/dev/klog");
};

2. Please use something like the following for the f_debug filter: 

filter f_debug { level(debug); };

3. You will need to add a special destination template, something like the one below: 

destination d_mysql {
        pipe("/tmp/mysql.syslog-ng.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));
};

4. You'll need to add a logging statement like the one below. Since we want all levels (from debug to emergency) to be sent to MySQL, the filter we use is f_debug: 

log { source(src); filter(f_debug); destination(d_mysql); };

5. Go ahead and re-start syslog-ng. Make sure the FIFO pipe exists:

root@localhost:~# ls -lah /tmp/mysql.syslog-ng.pipe

6. If the FIFO does not exist, you might need to do this, and then restart syslog-ng:

root@localhost:~# mkfifo /tmp/mysql.syslog-ng.pipe

Setup the bash/sh script

1. The script I use is below. Put it somewhere easy to find, I keep mine in the same directory as syslog-ng.conf. 

#!/bin/sh
#
# File: syslogng-mysql-pipe.sh
#
# Take input from a FIFO and run execute it as a query for
# a mysql database.
#
# IMPORTANT NOTE:  This could potentially be a huge security hole.
# You should change permissions on the FIFO accordingly.
#

if [ -e /tmp/mysql.syslog-ng.pipe ]; then
        while [ -e /tmp/mysql.syslog-ng.pipe ]
                do
                        mysql -usyslog --password=mypasswordhere logs < /tmp/mysql.syslog-ng.pipe
        done
else
        mkfifo /tmp/mysql.syslog-ng.pipe
fi

2. You'll need to mark the script as executable (chmod +x syslog-ng-mysql-pipe.sh), and then run it. When you run it, you should use the full path, something like this

root@localhost:~# /usr/local/etc/syslog-ng/syslog-ng-mysql-pipe.sh &

Login to MySQL

1. At this point, you should be receiving data in your MySQL database/table (syslog/syslog). To check it, run the following set of commands. If nothing is returned in the SELECT statement, something is not setup correctly:

root@localhost:~# mysql -u syslog -p
mysql> use syslog;
mysql> SELECT * FROM logs LIMIT 5\G

Setup the PHP front-end

1. We now need to setup the PHP front end.
2. Uncompress the php-syslog-ng package somewhere on the system, I put mine in /usr/local/etc.
3. Modify the file config/config.php, and add your settings (at the very least, change DBUSER, DBUSERPW, DBADMIN, DBADMINPW, and DBHOST (if it's something other than localhost)). I also do not use authentication for the PHP front-end, so I set REQUIRE_AUTH to FALSE.

Setup Apache to serve the PHP front-end

1. All I did was in my default virtual host, add 2 Aliases like the ones below. This allows me to go to http://myhost.com/phpsyslogng, and see the PHP front-end: 

        Alias /phpsyslogng/ "/usr/local/etc/phpsyslogng/"
        Alias /phpsyslogng "/usr/local/etc/phpsyslogng/"

2. Restart Apache, and give it a whirl!

Maintenance

One of the harder things to get a hold of (especially when your database gets large, mine's at 66.4 million records and counting) is deleting old syslog entries, and keeping the search cache (if you are using the previously mentioned php-syslog-ng frontend) up to date. I have included steps for automating the php-syslog-ng search cache below.

After poking around php-syslog-ng a little more, I did find a logrotate function, which can be easily used to rotate your syslog DB once a month.

php-syslog-ng Search Cache

This was rather easy to setup. All I did was make a copy of index.php (I called it updatedb.php, and put it inside of the 'scripts' folder) and added the following 3 lines at the top, just after the opening php tag: 

if ($_SERVER['HTTP_USER_AGENT']) { exit; }
$_POST['pageId'] = 'config';
$_POST['configTask'] = 'reloadCache';

This will allow me to run a nightly cronjob: 

0 0 * * * cd /path/to/phpsyslogng && /usr/bin/php scripts/updatedb.php > /dev/null 2>&1

Which will update my search cache entries. The first line is to make sure people can't call the updatedb.php directly from the web and the second and third lines fill in the $_POST array to have valid data to perform the update.

php-syslog-ng Log Rotate

Inside of the 'scripts' directory, there is also a file called 'logrotate.php'. Similar to the 'updatedb.php' script above, you just need to call this once per month on the last day of the month in order to have your old records archived. 

55 23 * * * [ `date -d tomorrow +\%d` -eq '01' ] && cd /path/to/phpsyslogng && /usr/bin/php scripts/logrotate.php > /dev/null 2>&1

In the config.php for php-syslog-ng, there is a variable LOGROTATERETENTION. WARNING: you should definately comment out that value, as it will cause problems with your underlying all_logs mergelog table. You will see errors like this in MySQL and/or php-syslog-ng: 

Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

I also added [ `date -d tomorrow +\%d` -eq '01' ] to the crontab entry, since cron has a very hard time calculating the last day of the month. I figured I would let the date command do that for me. Note that you have to escape the '%' in the crontab entry, otherwise it won't run correctly (it will look like this: cron[20585]: (root) CMD ([ `date -d tomorrow +) rather than cron[25502]: (root) CMD ([ `date -d tomorrow +%d` -eq '01' ] && cd /path/to/phpsyslogng && /usr/bin/php scripts/logrotate.php > /dev/null 2>&1).

If you still want to be able to rotate your old logs, just run this command in a MySQL prompt:

See Also

My most mysql records

Retrieved from "http://chaos.untouchable.net/HOWTO_setup_syslog-ng_to_log_to_mysql"
Personal tools
Namespaces
Variants
Actions
Navigation
Sponsored Links
Toolbox