上篇回顾: https://www.cnblogs.com/dotnetcrazy/p/10810798.html#top
1.6.5.MySQL 日志相关
本文的测试环境: MySQL5.7.26,MariaDB5.5.60,MySQL8.0.16
PS: 版本查询 select version();
1.MySQL 常用日志
服务器层日志(存储引擎层有自己的日志)
日志类型 | 描述 |
---|---|
error_log(错误日志) | 记录 MySQL 启动、运行或停止时出现的问题 |
general_log(常规日志) | 记录所有发送给 MySQL 的请求(耗性能) |
slow_query_log (慢查日志) | 记录符合条件的查询(eg:超过 10s、没有使用索引等) |
binary_log (二进制日志) | 记录全部有效的数据修改日志(老版本数据库不会开启) |
relay_log(中继日志) | 用于主从复制,临时存储主从同步的二进制日志(增量复制) |
知识扩展:
实时查看文件: 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,Info
select @@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://www.cnblogs.com/kerrycode/p/8973285.html
MySQL8.0 新增参数: log_error_services
日志服务组件:
日志服务组件名 | 描述 |
---|---|
log_sink_internal | 默认的日志输出组件(依赖 log_error) |
log_filter_internal | 默认的日志过滤组件(依赖 log_error_verbosity) |
log_sink_json | 将错误日志输出到 json 文件 |
log_sink_syseventlog | 将错误日志输出到系统日志文件 |
PS:log_filter_internal: 过滤错误信息(达不到级别的不记录)
日记格式一般是这样的: UTC 时间戳 进程 id [日志级别] [错误代码] [由什么产生的日志(Server or Client)] 详细信息
eg:2019-05-19T09:54:11.590474Z 8 [Warning] [MY-010055] [Server] IP address '192.168.36.144' could not be resolved: Name or service not known
一般 log_sink_json 用的比较多:
官方文档参考:
PS: 第一次使用需要安装一下 JSON 组件: install component 'file://component_log_sink_json';
常用设置: set persist 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:set persist 生成的配置文件路径在:/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.log
select @@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.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(推荐)
官方文档:
分析慢查询日志: 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 的常用工具我也在里面简单说了下, 对应文档也贴了
other
PS: 还有一款 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: 那什么样的修改会记录下来呢?
A: 记录所有对 MySQL 数据库的修改事件(包括增删改查事件和对表结构修改的事件), 而且只记录已经成功执行的事件(失败的不会记录)
这么说可能有点抽象, 熟悉 SQLServer 的同志看个图就秒懂:
5.1. 二进制日志格式
参数 | 说明 |
---|---|
STATEMENT | 基于段的格式,记录执行数据修改时候所执行的 SQL 语句 |
ROW | 基于行的格式,记录增删改查操作所修改行的信息(每修改一行就会有一条信息) |
MIXED | 基于行和端的混合格式,根据 SQL 语句由系统决定是基于段还是基于行的日志格式记录 |
查看方式: show variables like 'binlog_format';
binlog_format=statement: 基于段的记录格式(老版本的默认值)
优点: 记录量较小, 节约磁盘和网络 IO(单条操作 Row 更节约)
缺点: 必须记录上下文信息来保证语句在从服务器上执行结果与主服务器相同
但是如果使用了 uuid(),user()等结果非确定的函数, 可能会造成 MySQL 主从不一致
日志查看:
- mysqlbinlog /var/lib/MySQL/binlog.0000xx | more
- (不用指定参数)
binlog_format=row: 基于行的记录格式(5.7 以后的默认值)
优点: 可以避免 MySQL 复制中出现的主从不一致的问题(主从更安全)
PS: 没有备份的时候可以通过分析 row 格式的二进制日志来反向恢复
缺点: 记录日志量较大(顺序写入)
现在增加了新参数来优化:
binlog_row_image=[full|minimal|noblob]
日志查看:
mysqlbinlog -vv /var/lib/MySQL/binlog.0000xx | more
binlog_format=mixed: 基于行和端的混合格式(推荐)
PS: 数据量大小由所执行的 SQL 决定(非确定性函数越多, 行数越多)
PS:DDL 操作 (create,drop,alter) 的时候都是基于段方式来记录 log
如果一条一条记录, 表有上亿数据, 我就修改某列的状态值, 那不得疯?
对 binlog_row_image=[FULL|MINIMAL|NOBLOB]的补充说明:
PS: 查看方式: show variables like 'binlog_row_image'
默认是 full: 完整
记录修改行的全部内容
noblob: 就是在 full 记录的基础上对大文本列的优化
没有对 text 或者 blob 列修改就不记录该列
minimal: 简单记录, 只记录修改的那一列
PS: 这个要特别注意一点, 虽然容量小了, 但是一旦误操作, 很难恢复的(不知道原来内容)
推荐使用
一般使用 binlog_format=mixed 混合格式 or binlog_format=row + binlog_row_image=minimal
PS: 如果对安全性要求特别高, 推荐使用 binlog_format=row + binlog_row_image=full(不怕误操作)
这个和 SQLServer 的日志恢复模式有点类似, 我贴下图你们可以对比参考:
5.2. 二进制日志配置
上面虽然说完了二进制日志的常用 3 种格式, 但老版本默认都是不启用二进制日志的, 咋办?
PS: 如果是 MariaDB 可以去示例配置中查看: ls /usr/share/MySQL/ |grep .cnf(CentOS)
验证下:
MySQL8 之前: cat /etc/MySQL/MySQL.conf.d/mysqld.cnf(UbuntuServer)
- MySQL8:
- cat /etc/my.cnf |grep log
- (CentOS)
Q: 有些人可能疑惑了, 为什么用 show variables like 'log_bin'; 查询出来的结果和配置文件中不大一样啊?
PS: 一般配置项中的参数都可以使用 show variables like 'xx'来查询对应的值
A: 那是因为 5.7 之后版本分成了两个参数: log_bin 和 log_bin_basename:
PS: 配置文件的 log_bin=xxx 相当于命令中的 log_bin 和 log_bin_basename
- MySQL> show variables like 'log_bin%';
- +---------------------------------+-----------------------------+
- | Variable_name | Value |
- +---------------------------------+-----------------------------+
- | log_bin | ON |
- | log_bin_basename | /var/lib/MySQL/binlog |
- | log_bin_index | /var/lib/MySQL/binlog.index |
- | log_bin_trust_function_creators | OFF |
- | log_bin_use_v1_row_events | OFF |
- +---------------------------------+-----------------------------+
- 5 rows in set (0.00 sec)
开启演示
MariaDB 开启 binlog 图示:(CentOS)
MySQL5.7 演示:(UbuntuServer)
配置文件中修改:(show variables like 'binlog_format';: 查看当前 binlog 基于什么格式)
- # 服务器标识
- server-id=1 # 单机 MariaDB 可不开启
- # 开启 binlog 并设置路径
- # 不指定路径则默认在数据目录下
- log_bin=binlog # 这个代表以 binlog 开头的文件
- # binlog 采用 ROW|MIXED 格式
- # binlog_format=MIXED # 5.7 默认是 ROW
先看下文件前缀 (log_bin=binlog) 的概念, 一张图就懂:
PS: 如果 log_bin 只是指定一个名字, 那么默认路径一般都是在数据文件的文件夹中
配置文件一般都会写, eg:datadir=/var/lib/MySQL, 或者通过 show variables like 'datadir'; 也可以查询到
虽然和 SQLServer 文件组不是一个概念, 但有些相似 ==>
log 可以多个也可以动态调整
5.3.ROW 模式下记录 SQL
Q: 虽然 ROW 记录能保证主从数据安全, 但我们排查问题的时候往往需要知道 SQL, 而用段的记录方式又不合适, 咋办?
A: 有个新参数可以解决: binlog_rows_query_log_events, 开启后就可以记录 sql 了
查看方式: show variables like 'binlog_row%';
- MySQL> show variables like 'binlog_row%';
- +------------------------------+-------+
- | Variable_name | Value |
- +------------------------------+-------+
- | binlog_row_image | FULL |
- | binlog_rows_query_log_events | OFF |
- +------------------------------+-------+
- 2 rows in set (0.01 sec)
binlog 演示
显示 binlog 列表: show binary logs;
刷新一份新的 binlog:flush logs;(现在开始的二进制日志就记录在这个新文件中)
binlog 现在是空的:(-vv: 把二进制格式的日志显示为能读懂的字符串)
mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS /var/lib/MySQL/binlog.000006
现在简单摸拟几个 SQL 操作, 然后看看 binlog:
查看下 binlog 日志:(线上开发一般都是 FULL 模式, 主要是防止程序员修改 SQL 的时候不加条件等误操作)
FULL 模式就是这样, 该行数据全部记录(修改部分其实就绿色框的地方)
想要 binlog 中记录 SQL 就开启 binlog_rows_query_log_events:
PS: 像这种操作, 如果模式选混合模式, binlog 中会记录 SQL 的
临时开启下 binlog_rows_query_log_events(如果你有需要可以配置文件设置一下)
PS:MySQL8 可通过 set persist 命令将全局变量的修改持久化到配置文件中
效果如下:
5.4. 二进制日志的清除
自动清除
配置文件中设置时间:
expire_logs_days = 30
手动清除
删除指定编号之前的日志:
purge binary logs to 'binlog.000006';
删除指定时间之前的日志:
purge binary logs before '2019-06-15 14:14:00';
已经 23:23 了, 我们快速演示下:
MySQL 命令行中执行命令:
文件列表:
5.5. 二进制日志与主从
这个把运维篇讲完会继续说, 运维篇结束后会有个高级篇(架构), 这边就简单提下二进制格式对主从复制的影响:
基于 SQL 语句的复制(SBR)
二进制日志格式使用的是 statement 格式(5.7 前的默认)
基于行的复制(RBR)
二进制日志格式使用的是基于行的日志格式
混合模式
根据实际在上面两者中切换
贴个课后拓展文章: https://www.cnblogs.com/gujianzhe/p/9371682.html
下级预估: 备份与恢复, 监控
来源: https://www.cnblogs.com/dotnetcrazy/p/11029323.html