关闭

MySQL正确安全清空在线慢查询日志slow log

标签: mysql性能优化
1017人阅读 评论(0) 收藏 举报
分类:

1、see the slow log status;

mysql> show variables like %slow%;

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

| Variable_name       | Value                                    |

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

| log_slow_queries    | ON                                       |

| slow_launch_time    | 2                                        |

| slow_query_log      | ON                                       |

| slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |

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

4 rows in set (0.00 sec)

2、stop the slow log server.

mysql> set global slow_query_log=0;

Query OK, 0 rows affected (0.27 sec)

mysql> show variables like %slow%;

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

| Variable_name       | Value                                    |

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

| log_slow_queries    | OFF                                      |

| slow_launch_time    | 2                                        |

| slow_query_log      | OFF                                      |

| slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |

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

4 rows in set (0.00 sec)

mysql> show variables like %slow%; -- check slow log status

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

| Variable_name       | Value                                    |

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

| log_slow_queries    | OFF                                      |

| slow_launch_time    | 2                                        |

| slow_query_log      | OFF                                      |

| slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |

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

4 rows in set (0.00 sec)

3、reset the new path of slow log

mysql> set global slow_query_log_file=/mysqllog/slow_log/slow_queries_3306_new.log;

Query OK, 0 rows affected (0.03 sec)

4、start the slow log server

mysql> set global slow_query_log=1;

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like %slow%;

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

| Variable_name       | Value                                        |

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

| log_slow_queries    | ON                                           |

| slow_launch_time    | 2                                            |

| slow_query_log      | ON                                           |

| slow_query_log_file | /mysqllog/slow_log/slow_queries_3306_new.log |

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

4 rows in set (0.00 sec)

5、check the slow sql in the new slow log file.

mysql> select sleep(10) as a, 1 as b;

+---+---+

| a | b |

+---+---+

| 0 | 1 |

+---+---+

1 row in set (10.00 sec)

mysql>exit;

[mysql@xxx-xxx ']$ more /mysqllog/slow_log/slow_queries_3306_new.log

......

Time                 Id Command    Argument

# Time: 140213  6:44:24

# User@Host: root[root] @ localhost []

# Query_time: 10.000365  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1392273864;

select sleep(10) as a, 1 as b;

6、 backup the old big slow log file to other directory.

mv /mysqllog/slow_log/slow_queries_3306.log /mysqlbackup/slow_log/slow_queries_3306.log.bak.20140213






原文:http://www.zhujiangroad.com/program/MySQL/71912.html
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:371113次
    • 积分:3243
    • 等级:
    • 排名:第10921名
    • 原创:20篇
    • 转载:78篇
    • 译文:0篇
    • 评论:27条
    文章分类
    最新评论