Linux – Where to find the terminal logs in Mysql

For starters, I placed these lines in /etc/my.cnf

[mysqld]
log-output=TABLE
slow-query-log
slow-query-log-file=slow-queries.log

When you use the slow log with the log_output being TABLE, the table IS NOT created in /var/lib/mysql. The table is created in the mysql folder, /var/lib/mysql/mysql. The storage engine for the default table-based slow log is CSV. You can check this by doing the following:

use mysql
show tables;

You should see the table slow_log

MySQL> show create table 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.00 sec)

Here is how to convert the CSV file for the slow log table to MyISAM

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
SET GLOBAL slow_query_log = @old_log_state;

Keep in mind that the converted MyISAM does not have any indexes.

This is a column called 'start_time' which is a timestamp. Feel free to index it like this:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Let us know how this worked out, please !!!

It sounds like the mysql process (mysqld_safe, I believe) is unable to create the log file after you use logrotate to move it out of the way and send a HUP? If I'm way off here, let me know.

Assuming I'm not way off, there are a few options here:

• Move the log to a directory owned by the same user as the mysqld_safe process. For example, create a /var/log/mysqld/ directory and save the logfiles there. If the directory is mysql:mysql 700 then new files can be created without an issue.
• Use the copy/truncate logrotate method instead of the move/SIGHUP method. The copy/truncate method will copy the current log files (mysqld.slow.log) to a new file (mysqld.slow.log.1) then truncate the original file to zero bytes. This is useful if you don't want to interrupt the process that is writing to the log for some reason. The downside, of course, is that there is additional disk overhead with copying the original file to a new file before wiping the original file back to zero bytes. This is done by adding the copytruncate option to the logrotate stanza for that file and removing the no-longer-required postrotate section.