mysql故障排错临时打开通用日志和慢查询日志

          有时候你遇到sql 执行报错，有的是语法错误很好判断，但有的是sql本身写的没有问题，就是在执行的时候报错，有的sql很复杂你根本就不知道具体的哪一个地方报错，这个时候你可以把复杂的sql分解去确定报错的位置，如果分解成很简单的sql执行还是报错，那么你可以确定sql中是否含有mysql本身的关键字问题引起的。

          另外有一种是应用程序运行报sql执行失败，这个时候你可能不知道具体sql语句是什么（如果应用程序日志不打印），因为应用程序的sql是框架生成，这个时候定位问题你就需要指定mysql服务器最终接收到的查询是什么？

          这个时候可以通过启用通用查询日志来查询所有的查询语句，日志记录mysql接受的所有查询，这很消耗mysql本身的资源，我们可以临时打开通用日志，之后再关闭  

          临时打开通用日志：

mysql> set global general_log='on';
Query OK, 0 rows affected (0.00 sec)

这是mysql会在数据目录下生成mydb3.log 文件，记录查询语句

[root@mydb3 mysql]# ls
auto.cnf        ib_logfile2  mydb3-relay-bin.000014  mysql               test         wwang
ib_buffer_pool  master.info  mydb3-relay-bin.000015  performance_schema  uxallowance
ibdata1         mydb3.err    mydb3-relay-bin.index   relay-log.info      uxintegral
ib_logfile0     mydb3.log    mydb3-slow.log          set                 uxscan
ib_logfile1     mydb3.pid    myslave                 show                uxstatistic

[root@mydb3 mysql]# cat mydb3.log 
/usr/local/mysql/bin/mysqld, Version: 5.6.28 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
160518  7:42:05   187 Query     SET PROFILING=1
                  187 Query     SHOW STATUS
                  187 Query     select * from ddddd
160518  7:43:44   190 Connect   root@192.168.1.243 on 
                  190 Query     SET NAMES utf8
                  190 Init DB   mysql
                  190 Query     SELECT @@character_set_database, @@collation_database
                  190 Query     SHOW FULL TABLES WHERE Table_type != 'VIEW'
160518  7:43:58   191 Connect   root@192.168.1.243 on 
                  191 Query     SET NAMES utf8
                  191 Init DB   mysql
                  190 Query     SHOW CREATE TABLE `general_log`
                  191 Query     SELECT * FROM `general_log` LIMIT 0, 1000
                  191 Query     SHOW COLUMNS FROM `mysql`.`general_log`
160518  7:44:42   189 Query     set global log_output='table'

mysql> set global log_output='table';
Query OK, 0 rows affected (0.00 sec)

设置mysql查询日志记录到表中mysql.general_log

mysql> select * from mysql.general_log limit 4\G;
*************************** 1. row ***************************
  event_time: 2016-05-18 07:44:52
   user_host: root[root] @  [192.168.1.243]
   thread_id: 187
   server_id: 3000
command_type: Query
    argument: SET PROFILING=1
*************************** 2. row ***************************
  event_time: 2016-05-18 07:44:52
   user_host: root[root] @  [192.168.1.243]
   thread_id: 187
   server_id: 3000
command_type: Query
    argument: SHOW STATUS
*************************** 3. row ***************************
  event_time: 2016-05-18 07:44:52
   user_host: root[root] @  [192.168.1.243]
   thread_id: 187
   server_id: 3000
command_type: Query
    argument: SHOW STATUS
*************************** 4. row ***************************
  event_time: 2016-05-18 07:44:52
   user_host: root[root] @  [192.168.1.243]
   thread_id: 187
   server_id: 3000
command_type: Query
    argument: select * from ddddd
4 rows in set (0.00 sec)

关闭通用日志：

mysql> show variables like '%general_log%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | ON                    |
| general_log_file | /data/mysql/mydb3.log |
+------------------+-----------------------+
2 rows in set (0.00 sec)

mysql> set global general_log='off';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%general_log%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | OFF                   |
| general_log_file | /data/mysql/mydb3.log |
+------------------+-----------------------+
2 rows in set (0.00 sec)

        在有些时候，你会遇到其他人员反映程序执行慢，你怀疑可能是sql 执行慢导致，而又不知道具体的是那一条sql执行导致的，这个时候你可以临时开启慢查询日志，一般情况下慢查询都是开启的

    临时开启慢查询日志：

mysql>  show variables like '%slow%';
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| log_slow_admin_statements | OFF                        |
| log_slow_slave_statements | OFF                        |
| slow_launch_time          | 2                          |
| slow_query_log            | OFF                        |
| slow_query_log_file       | /data/mysql/mydb3-slow.log |
+---------------------------+----------------------------+
5 rows in set (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |    --默认记录执行超过10s的sql
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like '%slow%';
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| log_slow_admin_statements | OFF                        |
| log_slow_slave_statements | OFF                        |
| slow_launch_time          | 2                          |
| slow_query_log            | ON                         |
| slow_query_log_file       | /data/mysql/mydb3-slow.log |
+---------------------------+----------------------------+
5 rows in set (0.00 sec)

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |  还是10s 新打开的会话生效
+-----------------+-----------+
1 row in set (0.00 sec)

这个时候你可以去日志查询慢sql如果你设置了 log_output='table' 就去表中查询mysql.slow_log

关闭执行

mysql>  set global slow_query_log=off;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like '%slow%';
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| log_slow_admin_statements | OFF                        |
| log_slow_slave_statements | OFF                        |
| slow_launch_time          | 2                          |
| slow_query_log            | OFF                        |
| slow_query_log_file       | /data/mysql/mydb3-slow.log |
+---------------------------+----------------------------+
5 rows in set (0.00 sec)