LinuxQuestions.org - syslog-ng + mysql?

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - syslog-ng + mysql? (https://www.linuxquestions.org/questions/linux-newbie-8/syslog-ng-mysql-686781/)

syslog-ng + mysql?

I'm pretty new to both sylog-ng and mysql, but more so to mysql.

I am trying to pipe my game server logs to mysql so I can then do some stuff in php. I pretty much followed the directions found at http://vermeer.org/docs/1. Problem is nothing is going into the db from what I can tell. Here is what my syslog-ng file looks like:

Code:

source s_all {

        # message generated by Syslog-NG

        internal();

        # standard Linux log source (this is the default place for the syslog()

        # function to send logs to)

        unix-stream("/dev/log");

        # messages from the kernel

        file("/proc/kmsg" log_prefix("kernel: "));

        # use the following line if you want to receive remote UDP logging messages

        # (this is equivalent to the "-r" syslogd flag)

        # udp();

};

--

destination df_assaultcube { file("/var/log/ac/us1.log"); };

--

destination d_ACmysql {

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));

};

--

filter f_assaultcube {facility(local6); };

--

#local6.*                              -/var/log/assaultcube.log

log {

        source(s_all);

        filter(f_assaultcube);

        destination(df_assaultcube);

};

--

#local6.*                              -mySQL

log {  source(s_all);

        filter(f_assaultcube);

        destination(d_ACmysql);

};

Here is what my database and tables look like:

Quote:

The script I am useing to pipe everything is:

Code:

#

# 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

if logging to a flat log file logs usually look like this:

Quote:

Nov 28 01:11:52 AssaultCube local[30022]: 8 quilleur CLA 0 -1 0 normal *.*.*.*
Nov 28 01:11:52 AssaultCube local[30022]:
Nov 28 01:11:52 AssaultCube local[30022]: Team RVSF: 4 frags
Nov 28 01:11:52 AssaultCube local[30022]: Team CLA: 3 frags
Nov 28 01:11:52 AssaultCube local[30022]: Status at 28-11-2008 01:11:52: 9 remote clients, 11.7 send, 1.7 rec (K/sec)
Nov 28 01:11:52 AssaultCube local[30022]: Time remaining: 8 minutes for team one shot, one kill game, mastermode 0.
Nov 28 01:11:53 AssaultCube local[30011]: [*.*.*.*] arbo stole the flag
Nov 28 01:11:54 AssaultCube local[30011]: [*.*.*.*] LOCO!!!! fragged nobel_home
Nov 28 01:11:54 AssaultCube local[30011]: [*.*.*.*] nobel_home fragged LOCO!!!!
Nov 28 01:11:54 AssaultCube local[30022]: [*.*.*.*] MICHAEL fragged {PRO}Cheif

I was starting to think it is the variables being passed in the syslog-ng insert statement, but i have no clue. Can anyone enlighten me, i need it.

you need to break things down, there are far too many parts of the process listed here for us to troubleshoot. can you use your piping script to squirt stuff into mysql, for example?

ha ha, yea I kinda just wanted to throw everything i had right out there.

here is the script i am acttually using after being edited to match the db setup.

Code:

#

# Created by Tadghe Patrick Danu

#

#!/bin/bash



if [ -e /tmp/mysql.pipe ]; then

while [ -e /tmp/mysql.pipe ]

do

mysql aclog < /tmp/mysql.pipe

done

else

mkfifo /tmp/mysql.pipe

fi

I am not sure how I could test that. But if I run the script, then do a `ps x` I can see that the mysql command is being called.

Quote:

root@justice:~/ac# ./ac2mysql.sh &
[1] 13711
root@justice:~/ac# ps x
PID TTY STAT TIME COMMAND
1 ? Ss 0:00 init [2]
3831 ? Rs 0:00 sshd: root@pts/1
3833 pts/1 Ss 0:00 -bash
13711 pts/1 S 0:00 -bash
13712 pts/1 S 0:00 mysql aclog
13747 pts/1 R+ 0:00 ps x
19979 ? Ss 0:17 /sbin/syslog-ng -p /var/run/syslog-ng.pid

well for a start write somethign to the fifo.

Well here is what I have tried and the results:

Quote:

root@justice:~/ac# echo "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', 'hello world' );\n" >> /tmp/mysql.pipe
root@justice:~/ac# PAGER set to stdout

root@justice:~/ac# echo "INSERT INTO logs (msg) VALUES ('helloworld');" >> /tmp/mysql.pipe
root@justice:~/ac# echo "I INTO logs (msg) VALUES ('helloworld');" >> /tmp/mysql.pipe
root@justice:~/ac# ERROR 1064 (42000) at line 857: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'I INTO logs (msg) VALUES ('helloworld')' at line 1

above i tried inserting what is being done from the syslog-ng config, after I just tried a very simple insert of msg with no errrors back, then i tried sending bad syntax to it to see what type of error I would get. looked like the pipe was working correctly.

looking at the db after:

Quote:

Thats me trying to see if anything has changed, I could not see any new rows, but maybe I don't know the proper syntax since I have never used mysql commandline before.?

also:

Quote:

mysql> SELECT COUNT(*) FROM logs;
+----------+
| COUNT(*) |
+----------+
| 31718 |
+----------+
1 row in set (0.00 sec)

********

ha ha I guess this is working, howerver it seems to crash alot.