Large General log file: How to clean up safely and stop it reoccuring

mobcdi

Well-Known Member
Jul 13, 2009
109
0
66
In /var/lib/mysql I have a hostname.log file which is over 20GB
my.cnf is set to use it as the general log with another used for slow queries.

What can I do to reduce the size and stop it (or a combination of smaller files) growing so large in the future ?
 

mtindor

Well-Known Member
Sep 14, 2004
1,417
82
178
inside a catfish
cPanel Access Level
Root Administrator
From command line -- bold items are things you type to turn the general log off and verify that it is off.

# mysql
mysql> set global general_log = 0;
mysql> show variables where variable_name = 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.00 sec)

mysql> quit
#

Unless you're troubleshooting something, in my opinion there is no reason to keep the general log turned on. I would only turn it on if I were troubleshooting an issue, and then I'd turn it off when I was finished. I would suppose that it would improve performance with it off, especially if it's currently writing to a 20 GB logfile.

After you've turned off the general log, just delete that log from /var/lib/mysql

Mike
 

mobcdi

Well-Known Member
Jul 13, 2009
109
0
66
Hi Mike,

Is there a way to find out whats filling it up or is the general log as the name suggested used for everything. I wouldn't like to end up turning off the general log and missing some important details later now. Is it possible to check the logging level and set it to a lower threshold or rotate the log after a given number of days so I have some historic data if the day came I needed it?
 

mtindor

Well-Known Member
Sep 14, 2004
1,417
82
178
inside a catfish
cPanel Access Level
Root Administrator
If the general log is enabled, you're going to get everything logged. If that's what you really want, and you simply want to rotate it out, then you can use the mysql-log-rotate script. I don't know of a way to narrow what gets logged. You can log slow queries. You can log all queries (the general log). Or you can log no queries. You can simply look at what's in the general log using something like less / more. less /var/lib/mysql/servername.log, assuming you are familiar with how less | more works.

I'll tell you what is filling up the general log -- Every query made to the server is filling it up. If you have a lot of mysql activity, ALL of those queries are being logged to the general log if you have it enabled. I really see no reason to log routine queries. If you do, then read on.

On a typical RH-based system, you should find a mysql-log-rotate script that'd likely be located at /usr/share/mysql/mysql-log-rotate . It may also already exist as /etc/logrotate.d/mysql. If it does, you probably need to edit it since currently it is set to rotate out a logfile called /var/lib/mysql/mysqld.log.

For instance, my /etc/logrotate.d/mysql file is something like this:

Code:
# This logname can be set in /etc/my.cnf
# by setting the variable "err-log"
# in the [safe_mysqld] section as follows:
#
# [safe_mysqld]
# err-log=/var/lib/mysql/mysqld.log
#
# If the root user has a password you have to create a
# /root/.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password = <secret> 
# user= root
#
# where "<secret>" is the password. 
#
# ATTENTION: This /root/.my.cnf should be readable ONLY
# for root !

/var/lib/mysql/servername.log /var/lib/mysql/servername-slow.log {
        # create 600 mysql mysql
        notifempty
        daily
        rotate 3
        missingok
        compress
    postrotate
        # just if mysqld is really running
        if test -x /usr/bin/mysqladmin && \
           /usr/bin/mysqladmin ping &>/dev/null
        then
           /usr/bin/mysqladmin flush-logs
        fi
    endscript
}
So I'd first check and see if you already have mysql set to rotate in /etc/logrotate.d. If you do, you'll just need to adjust the actual logfiles specified. And you can set different parameters in /etc/logrotate.d/mysql so that it compresses the file after it rotates it, etc. You should read the man page for logrotate and you should read the following info regarding MySQL logfile rotation [especially if you are using the binary log for replication]:

MySQL :: MySQL 5.0 Reference Manual :: 5.2.5 Server Log Maintenance

Mike
 
Last edited:

mobcdi

Well-Known Member
Jul 13, 2009
109
0
66
Is the global variable general_log separate from the my.cnf logging value or do I need both "On" for general logging to work? I had general and slow query logging enabled in my.cnf but have now only slow query logging and restarting mysqld didn't stop the general log from being created until I removed it from the my.cnf file
 

mtindor

Well-Known Member
Sep 14, 2004
1,417
82
178
inside a catfish
cPanel Access Level
Root Administrator
Is the global variable general_log separate from the my.cnf logging value or do I need both "On" for general logging to work? I had general and slow query logging enabled in my.cnf but have now only slow query logging and restarting mysqld didn't stop the general log from being created until I removed it from the my.cnf file
I'm not a MySQL guru. You should really consult the MySQL documentation. It's very useful. Of course, if you wait long enough, somebody with definitive knowledge will likely answer that question for you here. So far all of the answers I've provided came straight from the MySQL docs.

Mike
 

mobcdi

Well-Known Member
Jul 13, 2009
109
0
66
Guru or not you were a great help.

After turning off the global variable and removing the massive file I tested it by re-enabling it and a general log was created automatically even though my.cnf didn't specify a general log file. So i disabled it again and will leave it off unless I really need it.
Thanks again for the help
 

mtindor

Well-Known Member
Sep 14, 2004
1,417
82
178
inside a catfish
cPanel Access Level
Root Administrator
Guru or not you were a great help.

After turning off the global variable and removing the massive file I tested it by re-enabling it and a general log was created automatically even though my.cnf didn't specify a general log file. So i disabled it again and will leave it off unless I really need it.
Thanks again for the help
You're welcome. Glad I could be of help !

mike