mysql general log 查看mysql 運行曆史

標籤：gpo   mysq   oba   log   tor   grep   table   span   ack   

我們有時候須要查看mysql的運行曆史，比方我們做sql最佳化的時候，起碼要知道啟動並執行sql是什麼。架構通常會幫我們拼裝sql，所以在程式中不一定能夠列印出sql，這個時候就須要mysql的general
log了。

查看設定mysql genneral log

show VARIABLES like ‘%general_log%‘;set GLOBAL general_log = off;// on-開啟; off-關閉general_log ONgeneral_log_file    /var/log/mysql/query.log

使用mysql general log

tail -f /path/to/log/query.log | grep yourtable13518 Prepare   SELECT count(*) AS `count` FROM `babysitter_tips` WHERE (tip_type = ‘1‘) AND (is_enable = 1) AND (is_tip = 2)        13518 Query DESCRIBE `babysitter_tips`        13518 Close stmt        13518 Prepare   SELECT `babysitter_tips`.* FROM `babysitter_tips` WHERE (tip_type = ‘1‘) AND (is_enable = 1) AND (is_tip = 2) ORDER BY `created_time` desc LIMIT 5        13518 Reset stmt        13518 Close stmt        13518 Prepare   SELECT count(*) AS `count` FROM `babysitter_tips` WHERE (tip_type = ‘1‘) AND (is_enable = 1) AND (is_tip = 2) AND (tip_id > 15440)        13518 Close stmt        13518 Prepare   SELECT count(*) AS `count` FROM `babysitter_tips` WHERE (tip_type = ‘3‘) AND (is_enable = 1) AND (is_tip = 2)        13518 Query DESCRIBE `babysitter_tips`        13518 Close stmt        13518 Prepare   SELECT `babysitter_tips`.* FROM `babysitter_tips` WHERE (tip_type = ‘3‘) AND (is_enable = 1) AND (is_tip = 2) ORDER BY `created_time` desc LIMIT 5        13518 Reset stmt        13518 Close stmt        13518 Prepare   SELECT count(*) AS `count` FROM `babysitter_tips` WHERE (tip_type = ‘3‘) AND (is_enable = 1) AND (is_tip = 2) AND (tip_id > ‘‘)        13518 Close stmt        13518 Prepare   SELECT count(*) AS `count` FROM `babysitter_tips` WHERE (tip_type = ‘2‘) AND (is_enable = 1) AND (is_tip = 2)        13518 Query DESCRIBE `babysitter_tips`        13518 Close stmt        13518 Prepare   SELECT `babysitter_tips`.* FROM `babysitter_tips` WHERE (tip_type = ‘2‘) AND (is_enable = 1) AND (is_tip = 2) ORDER BY `created_time` desc LIMIT 5        13518 Reset stmt        13518 Close stmt

清理mysql general log

general log會比較大，所以預設市關閉的，所以最好須要的時候開啟，隨手關閉。假設發現query.log過大。能夠手動刪除。在general log開啟的情況下，query.log檔案相似於mysql表的lock情況，不同意改動和刪除。關閉general log就能夠操作了。

mysql general log 查看mysql 運行曆史