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 am running a number 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 external database.

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

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

p.s. hope you appreciate my superb ASCII diagram'ing skills

share|improve this question
    
Thank you for accepting my answer. BTW nice ASCII display. –  RolandoMySQLDBA Nov 12 '12 at 16:22
    
I added additional info to my answer concerning error logs. –  RolandoMySQLDBA Nov 12 '12 at 21:55

2 Answers 2

up vote 2 down vote accepted

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:

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

share|improve this answer
    
hmz ... and then I can use replication to replicate logs to a central log database. Sounds good. Cheers. –  Katafalkas Nov 12 '12 at 16:17
    
And the error log ? –  Alain Collins Nov 12 '12 at 21:33
    
@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 Nov 12 '12 at 21:38
    
@AlainCollins Mission accomplished. I added the code I promised. –  RolandoMySQLDBA Nov 12 '12 at 22:18
    
@AlainCollins thanks for the vote of confidence. –  RolandoMySQLDBA Nov 12 '12 at 23:07

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.

share|improve this answer

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.