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

• RolandoMySQLDBA: I added additional info to my answer concerning error logs.

Answer 1

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

• Katafalkas: hmz ... and then I can use replication to replicate logs to a central log database. Sounds good. Cheers.
• Alain Collins: And the error log ?
• RolandoMySQLDBA: @AlainCollins The last link in my answer (Jun 30, 2011) gives the principle code on how to get chunks of text logs and copy it elsewhere. Tell you what: I put such code in my answer.
• RolandoMySQLDBA: @AlainCollins Mission accomplished. I added the code I promised.

Answer 2

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.