how does syslog-ng connect to mysql 3

[jhala]

I'm running syslog-ng v 1.6.11 on RHEL 4. I can log to local files with no problem. I want to also log to a mysql database. I have a mysql database server with a syslog database configured to receive logs.

On the syslog-ng server, do I need mysql installed in order to make the mysql pipe work? From what I read, you need a script scheduled in cron to run every minute to make the mysql connection to send the data. So the script goes on the syslog-ng server, and therefore I need mysql installed on the syslog-ng server to initiate the connection? I thought maybe mysql was somehow built into syslog-ng and it can make the connection itself. If mysql is needed on the syslog-ng server, are there any parameters to pass with reference to syslog-ng while installing mysql?

Here's my syslog-ng.conf, let me know if I'm missing anything:[/color green]

source net { udp(); };

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

filter f_server { host("server"); };

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

To create the pipe I did:

mkfifo /tmp/mysql.pipe

Here's a script I found for sending data through the pipe:

#
# 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

Thanks in advance, your help is greatly appreciated.

 

[lgarner]

You need MySQL installed somewhere. You also need the MySQL client installed on the syslog host.

There's nothing special about the MySQL installation. You just need to create the database and table that you'll be using.

 

[jhala]

Thanks for the reply.

So there's nothing built into syslog-ng to make the mysql connection, and therefore I'm forced to have a script push the logs to the database?

 

[geirendre]

Maby this howto from Debian can help You:

http://www.debianhelp.co.uk/syslog-ng.htm

 

[lgarner]

As far as I know, syslog-ng has no database capability at all. You just "log" to the insert statements, and process them into the program of your choice.

 

[jhala]

I've read the Debian link, but I'm still having issues configuring the mysql connection. I ran the command:

mysql -u user --password="password" database_name

OR

mysql -u user database_name -p

I type the password and it kicks back the error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

On the mysql db server I created the syslog database and ran the command:

grant usage on *.* to user@ip_address_of_syslog_server;

Then I created the mysql account on the syslog server.

Am I missing something?

Thanks

 

[geirendre]

After granting rights, I think you need to flush privileges
on the DB.
>>>flush privileges;

