線上安全清空慢查詢日誌slowlog
mysql> show variables like '%slow_query%';
+------------------------------------+-------------------------------+
| Variable_name | Value |
+------------------------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /data/DB/mysql/mysql-slow.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+-------------------------------+
[root@trcloud-gtt-dw backup]# ls -l /data/DB/mysql/mysql-slow.log
-rw-rw---- 1 mysql mysql 2482474901 Jun 24 14:47 /data/DB/mysql/mysql-slow.log
發現慢查詢日誌已經有2G多了,使用mysqldumpslow統計資訊的時候非常慢,需要線上清空並備份
一:關閉慢查詢日誌
mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%slow_query%';
+------------------------------------+-------------------------------+
| Variable_name | Value |
+------------------------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /data/DB/mysql/mysql-slow.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+-------------------------------+
6 rows in set (0.00 sec)
二:指定新的慢查詢日誌檔案
mysql> set global slow_query_log_file='/data/DB/mysql/mysql-slow_20160624.log';
Query OK, 0 rows affected (0.00 sec)
三:開啟慢查詢日誌
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%slow_query%';
+------------------------------------+----------------------------------------+
| Variable_name | Value |
+------------------------------------+----------------------------------------+
| slow_query_log | ON |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /data/DB/mysql/mysql-slow_20160624.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+----------------------------------------+
6 rows in set (0.00 sec)
檢視資訊的慢查詢日誌檔案
[root@trcloud-gtt-dw backup]# tail -50f /data/DB/mysql/mysql-slow_20160624.log
# Time: 160624 15:13:28
# User@Host: admin[admin] @ [172.30.250.103] Id: 20178
# Schema: gtt_api Last_errno: 0 Killed: 0
# Query_time: 0.000163 Lock_time: 0.000080 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 618
SET timestamp=1466752408;
SELECT invest_money, total_earning, invest_this_month, invest_last_month, total_wait_earning, total_people FROM th_home_data;
已經開始記錄慢查詢日誌
四:備份老的慢查詢日誌檔案
[root@trcloud-gtt-dw backup]# mv /data/DB/mysql/mysql-slow.log /backup/mysql-slow.log.bak.20160624
+------------------------------------+-------------------------------+
| Variable_name | Value |
+------------------------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /data/DB/mysql/mysql-slow.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+-------------------------------+
[root@trcloud-gtt-dw backup]# ls -l /data/DB/mysql/mysql-slow.log
-rw-rw---- 1 mysql mysql 2482474901 Jun 24 14:47 /data/DB/mysql/mysql-slow.log
發現慢查詢日誌已經有2G多了,使用mysqldumpslow統計資訊的時候非常慢,需要線上清空並備份
一:關閉慢查詢日誌
mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%slow_query%';
+------------------------------------+-------------------------------+
| Variable_name | Value |
+------------------------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /data/DB/mysql/mysql-slow.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+-------------------------------+
6 rows in set (0.00 sec)
二:指定新的慢查詢日誌檔案
mysql> set global slow_query_log_file='/data/DB/mysql/mysql-slow_20160624.log';
Query OK, 0 rows affected (0.00 sec)
三:開啟慢查詢日誌
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%slow_query%';
+------------------------------------+----------------------------------------+
| Variable_name | Value |
+------------------------------------+----------------------------------------+
| slow_query_log | ON |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /data/DB/mysql/mysql-slow_20160624.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+----------------------------------------+
6 rows in set (0.00 sec)
[root@trcloud-gtt-dw backup]# tail -50f /data/DB/mysql/mysql-slow_20160624.log
# Time: 160624 15:13:28
# User@Host: admin[admin] @ [172.30.250.103] Id: 20178
# Schema: gtt_api Last_errno: 0 Killed: 0
# Query_time: 0.000163 Lock_time: 0.000080 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 618
SET timestamp=1466752408;
SELECT invest_money, total_earning, invest_this_month, invest_last_month, total_wait_earning, total_people FROM th_home_data;
已經開始記錄慢查詢日誌
四:備份老的慢查詢日誌檔案
[root@trcloud-gtt-dw backup]# mv /data/DB/mysql/mysql-slow.log /backup/mysql-slow.log.bak.20160624
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29989552/viewspace-2120915/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [Slow log]正確安全清空線上慢查詢日誌slow log的流程
- [MySQL Slow log]正確安全清空線上慢查詢日誌slow log的流程MySql
- MySQLSlowlog慢查詢日誌的配置以及清空日誌內容MySql
- MySQL:慢查詢日誌MySql
- 使用慢查詢日誌
- mysql慢查詢日誌MySql
- 慢查詢日誌的管理
- 關於MySQL 通用查詢日誌和慢查詢日誌分析MySql
- PHP慢指令碼日誌和Mysql的慢查詢日誌PHP指令碼MySql
- 開啟查詢慢查詢日誌引數
- 慢查詢日誌開啟分析
- 【MySQL】慢查詢日誌不列印MySql
- mysqlsla 分析mysql慢查詢日誌MySql
- MySQL 清空慢查詢檔案MySql
- MySQL 慢查詢日誌——讓“慢”無所遁形MySql
- Redis慢查詢日誌學習功能Redis
- mysql之 slow log 慢查詢日誌MySql
- MySQL Slow Query log(慢查詢日誌)MySql
- mysql慢查詢日誌分析工具使用MySql
- Mysql慢查詢日誌分析工具mysqlslaMySql
- 【MySql】 慢日誌查詢工具之mysqlslaMySql
- 筆記 mongo查詢慢日誌,建立索引筆記Go索引
- MySQL慢查詢日誌相關設定MySql
- 【轉載】MySQL慢查詢日誌總結MySql
- mysql慢查詢和錯誤日誌分析MySql
- MYSQL開啟慢查詢日誌實施MySql
- mysql開啟檢視慢查詢日誌MySql
- 用命令過濾MySQL慢查詢日誌MySql
- Mysql慢查詢日誌檔案轉ExcelMySqlExcel
- 對 MySQL 慢查詢日誌的簡單分析MySql
- 如何在MySQL中開啟慢查詢日誌?MySql
- mysql分析慢查詢日誌工具mysqlsla安裝MySql
- Mysql 啟動慢查詢日誌 (不用重啟)MySql
- 分析mysql慢查詢日誌的好工具--mysqlslaMySql
- 【MySql】mysql 慢日誌查詢工具之mysqldumpslowMySql
- MySQL資料庫中的日誌檔案---(3)慢查詢日誌MySql資料庫
- Linux下mysql配置慢日誌查詢,把查詢慢的sql記錄下來LinuxMySql
- 資料庫MySQL一般查詢日誌或者慢查詢日誌歷史資料的清理資料庫MySql