Error Log and Slow Query Log in MySQL

Error log and Slow query log in MySQL

      

      1>    Error Log:

   ·         For Enable logs need to change in my.cnf file as follow:

      root@server# vi  /etc/mysql/my.cnf

                Or
      root@server# vi  /etc/my.cnf

   ·         Define error log with log-error in both [mysqld_safe] and [mysqld] section in my.cnf file

          [mysqld_safe]

          log-error=/var/log/mysql/error.log

          [mysqld]

          log-error=/var/log/mysql/error.log

   ·         Save file and Restart mysql service

      2>    Slow Query Log:

    ·     First thing need to check whether “Slow Query Log” is enable or not. For that access MySql and try  to execute following command.

         root@server# mysql –u root –p

         password:

         mysql> show variables like '%slow%';

+---------------------+---------------------------------+

| Variable_name       | Value                           |

+---------------------+---------------------------------+

| log_slow_queries    | OFF                             |

| slow_launch_time    | 2                               |

| slow_query_log      | OFF                             |

| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |

+---------------------+---------------------------------+

·   The command result shows slow query log is currently disabled in the server.  You have add the following entries in the “/etc/mysql/my.cnf” file in-order to enable “slow query log”.  Place the entries below the section “mysqld”

[mysqld]

log_slow_queries        = /var/log/mysql/mysql-slow.log

long_query_time = 2

·         Check again whether the “slow query log” is enabled.

   mysql> show variables like ‘%slow%’;

+——————+——-+

| Variable_name    | Value |

+——————+——-+

| log_slow_queries | ON    |

| slow_launch_time | 2     |

+——————+——-+