聊聊数据库~6.SQL运维中篇

上篇回顾：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(中继日志)	用于主从复制，临时存储主从同步的二进制日志（增量复制）

知识扩展：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、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

PS：SQLServer的ErrorLog也是差不多的

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用的比较多：

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

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语句

1. Time：查询的执行时间（start_time）
2. User@Host: root[root] @ localhost [] Id:11：执行 sql 的主机信息
3. Query_time：SQL查询所耗的时间
4. Lock_time：锁定时间
5. Rows_sent：所发送的行数
6. Rows_examined：锁扫描的行数
7. SET timestamp=1558530988;：SQL执行时间

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

1. 自带的慢日志分析工具：mysqldumpslow
2. 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语句：

1. 翻页sql不一样，性能也是不一样的，越往后的页数越容易出现慢查询，而mysqldumpslow把所有翻页sql当成一个sql了
2. 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

1. 使用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
2. 查看来自远程进程列表上最慢的查询：
   • pt-query-digest --processlist h=ip

安装可以参考：https://github.com/lotapp/awesome-tools/blob/master/README.md#4运维

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日志主要分为这两类：（互不干扰）

1. 服务层日志（和使用存储引擎无关）
   • 通用日志、慢查询日志、二进制日志
2. 存储引擎层日志
   • eg：innodb的重做日志(redo log)和回滚日志(undo log)

Q：那什么样的修改会记录下来呢？

A：记录所有对MySQL数据库的修改事件（包括增删改查事件和对表结构修改的事件），而且只记录已经成功执行的事件（失败的不会记录）

这么说可能有点抽象，熟悉SQLServer的同志看个图就秒懂：

5.1.二进制日志格式

参数	说明
STATEMENT	基于段的格式，记录执行数据修改时候所执行的SQL语句
ROW	基于行的格式，记录增删改查操作所修改行的信息（每修改一行就会有一条信息）
MIXED	基于行和端的混合格式，根据SQL语句由系统决定是基于段还是基于行的日志格式记录

查看方式：show variables like 'binlog_format';

1. binlog_format=statement：基于段的记录格式（老版本的默认值）
   1. 优点：记录量较小，节约磁盘和网络IO（单条操作Row更节约）
   2. 缺点：必须记录上下文信息来保证语句在从服务器上执行结果与主服务器相同
      • 但是如果使用了uuid()、user()等结果非确定的函数，可能会造成MySQL主从不一致
   3. 日志查看：mysqlbinlog /var/lib/mysql/binlog.0000xx | more（不用指定参数）
2. binlog_format=row：基于行的记录格式（5.7以后的默认值）
   1. 优点：可以避免MySQL复制中出现的主从不一致的问题（主从更安全）
      • PS：没有备份的时候可以通过分析row格式的二进制日志来反向恢复
   2. 缺点：记录日志量较大（顺序写入）
      • 现在增加了新参数来优化：binlog_row_image=[full|minimal|noblob]
   3. 日志查看：mysqlbinlog -vv /var/lib/mysql/binlog.0000xx | more
3. binlog_format=mixed：基于行和端的混合格式（推荐）
   • PS：数据量大小由所执行的SQL决定（非确定性函数越多，行数越多）

PS：DDL操作（create、drop、alter）的时候都是基于段方式来记录log

如果一条一条记录，表有上亿数据，我就修改某列的状态值，那不得疯？

对binlog_row_image=[FULL|MINIMAL|NOBLOB]的补充说明：

PS：查看方式：show variables like 'binlog_row_image'

1. 默认是full：完整
   • 记录修改行的全部内容
2. noblob：就是在full记录的基础上对大文本列的优化
   • 没有对text或者blob列修改就不记录该列
3. 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.二进制日志的清除

1. 自动清除
   • 配置文件中设置时间：expire_logs_days = 30
2. 手动清除
   • 删除指定编号之前的日志：purge binary logs to 'binlog.000006';
   • 删除指定时间之前的日志：purge binary logs before '2019-06-15 14:14:00';

已经23:23了，我们快速演示下：

MySQL命令行中执行命令：

文件列表：

5.5.二进制日志与主从

这个把运维篇讲完会继续说，运维篇结束后会有个高级篇（架构），这边就简单提下二进制格式对主从复制的影响：

1. 基于SQL语句的复制（SBR）
   • 二进制日志格式使用的是statement格式（5.7前的默认）
2. 基于行的复制（RBR）
   • 二进制日志格式使用的是基于行的日志格式
3. 混合模式
   • 根据实际在上面两者中切换

贴个课后拓展文章：https://www.cnblogs.com/gujianzhe/p/9371682.html

下级预估：备份与恢复、监控