Syslog and MySQL

[Matt1911]

Hi,

wanted to know if anyone here has experience with logging to a MySQL Database. I want the syslog daemon to sent it's output to a MySQL database instead of /var/log/messages.

- which software to use (I don't think the syslog daemon can do this default)
- how about performance

kind regards,

Matthijs

[jasonlambert]

Performance will decrease, and then what happens if mysqld fails?

If you REALLY want to get syslog entries into mysql the best way todo it would be to write a script to parse the /var/log/messages file on an hourly basis and run INSERT queries for each of the entries, then clear the log file contents again.

EDIT: Alternativly write your own syslogd to listen for input on /dev/log and then write that to a mysqldb directly, or modify an existing syslogd code to do this.

Jason

[hchasqueira]

I use sqlsyslogd (http://www.frasunek.com/sources/security/sqlsyslogd/) to insert log messages in a MySQL database. There's also a PERL version in http://www.packetshack.org/index.php?page=sqlsyslogd.pl.

Basically it works like this:

• - Create a FIFO somewhere on your file system (mkfifo).
  - Configure syslog to write the messages you want to that FIFO
  - Run a script that keeps reading the FIFO and every time something shows up, call sqlsyslogd to store it in MySQL

I have this setup in a central log server, receiving logs from about 10 machines. It seems to never miss any messages. There are no problems with performance, just with storage space. You really want to filter the kind of messages that go into MySQL, or your tables will grow very fast.