Redirecting MySQL errors and slow logs into another database

Question

I am running a number of MySQL servers as slaves and masters. It is quite annoying to look for slow logs and errors in every server, so I would like to log all the errors into an external database.

    DB1    DB2    DBn ...
      \      |      /
       \     |     /          <-- error logs and slow logs
        \    |    /
[MySQL error & slow log database]

Is there some simple way of doing it?
Or I do need to write some scripts and stuff?

P.S. I hope you appreciate my superb ASCII diagraming skills

score 3 · Accepted Answer · edited Apr 13 '17 at 12:42

Have you ever considered converting the slow log into a MyISAM table?

As for MySQL 5.1.30, you can choose the format of the slow log and general log as either TABLE or FILE by means of the log_output option. It is also available in MySQL 5.5.

To support the TABLE option for log_output, MySQL provided two tables:

mysql.slow_log
mysql.general_log

For the sake of this question, I will discuss only mysql.slow_log

By default, this is mysql.slow_log:

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.10 sec)

mysql>

The slow_log table uses the CSV storage engine. Please note that a CSV table cannot be indexed. That's not very useful for doing analysis by date and time. GOOD NEWS: You can convert it to a MyISAM table.

In order to facilitate a MyISAM-based slow log, perform the following:

Step01) Make mysql.slow_log a MyISAM table

ALTER TABLE mysql.slow_log ENGINE-MyISAM;

Step02) Index to timestamp column called start_time

ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Step03) Add the following lines to the my.cnf (or my.ini)

[mysqld]
slow-query-log
log-output=TABLE

Step04) Restart MySQL

For Linux

service mysql restart

or for Windows

net stop mysql
net start mysql

Step05) Run the following command in MySQL

mysql> SHOW VARIABLES LIKE 'long_query_time';

By default long_query_time is 10 seconds

Step06) Run this command

mysql> SELECT SLEEP(15);

If long_query_time is 10 seconds, this SELECT should deliberately be placed in mysql.slow_log.

Step07) Run this command

mysql> SELECT sql_text FROM mysql.slow_log;

If you see any row in the table with the SELECT SLEEP(15);, CONGRATULATIONS !!!

You can now run SELECT queries against mysql.slow_log to copy some or all of the rows to other tables or other databases.

I have posted past answers in conjunction with this subject:

Mar 30, 2012 : MySQL slow log configuration
Nov 18, 2011 : Create a custom log table in MySQL
Jun 30, 2011 : How do I output MySQL logs to syslog?

UPDATE 2012-11-12 17:00 EDT

In all fairness, the question did ask about error logs. I did not explicitly include anything about it. I will include some code that addresses how to

Let's say the error log is defined on any DB server as follows:

[mysqld]
log-error=/var/log/mysqld.log

You could create a script like this:

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo ${NEWCOUNT} > ${LOG_LINES}
    fi
fi

or if you want to collect and reset the error log without a mysql restart (linux only) you can use this (This technique could never be done in Windows because the Windows OS locks the error log file from external writing):

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo -n > ${ERROR_LOG}
        echo 0 > ${LOG_LINES}
    fi
fi

Place this script on every DB server. Setup a crontab for this script to go off every 5 minutes. Make sure ERROR_FOLDER is set to a common shared folder.

UPDATE 2012-11-12 17:24 EDT

For clarification, running MySQL FLUSH LOGS; is insufficient because...

For all text-based logs, FLUSH LOGS; closes and reopens. No rotation is provided by mysqld
For binary logs, FLUSH LOGS; will...
- Close current binary log
- Rotate to a new binary log
- Open new binary log
For MySQL 5.5, FLUSH ERROR LOGS; closes and reopens the error log. No rotation is provided

This is why some scripting is necessary

score -1 · Answer 2 · answered Nov 09 '12 at 17:53

The slow logs are just text files, so there's no MySQL way of dealing with them.

The general process is to move/rename the files and then do a MySQL FLUSH, which will tell MySQL to start a new log file.

Then take the moved/renamed file and ship it off to your log server. Seems like a simple bash script would work.

Hope that helps.

Redirecting MySQL errors and slow logs into another database

2 Answers2

UPDATE 2012-11-12 17:00 EDT

UPDATE 2012-11-12 17:24 EDT

Linked