mysqlgenerallog查看mysql执行历史_MySQL-mysql教程-PHP中文网

mysqlgenerallog查看mysql执行历史_MySQL

php中文网

发布： 2016-06-01 12:58:38

原创

1349人浏览过

我们有时候需要查看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 ON
general_log_file    /var/log/mysql/query.log

登录后复制

`使用mysql general log`

<code class="hljs sql">tail -f /path/to/log/query.log | grep yourtable
13518 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</code>

登录后复制

`<code class="hljs sql">清理mysql general log`

<code class="hljs sql">general log会比较大，所以默认市关闭的，所以最好需要的时候打开，随手关闭。如果发现query.log过大，可以手动删除。在general log打开的情况下，query.log文件类似于mysql表的lock情况，不允许修改和删除，关闭general log就可以操作了。

Python操作Mysql实例代码教程
本文介绍了Python操作MYSQL、执行SQL语句、获取结果集、遍历结果集、取得某个字段、获取表字段名、将图片插入数据库、执行事务等各种代码实例和详细介绍，代码居多，是一桌丰盛唯美的代码大餐。如果想查看在线版请访问：https://www.jb51.net/article/34102.htm

0

查看详情