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.

Access Logs for MySQL

Discussion in 'Database Discussions' started by santrix, Jul 29, 2011.

  1. santrix

    santrix Well-Known Member

    Joined:
    Nov 30, 2008
    Messages:
    223
    Likes Received:
    2
    Trophy Points:
    18
    Can someone point me in the right direction... I'm trying to find the config options to turn on some logging for mysql in /etc/my.cnf

    Currently we have a pretty vanilla my.cnf that is created by cPanel. I would like to be able to at least log connections, and also be able to log queries (preferably independently).

    The standard my.cnf already has the following entries (in amongst the rest):

    Code:
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    and

    Code:
    [mysqld]
    log_slow_queries=/var/log/mysql.slow-queries.log
    but neither of those log files exist in /var/log - is this normal? Do they need touching and chmodding to get them working? This is a production server that has been running for months so I would have expected to see something by now.

    Any help?
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hi santrix,

    Which MySQL version are you using? If you are using MySQL 5.0, then you need to create the log file and you need to use the right /etc/my.cnf syntax:

    Code:
    log-slow-queries=/var/log/mysql.slow-queries.log
    Code:
    touch /var/log/mysql.slow-queries.log
    chmod 660 /var/log/mysql.slow-queries.log
    chown mysql:mysql /var/log/mysql.slow-queries.log
    You won't receive any logs in /var/log/mysqld.log unless you restart MySQL in safe mode. The normal error log is at /var/lib/mysql/hostname.err location where hostname will be the fully qualified hostname for the machine (you can see the fully qualified hostname by issuing "hostname -f" command in root SSH).

    As for logging connections and queries, you are only entering slow queries, those that take longer than 10 seconds to run, by enabling the slow query log. If you want all queries logged, you'll need to enable the general log. This is the variable to use for it:

    Code:
    log=/var/log/mysql.general.log
    If you wanted to call the log file mysql.general.log and again you would do these commands to create it with the proper permissions and ownership:

    Code:
    touch /var/log/mysql.general.log
    chmod 660 /var/log/mysql.general.log
    chown mysql:mysql /var/log/mysql.general.log
    The above would not be the case if you are using MySQL 5.1, since to enable the general and slow query logs, you'd simply input these variables into /etc/my.cnf file:

    Code:
    general_log
    slow_query_log
    This will automatically create files at /var/lib/mysql called hostname.log and hostname-slow.log where hostname is the short hostname for the machine.

    I do want to note that the general log becomes very large very quickly. I would not suggest creating it unless you really need that information. If you do believe that information is necessary to obtain, then I highly suggestion periodically archiving the file using logrotate to do so.

    Finally, the above entries being added to /etc/my.cnf file will require a restart of MySQL. Please ensure to create the log files before restarting MySQL so they can write to those log files.
     
  3. santrix

    santrix Well-Known Member

    Joined:
    Nov 30, 2008
    Messages:
    223
    Likes Received:
    2
    Trophy Points:
    18
    Jackpot! Thanks Tristan, that's really helpful. :)
     
Loading...

Share This Page