聊聊数据库：SQL运维~日记上篇

聊聊数据库：SQL运维~日记上篇

1.6.5.MySQL日志相关

1.MySQL常用日志2.errorlog（错误日志）MySQL8.0新增参数：logerrorservices时间戳相关的小知识点3.generallog（常规日志）4. slow_query_log（慢查询日志）扩展：慢查询工具mysqldumpslow（精简）pt-query-digest（推荐）percona-toolkitother

上篇回顾：聊聊数据库：SQL运维~存储引擎与权限

本文的测试环境：MySQL5.7.26、 MariaDB5.5.60、 MySQL8.0.16本篇：错误日志、常规日志、慢查询日志下集预告：binlog

1.6.5.MySQL日志相关

本文的测试环境：MySQL5.7.26、 MariaDB5.5.60、 MySQL8.0.16

PS：版本查询 selectversion();

1.MySQL常用日志

服务器层日志（存储引擎层有自己的日志）

知识扩展：https://blog.csdn.net/zhang123456456/article/details/72811875

实时查看文件：tail-f/var/log/mysqld.log

tail -f 用于监视文件增长（默认是末尾10行）

2.error_log（错误日志）

一般记录MySQL 运行错误和和 未授权的访问

老版： log_error + log_warnings常用： log_error + log_error_verbosity新版： log_error + log_error_verbosity + log_error_services

查询MySQL配置：show variables like'%log_error%';

SQL查询可以这么干：

-- Ubuntu下默认是：`/var/log/mysql/error.log`-- CentOS下默认是：`/var/log/mysqld.log` | `/var/log/mariadb/mariadb.log`select @@log_error; -- 尽可能和Data分开存储-- 0：不记录警告信息，1：告警信息写入错误日志，2：各类告警信息都写入（eg:网络故障和重连信息）select @@log_warnings; -- MySQL8中已经移除（MySQL5.7默认是2，MariaDB5.5.60默认是1）-- 错误级别（1：Error，2：Error、Warning，3：Error、Warning、Infoselect @@log_error_verbosity; -- MySQL8默认是2，MySQL5.7默认是3-- PS：从MySQL 5.7.2开始，首选`log_error_verbosity`系统变量-- 默认是`log_filter_internal; log_sink_internal`select @@log_error_services; -- MySQL8.0中新增

PS：其实MySQL在市面上有很多成熟解决方案（基本上都是基于5.6、5.7的）

这也是为什么我开篇主推 MySQL5.7系列和 MariaDB5.5.60（很多时候不是不用最新DB，而是架构依赖做不到啊）

知识拓展：https://cnblogs.com/kerrycode/p/8973285.html

MySQL8.0新增参数：logerrorservices

日志服务组件：

PS：log_filter_internal：过滤错误信息（达不到级别的不记录）

日记格式一般是这样的：UTC时间戳进程id[日志级别][错误代码][由什么产生的日志（ServerorClient）]详细信息

eg：2019-05-19T09:54:11.590474Z8[Warning][MY-010055][Server]IP address'192.168.36.144'couldnotbe resolved:Nameorservicenotknown

一般 log_sink_json用的比较多：

官方文档参考：https://dev.mysql.com/doc/refman/8.0/en/error-log-json.html

PS：第一次使用需要安装一下json组件：install component'file://component_log_sink_json';

常用设置：setpersist log_error_services='log_filter_internal;log_sink_json';

时间戳相关的小知识点

上面的时间默认是UTC的时间戳，和我们是有时差的，这个时间戳可以通过设置 log_timestamps来本地化：

-- 查询select @@log_timestamps; -- MySQL5.7新增-- 从8开始,可通过SET PERSIST命令将全局变量的修改持久化到配置文件中set persist log_timestamps='SYSTEM'; -- 需要root权限

PS：setpersist生成的配置文件路径在：/var/lib/mysql/mysqld-auto.cnf

3.general_log（常规日志）

以前开发调试的时候基本上都是会开启的，上线后关闭（系统V1初期的时候也会开启一段时间）

现在开发可以使用go-sniffer来抓包查看客户端执行的SQL

-- 是否打开常规日志（0不打开，1打开）-- 一般不打开（性能）select @@general_log; -- 默认为0-- Ubuntu默认：/var/lib/mysql/ubuntuserver.log-- CentOS默认：/var/lib/mysql/localhost.logselect @@general_log_file; -- 常规日志的路径-- 日志的存储方式（FILE | TABLE | NONE）select @@log_output; -- 默认是文件存储

简单看一下常规日志在数据库中的结构：

临时开启参考：

# 开启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.logselect @@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的SQLset 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/2311752https://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: 5SET 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执行时间

现在可以说说工具了，推荐两款：

自带的慢日志分析工具： mysqldumpslowMySQL工具箱（ percona-toolkit）中的 pt-query-digest

