The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Database Discussions' started by mobcdi, May 28, 2012.

  1. mobcdi

    mobcdi Well-Known Member

    Joined:
    Jul 13, 2009
    Messages:
    109
    Likes Received:
    0
    Trophy Points:
    16
    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 ?
     
  2. mtindor

    mtindor Well-Known Member

    Joined:
    Sep 14, 2004
    Messages:
    1,279
    Likes Received:
    36
    Trophy Points:
    48
    Location:
    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
     
  3. mobcdi

    mobcdi Well-Known Member

    Joined:
    Jul 13, 2009
    Messages:
    109
    Likes Received:
    0
    Trophy Points:
    16
    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?
     
  4. mtindor

    mtindor Well-Known Member

    Joined:
    Sep 14, 2004
    Messages:
    1,279
    Likes Received:
    36
    Trophy Points:
    48
    Location:
    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
     
    #4 mtindor, May 28, 2012
    Last edited: May 28, 2012
  5. mobcdi

    mobcdi Well-Known Member

    Joined:
    Jul 13, 2009
    Messages:
    109
    Likes Received:
    0
    Trophy Points:
    16
    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
     
  6. mtindor

    mtindor Well-Known Member

    Joined:
    Sep 14, 2004
    Messages:
    1,279
    Likes Received:
    36
    Trophy Points:
    48
    Location:
    inside a catfish
    cPanel Access Level:
    Root Administrator
    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
     
  7. mobcdi

    mobcdi Well-Known Member

    Joined:
    Jul 13, 2009
    Messages:
    109
    Likes Received:
    0
    Trophy Points:
    16
    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
     
  8. mtindor

    mtindor Well-Known Member

    Joined:
    Sep 14, 2004
    Messages:
    1,279
    Likes Received:
    36
    Trophy Points:
    48
    Location:
    inside a catfish
    cPanel Access Level:
    Root Administrator
    You're welcome. Glad I could be of help !

    mike
     
Loading...

Share This Page