How to Create a centralized log in server with php-syslog-ng and mysql

How to Create a centralized log in server

Configure syslog-ng to log to mysql database using fifo template /etc/syslog-ng/sylog-ng.conf:

destination d_mysql {
pipe("/tmp/mysql.pipe"
template("INSERT INTO logs (host, facility, priority, level, tag, date,
time, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL','$TAG',
'$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n") template-escape(yes));
};

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

Comment out the following line

#source src { unix-dgram("/dev/log"); internal(); };

Uncomment out the following lines

source src { unix-dgram("/etc/log/log"); internal(); };
source net { udp(); };

Create the fifo pipe for syslog-ng to export out logs

mkfifo /tmp/mysql.pipe

Create syslog database

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,
date date default NULL,
time time 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 time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)
) TYPE=MyISAM;

Create the fifo pipe for syslog-ng to export out logs

mkfifo /tmp/mysql.pipe

Restart syslog-ng process

Stop syslog-ng

/etc/init.d/syslog-ng stop

Start syslog-ng

/etc/ini.d/syslog-ng start

Pipe Insert scripts

# Created by Matthias Buch
#

In the syslog-ng.conf we use:
destination d_oracle {
pipe("/dev/ora.pipe"
template("INSERT INTO logs (LL_HOST, LL_facility, LL_priority, LL_level, LL_tag,
LL_DATE, LL_program, LL_msg) VALUES ( '$HOST', '$FACILILITY', '$PRIORITY',
'$LEVEL', '$TAG',
to_date('$YEAR.$MONTH.$DAY $HOUR:$MIN:$SEC', 'yyyy.mm.dd hh24:mi:ss'),
'$PROGRAM', substr('$MSG',1,511));\n COMMIT;\n") template-escape(yes));

This script is used to pipe syslog-ng to mysql

#
# Created by Tadghe Patrick Danu
#
#!/bin/bash

if [ -e /tmp/mysql.pipe ]; then
while [ -e /tmp/mysql.pipe ]
do
mysql -u theuserid --password=thepassword syslogdb < /tmp/mysql.pipe done else mkfifo /tmp/mysql.pipe fi 

CronJob

#Rotate logs once a month

0 0 1 * *  /var/www/php-syslog-ng/scripts/logrotate.php

@reboot           /muysql #the name is right  is th script that pipes the local logs to mysql.