LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 11-28-2008, 01:16 AM   #1
blancs
Member
 
Registered: Mar 2005
Posts: 49

Rep: Reputation: 15
Unhappy 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:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aclog |
| mysql |
+--------------------+
3 rows in set (0.02 sec)

mysql> use aclog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_aclog |
+-----------------+
| logs |
+-----------------+
1 row in set (0.00 sec)

mysql> DESC logs
-> \g
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| host | varchar(32) | YES | MUL | NULL | |
| facility | varchar(10) | YES | MUL | NULL | |
| priority | varchar(10) | YES | MUL | NULL | |
| level | varchar(10) | YES | | NULL | |
| tag | varchar(10) | YES | | NULL | |
| date | date | YES | MUL | NULL | |
| time | time | YES | MUL | NULL | |
| program | varchar(15) | YES | MUL | NULL | |
| msg | text | YES | | NULL | |
| seq | int(10) unsigned | NO | PRI | NULL | auto_increment |
+----------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

Connection id: 9
Current database: aclog
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 12 hours 20 min 47 sec

Threads: 1 Questions: 98 Slow queries: 0 Opens: 24 Flush tables: 1 Open tables: 18 Queries per second avg: 0.002
--------------
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.
 
Old 11-28-2008, 02:42 AM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,417

Rep: Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974
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?
 
Old 11-28-2008, 03:41 AM   #3
blancs
Member
 
Registered: Mar 2005
Posts: 49

Original Poster
Rep: Reputation: 15
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
 
Old 11-28-2008, 04:24 AM   #4
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,417

Rep: Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974Reputation: 1974
well for a start write somethign to the fifo.
 
Old 11-28-2008, 12:33 PM   #5
blancs
Member
 
Registered: Mar 2005
Posts: 49

Original Poster
Rep: Reputation: 15
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:
Database changed
mysql> DESC logs
-> \g
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| host | varchar(32) | YES | MUL | NULL | |
| facility | varchar(10) | YES | MUL | NULL | |
| priority | varchar(10) | YES | MUL | NULL | |
| level | varchar(10) | YES | | NULL | |
| tag | varchar(10) | YES | | NULL | |
| date | date | YES | MUL | NULL | |
| time | time | YES | MUL | NULL | |
| program | varchar(15) | YES | MUL | NULL | |
| msg | text | YES | | NULL | |
| seq | int(10) unsigned | NO | PRI | NULL | auto_increment |
+----------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> show logs;
Empty set, 1 warning (0.00 sec)

mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

Connection id: 24
Current database: aclog
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 23 hours 8 min 55 sec

Threads: 2 Questions: 4473 Slow queries: 0 Opens: 24 Flush tables: 1 Open tables: 18 Queries per second avg: 0.054
--------------
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.?
 
Old 11-29-2008, 07:32 PM   #6
blancs
Member
 
Registered: Mar 2005
Posts: 49

Original Poster
Rep: Reputation: 15
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.

Last edited by blancs; 11-29-2008 at 07:45 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
I need help getting syslog to log remotely, this is just the regular syslog. abefroman Linux - Software 2 06-05-2008 12:36 PM
mysql integrate syslog-ng program error raiux Programming 2 11-04-2007 06:02 AM
syslog-ng + mysql + firewall vortmax Linux - Server 1 10-06-2007 09:26 PM
syslog-ng destination mysql isuck@linux Linux - Software 2 04-30-2007 02:46 AM
LXer: Centralized Syslog Server Using syslog-NG LXer Syndicated Linux News 0 04-28-2006 07:21 PM


All times are GMT -5. The time now is 07:42 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration