Navigation

    择维士

    择维士用户社区

    • Register
    • Login
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups

    监控MySQL (一) 如何打开MySQL慢查询日志

    博客
    2
    2
    21
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • A
      aviation last edited by aviation

      慢查询会严重影响数据库性能和服务器整体性能. 慢查询日志的功能可以让MySQL记录下那些超过一定执行时间的语句,从而有助于我们查看和优化耗时的查询.

      1. 登录MySQL
      mysql -u root -p
      
      1. 输入对应的密码.
      2. 打开慢查询功能:
      SET GLOBAL slow_query_log = 'ON';
      
      1. 有一些额外的变量可以用于设置慢查询日志相关:
      • 默认情况下超过10秒的查询将会被记录. 可以通过如下语句来修改该时间: (X单位是秒)
      SET GLOBAL long_query_time = X;
      
      • 默认情况下慢查询日志位于 /var/lib/mysql/hostname-slow.log 可以通过如下语句来修改该路径:
      SET GLOBAL slow_query_log_file = '/path/filename';
      
      1. 可以通过如下语句来测试慢查询功能是否开启(需退出MySQL客户端重新登录):
      -- X应该大于前面的long_query_time
      SELECT SLEEP(X); 
      

      如下是在docker中的日志:

      root@a22bd29bee91:/# tail -f /var/lib/mysql/a22bd29bee91-slow.log 
      mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with:
      Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
      Time                 Id Command    Argument
      # Time: 2022-07-14T08:07:28.442751Z
      # User@Host: root[root] @  [192.168.3.2]  Id:  1031
      # Query_time: 15.000278  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
      use a1;
      SET timestamp=1657786048;
      SELECT SLEEP(15);
      
      1. 关闭慢查询日志:
      SET GLOBAL slow_query_log = 'OFF';
      
      H 1 Reply Last reply Reply Quote 0
      • H
        Hades @aviation last edited by

        @aviation 挺实用的,呵呵

        1 Reply Last reply Reply Quote 0
        • First post
          Last post