mysqldumpslow（精简）

查询最慢的10条SQL：mysqldumpslow-s t-t10/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*fromtb_tablewhereuid=20groupbycreatetime limit10000,1000; ==> select*fromtb_tablewhereuid=Ngroupbycreatetime limit N,N;

不管你uid和limit怎么变，mysqldumpslow认为是一样的

pt-query-digest（推荐）

官方文档：https://percona.com/doc/percona-toolkit/3.0/pt-query-digest.html

分析慢查询日志：pt-query-digest/var/lib/mysql/localhost-slow.log

使用tcppdump捕获MySQL协议数据，然后报告最慢的查询：

tcpdump-s65535-x-nn-q-tttt-i any-c1000port3306>mysql.tcp.txtpt-query-digest--type tcpdump mysql.tcp.txt

查看来自远程进程列表上最慢的查询：

pt-query-digest--processlist h=ip

percona-toolkit

percona-toolkit的常用工具我这边简单说说：

MySQL常用工具包：percona-toolkit

官方文档：https://percona.com/doc/percona-toolkit/LATEST/index.html

列几个常用的：

pt-summary：查看 服务器信息

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-summary.html

pt-diskstats：查看 磁盘开销使用信息

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-diskstats.html

pt-mysql-summary--user=用户名--password=密码：查看 mysql的 信息

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-mysql-summary.html

pt-ioprofile：查看mysql表和文件的 IO开销

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-ioprofile.html

pt-show-grants--user=root--password=密码：查看mysql 授权

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-show-grants.htmlPS：通过 --revoke、 -separate等,可以撤消用户的特定权限

pt-duplicate-key-checker--host=localhost--user=root--password=密码： 查找数据库表中 重复的索引

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-duplicate-key-checker.html

pt-deadlock-logger--user=root--password=密码--host=localhost：查看mysql 死锁信息

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-deadlock-logger.html

pt-query-digest/var/lib/mysql/localhost-slow.log：分析 慢查询日志

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html

pt-index-usage/var/lib/mysql/localhost-slow.log：从 慢查询日志中分析 索引使用情况

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-index-usage.html

pt-config-diff/etc/my.cnf/etc/my_master.cnf： 查看不同mysql 配置文件的差异

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-config-diff.html

pt-slave-find--host=localhost--user=root--password=密码：查找mysql的 从库和同步状态

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-slave-find.html

pt-table-checksum--user=root--password=密码： 验证数据库 复制的完整性

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-table-checksum.html

安装附录：https://github.com/lotapp/awesome-tools/blob/master/README.md#4%E8%BF%90%E7%BB%B4

# Ubuntu：# curl https://percona.com/downloads/percona-toolkit/2.2.20/deb/percona-toolkit_2.2.20-1_all.deb > percona-toolkit-2.2.20.deb# CentOS：# curl https://percona.com/downloads/percona-toolkit/2.2.20/RPM/percona-toolkit-2.2.20-1.noarch.rpm > percona-toolkit-2.2.20.noarch.rpm[dnt@localhost ~]$ curl https://percona.com/downloads/percona-toolkit/2.2.20/RPM/percona-toolkit-2.2.20-1.noarch.rpm > percona-toolkit-2.2.20.noarch.rpm % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed100 1700k 100 1700k 0 0 309k 0 0:00:05 0:00:05 --:--:-- 426k[root@localhost dnt] lspercona-toolkit-2.2.20.noarch.rpm# Ubuntu：# sudo apt install ./percona-toolkit-2.2.20.deb -y# CentOS：# yum install percona-toolkit-2.2.20.noarch.rpm -y[root@localhost dnt] yum install percona-toolkit-2.2.20.noarch.rpm -y已加载插件：fastestmirror正在检查 percona-toolkit-2.2.20.noarch.rpm: percona-toolkit-2.2.20-1.noarchpercona-toolkit-2.2.20.noarch.rpm 将被安装正在解决依赖关系--> 正在检查事务---> 软件包 percona-toolkit.noarch.0.2.2.20-1 将被 安装--> 解决依赖关系完成依赖关系解决======================================================================================================================================== Package 架构 版本 源 大小========================================================================================================================================正在安装: percona-toolkit noarch 2.2.20-1 /percona-toolkit-2.2.20.noarch 5.7 M事务概要========================================================================================================================================安装 1 软件包总计：5.7 M安装大小：5.7 MDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning transaction 正在安装 : percona-toolkit-2.2.20-1.noarch 1/1 验证中 : percona-toolkit-2.2.20-1.noarch 1/1已安装: percona-toolkit.noarch 0:2.2.20-1完毕！

other

PS：还有一款 mysqlsla我没用过，所以贴个参考文章，感兴趣的同志自己研究下

知识拓展：https://cnblogs.com/fengchi/p/6187099.html