Or restart the DB-engine (if it's running?)

#/etc/init.d/mysql status

#/etc/init.d/mysql restart

 

[thedaver]

jhala, if I understand your scenario, you are trying to use a client on the syslog_ng machine to talk to the database on a different mysql database server.

If that's correct, then your first commands are incorrect. The "socket" error is the mysql client complaining that there is no local copy of the mysql server found/reachable. Again, if the mysql database is on another server then this is obviously true.

What you need to do is modify your command line on the syslog_ng client to include the necessary server information.

You need to add "-h [remote server]" using either name-based or IP-based reference for the server (remove the brackets.

This is also contingent upon your mysql server listening on IP port 3306 (default may be off) and that your firewall(s) permit this port to be accessed.

You can check the port listening with "lsof -Pni" and looking. Can't help you with your firewalls.










D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[jhala]

Thanks! I can't believe I didn't realize that... lol, I was just specifying the db, so how could it know where the db was if I didn't specify the host!

Just did:

mysql -u 'user' --password='password' -h 'ip_of_mysql_db_server' 'database_name'

Also did a flush priv's.

Thanks again

 

[thedaver]

Keep in mind that by including your password in your command line as you are doing, your password is being written to your .bash_history

This is a security issue for some environments.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[geirendre]

Using LAMP to make a Syslog-ng server:

http://www.phpwizardry.com/php-syslog-ng.php

 

[jhala]

I have a syslog server relaying packets to the mysqldb server. Whenever I try to connect to the database I get the following:

# mysql -u root -p
Enter password:
ERROR 1040 (00000): Too many connections

I have the above script by Patrick Danu running in cron:
crontab -e -u root

@reboot /etc/syslog2mysql.sh
# A command required to transfer the data stored
# into the syslog_incoming table to the syslog table. (every 1 minute)
# For an unknown reason this is not done automatically by the syslog plugin.
*/1 * * * * /etc/syslog2mysql.sh

If I run the command lsof -Pni, I have about 100 established connections for mysqld ie.

mysqld 20564 mysql 274u IPv4 80620810 TCP IP_of_mysqldb_server:3306->IP_of_syslog_server:39741 (ESTABLISHED)

I then went to the my.cnf file on the mysqldb server and entered the line max_connections=250 under the mysqld heading, restarted mysql, but still getting the same message of too many connections.

Thanks

 

[thedaver]

If you query the tables on the mysql server for the syslog data, do you see any data being entered?

also, how many syslog daemons do you see on your logging machine?

Your cron job is launching every minute. If your connection time to transmit the log interval exceeds one minute then you likely begin to have a queue of working instances of that cronjob trying to complete their work. If they are not logging in correctly, are timing out, running long, etc. you will likely see connection exhaustion.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[jhala]

I do see data in the tables.

If I do a netstat -an from the mysqldb server I see many connections, maybe 28000 total! It seems they may be half open connections, or connections that just never finished or are finishing....

So as you say, they are getting queued up and taking time to finish.

And I'm guessing the same amount of daemons are running on the logging server... the 28000+.

Maybe I need to create multiple pipes, or maybe there's a way to limit the number of connections that can be made...

Thanks

 

[thedaver]

How about you back down the my.cnf setting to only 20 connections for mysql db.

Then you change the crontab entry from */1 to */5, assuming you can tolerate a 5 minute latency in logging - and, I presume, that syslog-ng can tolerate it.

Then, try to kill off all the spurious connections from the client machine (perhaps remove the line from crontab, killall syslog2mysql, and reenable the line in crontab)

Are you absolutely sure that you need to launch the syslog2mysql script every minute? Isn't there some resident/daemonized version of that process to avoid this kind of mess?

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[jhala]

I made the changes that you recommended, but I think there's a problem with the shell script because the established connections are not going away, just compounding...

tcp 0 0 ip_of_mysqldb_server:3306 ip_of_syslog_server:35074 ESTABLISHED

... instead of being a 'while' loop, maybe there should just be an 'if':

if [ ! -e /tmp/mysql.pipe ]
then
mkfifo /tmp/mysql.pipe
mysql -u mysql --password='secret' -h 'ip_of_mysqldb_server' syslog < /tmp/mysql.pipe >/dev/null
fi
done


When you do a redirection, does it only redirect a single line off the pipe or the entire pipe?

Thanks again.

 

[jhala]

yeah, that doesn't make sense... you have to have the while loop in there...

 

[geirendre]

From the Debianhelp site:

http://www.debianhelp.co.uk/syslog-ng.htm

Setup syslog-ng to MySQL pipe

An example for a script that feeds log entries from the FIFO pipe to MySQL is included in the scripts directory. The script is called syslog2mysql.sh.
#!/bin/bash

if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do
mysql -u syslogfeeder --password=PASS_HERE syslog < /var/log/mysql.pipe >/dev/null
done

If you decide to use this script then you have to replace PASS_HERE with the password for the syslogfeeder user. You will also probably want to have this script started automatically whenever you start the server. So add an entry in the inittab or start it through init.d (or whatever is appropriate on your system). But make sure you call it after MySQL has been started.

Now start the syslog2mysql.sh script:

shell> ./syslog2mysql.sh &

or if you created an init.d script:

shell> /etc/init.d/syslog2mysql start

 

[thedaver]

So if you use geirendre's script, there would be NO crontab entry at all!

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[jhala]

Thanks for the script.

I've tried this previously, and it just sits there and doesn't send any data to the mysqldb server.

On the syslog server, if I do
ps -ef | grep mysql it returns:

root 27707 27073 0 08:10 pts/1 00:00:00 /bin/bash ./syslog2mysql.sh
root 27708 27707 0 08:10 pts/1 00:00:00 /bin/bash ./syslog2mysql.sh

On the mysqldb server, if I do
netstat -an
I do not see any established connections from the syslog server.

I am also running tcpdump on the mysqldb server and I do not see any of the traffic coming to it.