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.

Mysql logs

Discussion in 'Workarounds and Optimization' started by capoinfra, Jul 9, 2010.

  1. capoinfra

    capoinfra Well-Known Member

    Joined:
    Oct 25, 2007
    Messages:
    119
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Uruguay
    Hello,

    I can not see any mysql log, and the logs are enabled in /etc/my.cnf

    Here's the copy of the file:

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    local-infile=0
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=600
    interactive_timeout=20
    wait_timeout=10
    connect_timeout=10
    thread_cache_size=128
    #key_buffer=16M
    key_buffer=200M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=1536
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    max_connect_errors=10
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=8
    myisam_sort_buffer_size=64M

    [mysql.server]
    user=mysql
    #basedir=/var/lib

    [mysqld_safe]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    Any idea?

    Thanks in advance.-
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,453
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    bin files? Are they recent or old? Is it possible you had this in your my.cnf at one time? log-bin=mysql-bin
    If so you might clear them with this:
    expire_logs_days = 0

    restart MySQL.

    Just guessing here of course. I've done this one myself.
     
    #2 Infopro, Jul 9, 2010
    Last edited: Jul 9, 2010
  3. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    There are quite a few really old settings in your /etc/my.cnf file. When you mention you don't see any MySQL logs, which logs are you talking about? The general and slow query logs don't automatically log and only the error logs do.

    You don't need several of the settings you have in your /etc/my.cnf due to the reason they are already set by default in MySQL for these:

    Code:
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-locking is now called skip-external-locking and it's already set by default (see this location for a discussion). The datadir is already /var/lib/mysql and the socket file is already /var/lib/mysql/mysql.sock

    Also, thread_concurrency doesn't exist on Linux MySQL so that variable serves no purpose in /etc/my.cnf file (see this location for a discussion on how thread_concurrency only exists on Solaris systems).

    Next, this err-log isn't even a variable for mysql itself for logging errors, this is only when running with mysqld_safe:

    Code:
    [mysqld_safe]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    The actual MySQL error log itself automatically logs to /var/lib/mysql and should be saving in /var/lib/mysql/hostname.err where hostname is your server's fully qualified domain name hostname. If you wanted to specify a different name or location for the error log, under the [mysqld] section (not the [mysqld_safe] section), you would put:

    Code:
    log-error=/var/lib/mysql/error.log
    Or whatever you wanted for the name of the error log. You would have to create the file with the right permissions and ownership:

    Code:
    cd /var/lib/mysql
    touch error.log
    chmod 660 error.log
    chown mysql:mysql error.log
    Again, though, it automatically saves in /var/lib/mysql/hostname.err anyway without setting it in /etc/my.cnf so there's no point to add log-error line unless you really want to change the log's name or location for some reason.

    If you want the general or slow query logs to save as well, you can enable them using the following depending on what MySQL version you are using:

    MySQL 4.1 and MySQL 5.0

    In /etc/my.cnf put the following for the general log:

    Code:
    log=/var/lib/mysql/general.log
    In /etc/my.cnf put the following for the slow log:

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    Now, you'd have to create the log files:

    Code:
    cd /var/lib/mysql
    touch general.log
    touch slow.log
    chmod 660 general.log
    chmod 660 slow.log
    chown mysql:mysql general.log
    chown mysql:mysql slow.log
    Restart MySQL

    Code:
    /etc/init.d/mysql restart
    MySQL 5.1

    In /etc/my.cnf put the following for the general log:

    Code:
    general_log
    In /etc/my.cnf put the following for the slow log:

    Code:
    slow_query_log
    Restart MySQL:

    Code:
    /etc/init.d/mysql restart
    Under MySQL 5.1, if you have the general_log and slow_query_log lines in /etc/my.cnf file, then the log files create automatically without having to create them in /var/lib/mysql

    They will have the names /var/lib/mysql/hostname.log (general log) and /var/lib/mysql/hostname-slow.log (slow query log) where hostname is your machine's fully qualified domain hostname.

    The general logs can grow quite large as they will log all database activity, so a cron to clear the log periodically might be needed. There is a log rotation script at /usr/share/mysql/mysql-log-rotate on most systems that you could modify to put the correct paths to the log (it defaults to /var/lib/mysql/mysqld.log as the general log name so you'd need to change it to /var/lib/mysql/general.log or /var/lib/mysql/hostname.log depending on what MySQL version you are using).

    Of note, I would advise being careful about getting variables online from guides. I've seen a lot of people with these incorrect variables on their MySQL 4.1, 5.0 and 5.1 machines all due to using guides that haven't been updated since 2004 or 2005.
     
    #3 Miraenda, Jul 10, 2010
    Last edited: Jul 10, 2010
Loading...

Share This Page