Php-Syslog-ng

cf http://www.phpwizardry.com/php-syslog-ng.php
This installation is done based on the proposed setup in Syslog.

Install

In Apache node

Get http://www.phpwizardry.com/php-syslog-ng/phpsyslogng-2.8.tar.gz
Untar it in /usr/local/share/phpsyslogng
Manage the config file the Debian way: move config/config.php to /etc/phpsyslogng/config.php and make a symlink
In config.php, change at least those lines and assign your passwords:

define('DBUSERPW', 'aaaa');
define('DBADMINPW', 'cccc');

And in our case as the sql is running on a separate node:

define('DBHOST', 'sql'); 

Create /etc/apache2/sites-available/phpsyslogng with:

Alias /syslog /usr/local/share/phpsyslogng
<Directory /usr/local/share/phpsyslogng>
 Options FollowSymLinks
</Directory>

Reload apache2 and go to http:// ... /syslog you will get a help page for the installation process.

Another step is to install the scripts/logrotate.php script.
First adapt the $APP_ROOT to your install, here /usr/local/share/phpsyslogng, then

ln -s /usr/local/share/phpsyslogng/scripts/logrotate.php /etc/cron.daily/phpsyslogng-logrotate

In Mysql node

Take scripts/dbsetup.sql from php-syslog-ng
But adapt some lines to our situation:

# create users
INSERT INTO user (Host, User, Password) VALUES ('private','sysloguser', password('aaaa'));
INSERT INTO db (Host, Db, User) VALUES ('private','syslog','sysloguser');

INSERT INTO user (Host, User, Password) VALUES ('mail','syslogfeeder', password('bbbb'));
INSERT INTO db (Host, Db, User) VALUES ('mail','syslog','syslogfeeder');

INSERT INTO user (Host, User, Password) VALUES ('private','syslogadmin',password('cccc'));
INSERT INTO db (Host, Db, User) VALUES ('private','syslog','syslogadmin');
COMMIT;
FLUSH PRIVILEGES;

# grant rights to user syslogadmin for backup purpose
GRANT USAGE ON syslog.* TO syslogadmin@private;
GRANT ALL ON syslog.* TO syslogadmin@private;
GRANT RELOAD ON *.* TO syslogadmin@private;

REVOKE ALL PRIVILEGES ON syslog.* FROM sysloguser@private;
GRANT USAGE ON syslog.* TO sysloguser@private;
GRANT SELECT ON syslog.* TO sysloguser@private;
GRANT UPDATE ON syslog.users TO sysloguser@private;

REVOKE ALL PRIVILEGES ON syslog.* FROM syslogfeeder@mail;
GRANT USAGE ON syslog.* TO syslogfeeder@mail;
GRANT INSERT ON syslog.* TO syslogfeeder@mail;

GRANT ALL ON syslog.search_cache TO sysloguser@private;
GRANT SELECT ON syslog.user_access TO sysloguser@private;
GRANT ALL ON syslog.user_access TO syslogadmin@private;
GRANT SELECT ON syslog.actions TO sysloguser@private;
GRANT ALL ON syslog.actions TO syslogadmin@private;

Then use it:

mysql -uroot -p < dbsetup.sql

Now you should be able to login to your http:// ... /syslog with account admin/admin
But you will face an error since there is no entry yet in your db.

In Syslog node

Add to /etc/syslog-ng/syslog-ng.conf the example given in scripts/syslog.conf:

destination d_mysql {
   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));
}; 

And the log section but with our additional source:

log {
   source(s_all);
   source(net);
   destination(d_mysql);
};

Get inspired by scripts/syslog2mysql.sh to make an init.d script:

• add option "-h sql.vlan2" to mysql client to connect to the remote host
• save the password in a secured file instead of leaving it on the cmd line of mysql, e.g. /etc/syslog-ng/my.cnf with strich access rights (600 root.root)
• script is based on /etc/init.d/skeleton but much more tricky to track PIDs of processes as mysql client is not a daemon!

[client]
password="bbbb"

The init.d script itself, to be stored in /etc/init.d/syslog2mysql

#! /bin/sh
# Author: Philippe Teuwen

# Do NOT "set -e"

PATH=/usr/sbin:/usr/bin:/sbin:/bin
DESC="Fetch queries from syslog-ng to mysql db"
NAME=syslog2mysql
PIDFILE=/var/run/$NAME.pid
SCRIPTNAME=/etc/init.d/$NAME

# Exit if mysql client is not installed
[ -x "/usr/bin/mysql" ] || exit 0

# Load the VERBOSE setting and other rcS variables
[ -f /etc/default/rcS ] && . /etc/default/rcS

# Define LSB log_* functions.
# Depend on lsb-base (>= 3.0-6) to ensure that this file is present.
. /lib/lsb/init-functions

#
# Function that starts the daemon/service
#
do_start()
{
        [ ! -e /var/log/mysql.pipe ] && echo " (Creating $NAME pipe)." && mkfifo /var/log/mysql.pipe
        if [ -e $PIDFILE ]; then
            if ps -p $(cat $PIDFILE) >/dev/null; then
                echo -n -e "\nError: $NAME seems to be already running!"
                return 1
            else
                rm -f $PIDFILE
            fi
        fi
        {
            while [ -e /var/log/mysql.pipe ]
            do
                mysql --defaults-file=/etc/syslog-ng/my.cnf -u syslogfeeder -h sql.vlan2 syslog < /var/log/mysql.pipe >/dev/null
                sleep 1
            done
        } &
        echo $! > $PIDFILE
}

#
# Function that stops the daemon/service
#
do_stop()
{
        if [ -e $PIDFILE ]; then
            PID=$(cat $PIDFILE)
            if ps -p $PID >dev/null; then
                # get PID of mysql child
                CPID=$(pgrep -P $PID)
                # kill syslog2mysql script
                kill $PID
                # kill mysql child
                kill $CPID
                rm -f $PIDFILE
                return 0
            else
                echo -e "\nWarning: $NAME was not running."
                echo -n -e "\nCleaning PID file"
                rm -f $PIDFILE
                return 1
            fi
        else
            echo -n -e "\nWarning: $NAME was not running"
            return 1
        fi
}

case "$1" in
  start)
        [ "$VERBOSE" != no ] && log_daemon_msg "Starting $DESC" "$NAME"
        do_start
        case "$?" in
                0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
                2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
        esac
        ;;
  stop)
        [ "$VERBOSE" != no ] && log_daemon_msg "Stopping $DESC" "$NAME"
        do_stop
        case "$?" in
                0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
                2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
        esac
        ;;
  restart|force-reload)
        #
        # If the "reload" option is implemented then remove the
        # 'force-reload' alias
        #
        log_daemon_msg "Restarting $DESC" "$NAME"
        do_stop
        case "$?" in
          0|1)
                do_start
                case "$?" in
                        0) log_end_msg 0 ;;
                        1) log_end_msg 1 ;; # Old process is still running
                        *) log_end_msg 1 ;; # Failed to start
                esac
                ;;
          *)
                # Failed to stop
                log_end_msg 1
                ;;
        esac
        ;;
  *)
        echo "Usage: $SCRIPTNAME {start|stop|restart|force-reload}" >&2
        exit 3
        ;;
esac

:

And create the links to run the script at startup:

update-rc.d syslog2mysql defaults