临时开启参考:
# 开启 set global general_log = 1; # set [global | persist] general_log_file = '日志路径'; set global log_output = 'TABLE'; 4.slow_query_log(慢查询日志)这个是最常用的,把符合条件的查询语句记录在日志中,一般都是些需要优化的SQL
PS:出现性能瓶颈的时候,或者为了优化SQL会开启一段时间(小项目推荐直接开启)
先看下默认值:show variables like '%slow%';、show variables like 'long%';
SQL查询:
-- 是否开启 select @@slow_query_log; -- 默认是关闭 -- CentOS:/var/lib/mysql/localhost-slow.log -- Ubuntu:/var/lib/mysql/ubuntuserver-slow.log select @@slow_query_log_file; -- 条件:设置超过多少秒为慢查询(一般设置1s) select @@long_query_time; -- 默认是10s(支持小数:0.003) -- PS:设置为0就会记录所有SQL(不推荐这么干) -- 条件:没有使用索引的查询记录到日志中 select @@log_queries_not_using_indexes; -- 默认是0(不开启) -- 记录optimize table、analyze table和alter table的管理语句 select @@log_slow_admin_statements; -- 默认是0(不开启) -- 记录由Slave所产生的慢查询 select @@log_slow_slave_statements;常用设置:
PS:高并发下的互联网项目,对SQL执行时间的容忍度一般都是低于300~500ms的(long_query_time=0.05)
# 常用如下:(需要MySQL的root权限) set global slow_query_log = 1; # 开启慢查询日志 set global long_query_time = 1; # 记录大于1s的SQL set global log_slow_admin_statements = 1; # 记录管理语句 set global log_queries_not_using_indexes = 1; # 记录没有使用索引的SQL # set [global | persist] slow_query_log_file = '路径'; # 设置log路径设置long_query_time时,需要重新连接才能生效(不需要重启DB)
PS:当前会话不生效,之后的会话就生效了(不想重连可以再设置下当前会话的long_query_time)
知识拓展:(chown mysql:mysql /work/log/xxx.log)
https://shihlei.iteye.com/blog/2311752
https://www.cnblogs.com/1021lynn/p/5328495.html
扩展:慢查询工具先简单分析下慢查询日志:
# Time: 2019-05-22T21:16:28.759491+08:00 # User@Host: root[root] @ localhost [] Id: 11 # Query_time: 0.000818 Lock_time: 0.000449 Rows_sent: 5 Rows_examined: 5 SET timestamp=1558530988; select * from mysql.user order by host; # SQL语句Time:查询的执行时间(start_time)
User@Host: root[root] @ localhost [] Id:11:执行 sql 的主机信息
Query_time:SQL查询所耗的时间
Lock_time:锁定时间
Rows_sent:所发送的行数
Rows_examined:锁扫描的行数
SET timestamp=1558530988;:SQL执行时间
现在可以说说工具了,推荐两款:
自带的慢日志分析工具:mysqldumpslow
MySQL工具箱(percona-toolkit)中的pt-query-digest
mysqldumpslow(精简)查询最慢的10条SQL:mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log
-s 按照那种方式排序 t: 查询时间 c:访问计数 l:锁定时间 r:返回记录 al:平均锁定时间 ar:平均访问记录数 at:平均查询时间 -t 返回多少条数据(可以理解为top n) -g 可以跟上正则匹配模式,大小写不敏感。PS:使用mysqldumpslow的分析结果不会显示具体完整的sql语句:
翻页sql不一样,性能也是不一样的,越往后的页数越容易出现慢查询,而mysqldumpslow把所有翻页sql当成一个sql了
eg:select * from tb_table where uid=20 group by createtime limit 10000, 1000; ==> select * from tb_table where uid=N group by createtime limit N, N;
不管你uid和limit怎么变,mysqldumpslow认为是一样的
pt-query-digest(推荐)官方文档:https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html
分析慢查询日志:pt-query-digest /var/lib/mysql/localhost-slow.log
使用tcppdump捕获MySQL协议数据,然后报告最慢的查询:
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt
查看来自远程进程列表上最慢的查询:
pt-query-digest --processlist h=ip
安装可以参考:
PS:percona-toolkit的常用工具我也在里面简单说了下,对应文档也贴了
otherPS:还有一款mysqlsla我没用过,所以贴个参考文章,感兴趣的同志自己研究下
https://www.cnblogs.com/fengchi/p/6187099.html
知识拓展:https://www.cnblogs.com/fengchi/p/6187099.html
5.binary_log(二进制日志)上节主要说了通用日志和慢查日志,今天说下二进制日志:
二进制日志算是最常用的了,主要就是记录对数据库的修改,然后就是主从复制用的比较多(比如增量备份)
PS:记录了修改操作,那么衍生出的场景就是:增量备份和恢复(基于时间点的备份和恢复)
PS:MySQL日志主要分为这两类:(互不干扰)
服务层日志(和使用存储引擎无关)
通用日志、慢查询日志、二进制日志
存储引擎层日志
eg:innodb的重做日志(redo log)和回滚日志(undo log)
Q:那什么样的修改会记录下来呢?