Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS). I found information that output error log to syslog.

[mysqld_safe]
syslog

But I want to use syslog for logging general logs and slow query logs. Please advise me how to write config file?

I could not find how to do that in the reference manual.

http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html

share|improve this question
    
I am sure many have asked this question. Therefore, you a get +1 for revealing this quesiton publicly. –  RolandoMySQLDBA Aug 5 '11 at 22:07

3 Answers 3

up vote 2 down vote accepted

That mechanism is entirely different from the OS syslog.

Setting log-output can be set either to TABLE, FILE (default), or NONE

if you use this

[mysqld]
log-output=TABLE

This will cause the logging for the general log and/or slow log to go to a CSV file. You can convert that CSV to MyISAM as follows:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;

You can then let this file grow tremendously and you will have to purge the table every so often. Here is how to purge the general_log table and keep the last 3 days:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
INSERT INTO mysql.general_log_new
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY;
DROP TABLE mysql.general_log;
ALTER TABLE mysql.general_log_new RENAME mysql.general_log;
SET GLOBAL general_log = @old_log_state;

What about the syslog (var/log/messages) ? You must script that yourself. First, you either need this:

[mysqld]
log
log-ouput=TABLE,FILE
general-log-file=/var/log/mysql-general.log

if you want to collect the general log in both formats or

[mysqld]
log
general-log-file=/var/log/mysql-general.log

for just the file format.

Now make a script to collect changes to /var/log/general.log. The script should look a lot like this:

NEWCOUNT=`wc -l < /var/log/mysql-general.log`
if [ -f /tmp/general-log-lines.txt ]
then
    OLDCOUNT=`cat /tmp/general-log-lines.txt`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        DIFF=`echo ${NEWCOUNT}-${OLDCOUNT}|bc`
        tail -${DIFF} < /var/log/mysql-general.log >> /var/log/messages
        echo ${NEWCOUNT} > /tmp/general-log-lines.txt
    fi
fi

Run this script every minute. I recommend truncating the general log every midnight like this

echo -n > /var/log/mysql-general.log

Give it a Try !!!

share|improve this answer

In /etc/my.cnf, set it.

[mysqld]
general-log-file = /path/to/mysql/dir/mysql-general.log

Edit /etc/rsyslog.conf (RHEL/CentOS) file, and enable module imfile to read /path/to/mysql/dir/mysql-general.log and then, send it to remote syslog server, respecting an interval configured in the PollingInterval parameter.

This section should look like this below:

module(load="imfile" PollingInterval="1")

input(type="imfile"
    File="/path/to/mysql/dir/mysql-general.log"
    stateFile="statefile-mysql-general"
    Tag="mysql-general"
    Severity="warning"
    Facility="local1")

Uncoment the line with WorkDirectory

$WorkDirectory /var/lib/rsyslog # where to place spool files

And configure it to send all logs remotely

*.* @@remote-ip:514 ## For TCP Connection

Or

 *.* @remote-ip:514 ## For UDP Connection
share|improve this answer

This is MUCH more simply done this way:

cd to mysql folders:

mkfifo mysql-general.log

in my.cnf tell it:

[mysqld]
general-log-file = /path/to/mysql/dir/mysql-general.log

Then, configure your syslog/syslog-ng to read the FIFO pipe and do with it as it will..

In my case, I pipe it across net to a centralized server with only the error logs and slow query logs however.

In situations where you want to also keep local copy, just set it to output to table and file as described above.

share|improve this answer
    
That method sound intriguing. One just must prepare their network for torrents of traffic because everything and its grandmother lands in the general log. This would definite suit enterprise installations that stringently check every command and every userid that logs in. This is a +1 !!! –  RolandoMySQLDBA Aug 27 '11 at 20:51
1  
fifo's also have the unfortunate tendency to freeze any listening programs if they are not being written too. So, if MySQL stops writing to this fifo, then syslog may also stop. –  Stefan Lasiewski Oct 9 '13 at 22:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.