×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how does syslog-ng connect to mysql
3

how does syslog-ng connect to mysql

how does syslog-ng connect to mysql

(OP)
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:

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.

RE: how does syslog-ng connect to mysql

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.

RE: how does syslog-ng connect to mysql

(OP)
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?

RE: how does syslog-ng connect to mysql

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.

RE: how does syslog-ng connect to mysql

(OP)
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

RE: how does syslog-ng connect to mysql

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

RE: how does syslog-ng connect to mysql

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/

RE: how does syslog-ng connect to mysql

(OP)
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

RE: how does syslog-ng connect to mysql

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/

RE: how does syslog-ng connect to mysql

(OP)
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

RE: how does syslog-ng connect to mysql

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/

RE: how does syslog-ng connect to mysql

(OP)
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

RE: how does syslog-ng connect to mysql

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/

RE: how does syslog-ng connect to mysql

(OP)
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.

RE: how does syslog-ng connect to mysql

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

RE: how does syslog-ng connect to mysql

From the Debianhelp site:
http://www.debianhelp.co.uk/syslog-ng.htm

Quote:

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
smile

RE: how does syslog-ng connect to mysql

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/

RE: how does syslog-ng connect to mysql

(OP)
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.

RE: how does syslog-ng connect to mysql

Is your server doing anything that would generate log activity while you're monitoring?

I also would NOT expect you to see two (2) instances of syslog2mysql.sh running.  That would be nonsense.  Kill them both off and start over.

D.E.R. Management - IT Project Management Consulting
http://www.dermanagement.com/

RE: how does syslog-ng connect to mysql

(OP)
In addition to logging to the mysqldb server, I am logging locally as well.  And I see those logs being generated.  I have also verified with a tcpdump.

I don't understand why the second instance of the shell script is being generated.  I killed them both off, and just ran

./syslog2mysql.sh &

And the second instance keeps appearing...

Thanks

RE: how does syslog-ng connect to mysql

Um, are you sure syslog-ng permits multiple destinations for the same log data?  Perhaps that's the problem.  Local logging may trump an external logging.

D.E.R. Management - IT Project Management Consulting
http://www.dermanagement.com/

RE: how does syslog-ng connect to mysql

(OP)
damn, you're MONEY, thanks!

I thought I read a configuration that had both...

RE: how does syslog-ng connect to mysql

(OP)
Here's an update...

So I took out one of the destinations in the log line of the syslog-ng config.  
Restarted syslog-ng and it started working... and I only had one instance of the shell script in the process list...

Then I went back and added the second destination again,

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

restarted everything, and that also worked!  

So I'm logging to both the db and the local server.

Go figure...

Thanks again.

RE: how does syslog-ng connect to mysql

Syslog-ng can indeed have multiple destinations for the
same log data.
If you look at this examples
http://www.softpanorama.org/Logs/Syslog_ng/configuration_examples.shtml
you se how syslog-ng.conf is set up.

Basicaly it is made up of 3 parts:

1- sources: where to get log entries from (localy, process, IP-addr, TCP-port, UDP-port etc)

2- filters: what messages shall be let trough.

3- destinations: where to send the log datas.

You can have as many instances of these tre parts as you wanth.

Then comes the clever part. You tie these parts together in
a log-line, which is made up of source, filter(optional) and  destination.

You can reuse any source/filter/destination as many times
as you like.
So you can have 2 log-lines with the same source and filter,
but with different destinations.

http://sial.org/howto/logging/syslog-ng/#s2

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close