【MYSQL】slow_log檔案清理的切換步驟

通常服務沒問題，慢查詢檔案大小只會越來越大，到一段時間需要清理

以下是正常開啟時的設置

＃查看慢查詢日志相關內容

MySQL [(none)]> 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/mysql-slow.log |

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

5 rows in set (0.00 sec)

＃查看執行超過多久會被寫入慢查詢日志中

MySQL [(none)]> show variables like '%long_query_time%';

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

| Variable_name   | Value    |

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

| long_query_time | 1.000000 |

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

1 row in set (0.00 sec)

當檔案過大時，需要切換時

#先關閉OFF

MySQL [(none)]> set global slow_query_log=0;

Query OK, 0 rows affected (0.01 sec)

關閉slow_log後，再進MYSQL資料夾裡面更名

cd /data/mysql/

mv  mysql-slow.log  mysql-slow.log.20190814old

再次進入MYSQL

＃重新下路徑（避免設定跑掉，或者下之前也可以先 show variables like '%slow%'; 查看）

MySQL [(none)]> set global slow_query_log_file='/data/mysql/mysql-slow.log';

Query OK, 0 rows affected (0.00 sec)

#重新開啟slow_log為ON

MySQL [(none)]> set global slow_query_log=1;

Query OK, 0 rows affected (0.11 sec)

#查詢是否已啟動

MySQL [(none)]> 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/mysql-slow.log |

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

5 rows in set (0.00 sec)

 

啟動後可進入MYSQL資料夾內查看是否有新的檔案產生，檔案是否有慢慢增大～有就成功了

 

接著壓縮檔案 tar -czf mysql-slow.log. 20190814 old.tar.gz mysql-slow.log. 20190814old　做為留抵用