Huge mysql.log file

[airserv]

Hello all! First, I must admit that I don't know much about MySQL (sorry in advanced) but have used MS SQL in the past (so all is not lost) and have dabled in Linux in the past (built out Gentoo from source WAAAAY back in college).

Here is the back storry:
We are having some odd issues. Our virtual Nagios XI instance has been, shall we say, cranky. From dropped packets (haven't been able to figure that one out) to high CPU to full disk, we seem to be chasing our tails. Recently we ran out of disk space and implemented the steps identified in the forums to expand the VMDK. No issues there. HOWEVER, the /var/log/mysql.log file has gained water weight and is consuming 16.3/27 GB of my available disk (I've already updated log rotation to compress logs).

Now, I've tried to see if there is a MySQL tool to complress the logs, but I haven't been able to even administer the MySQL instance since I don't have usr/pw (I didn't do the initial setup and we don't have the info logged anywhere). Tail produces some odd errors (see below). Nagios it's self (including DB access) appears to be working without any issues.

[root@noc log]# tail mysqld.log
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed
111019 14:59:18 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_timedeventqueue' is marked as crashed and last (automatic?) repair failed

So what can I do? Am I missing something obvious?

Any help is MUCH appreciated!

EDIT: the file name is mysqld.log and not mysql.log

[nscott]

This is the doc on how to do it, let us know if it fixes your problem:

http://assets.nagios.com/downloads/nagi ... tabase.pdf

[airserv]

Same result. mysqld.log's tail is better (see below). As a general question, is this log ever purged?

[root@noc log]# tail mysqld.log
111019 15:27:24 [Note] /usr/libexec/mysqld: Shutdown complete

111019 15:27:24 mysqld ended

111019 15:29:07 mysqld started
111019 15:29:07 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
111019 15:29:07 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
111019 15:29:07 InnoDB: Started; log sequence number 0 43655
111019 15:29:07 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

[mguthrie]

Same result. mysqld.log's tail is better (see below). As a general question, is this log ever purged?

Good question. If it is I'm guessing it's handled by mysql, but it's something we should probably look into...

The repair procedure should take care of any new log flooding from here.

[airserv]

Ok. Got the large file solved by making a new file (setup permissions to reflect mysqld.log), stopping mysql, swapped new/old file names, and restart mysql. (Yes this approach is like killing a single fly with C4, but it worked).

NOTE: the mysqladmin flush-logs command did NOT flush my logs when I ran it. Don't know why at this point.

There is a way to automatically rotate the logs but I haven't tested it.

http://adminlinux.blogspot.com/2009/09/ ... ation.html

[nscott]

Thanks for the link airserv.