MySQL二进制日志与慢日志

# 二进制日志(binlog)

# 介绍

二进制日志是一个二进制文件，它记录着数据库中所有变更类操作的日志。二进制日志主要用于进行主从同步、数据恢复、审计等操作。

# 配置

在配置文件中添加以下参数，配置完后重启MySQL服务即可。

# server_id=[Num] - MySQL服务实例ID (Num<65535)
# log_bin=/data/binlog/mysql-bin - 开启和指定二进制日志文件存放路径和前缀
- 1 - 打开二进制日志，但是日志放在默认位置
- [日志路径] - 开启二进制日志，同时指定日志文件存放路径和前缀
  - 如/data/binlog/mysql-bin中，mysql-bin就是日志名前缀，MySQL最终会生成类似于mysql-bin.0001的日志文件。
  - 另外注意存放日志的目录mysql需要有权限。
# binlog_format=row - 指定binlog日志行变化格式
- row - RBR模式(建议)
  - 行模式，记录数据行变化，比如1变成了2，与redo日志不同的是，它仅记录数据行的变化。
  - 可读性较弱，对于范围操作日志量大，但是严谨性高不会出现记录错误。
- statement - SBR模式
  - 语句模式记录SQL日志，执行什么SQL命令就记录什么命令。
  - 可读性较强，对于范围操作比RBR日志量小，但是可能出现记录不准确的情况。比如有用到now()函数的SQL命令，通过日志恢复数据时，时间就会不对。
# sync_binlog=1 - 控制binlog日志刷写磁盘的策略
- 0 - 将binlog缓冲写到文件系统缓存中，然后由文件系统自己控制它的缓存的刷新。
  - 这样子性能最好，但是风险也最大，因为一旦系统宕机，在文件系统缓冲中的binlog信息都会被丢失。
- 1 - 每次事务提交时，都实时刷写二进制日志到磁盘。

# binlog的记录方式

DDL和DCL语句会以语句的方式，原原本本的记录。
而DML则仅记录已提交的事务的语句，它的记录格式由binlog_format参数决定。

# binlog的记录单元

二进制日志的最小单元是event(事件)，每个event事件有开始和结束的号码，方便我们从日志中截取我们想要的日志事件。
对于DDL等语句来讲每一个语句就是一个事件。对于DML语句，一个事务中包含多个事件，例如：
```
begin; 事件1
a 事件2
b 事件3
commit; 事件4
```
1
2
3
4

# 二进制日志管理

# 查看二进制日志文件存放位置
- show variables like '%log_bin%';
# 滚动一个新的binlog日志
- flush logs;
# 查看所有已存在的二进制日志
- show binary logs;
# 查看正在使用的二进制日志
- show master status;
- MySQL一次只会使用一个二进制日志。
# 查看二进制事件
- show binlog events in '[当前使用日志]';
# 查看二进制日志文件内容
- mysqlbinlog --base64-output=decode-rows -vvv [日志文件路径]
- SET没有什么用，可以使用grep排除掉。
- @1=2 /* INT meta=0 nullable=1 is_null=0 */ - @1=2往表里第1列插入2。

# 基于二进制日志的恢复

# 先确认MySQL当前使用的是哪个binlog日志文件。
- show master status;
# 然后我们查看binlog日志事件找出要进行恢复操作的起始事件号和结束事件号。
- show binlog events in '[正在使用的日志名]';
- 然后我们记录开始号码(Pos)和结束号码(End_log_pos)。
- 如果很长，我们可以使用mysql -e参数以非交互式方式执行SQL语句，然后通过grep命令筛选。
# 截取二进制日志并导出为SQL文件。
- mysqlbinlog --start-position=[开始号码] --stop-position=[结束号码] [binlog日志文件路径] > [备份文件路径].sql
- 另外还可以指定-d [库名]参数过滤出指定库的日志，但不能直接过滤表，因为没有标签。截取出来的文件和SQL文件没有区别了，可以直接做数据恢复。

# 恢复MySQL数据。

恢复之前需要先修改log_bin参数将二进制日志临时关闭，然后再进行恢复。

# 进入MySQL交互式管理界面
mysql -uroot -p

# 暂时binlog日志参数
> set sql_log_bin=0;

# 恢复数据
> source [sql文件];

# 恢复后启动binlog日志参数，或者关闭会话重新进入即可
set sql_log_bin=1;

1
2
3
4
5
6
7
8
9
10
11

# 二进制日志恢复的缺陷

日志中的数据很混乱，其他库的日志可能会混在要恢复的库的日志中间，需要过滤出来。并且在数据量大时，即使能找到，恢复会非常慢。
所以一般二进制日志恢复，只能作为数据恢复的辅助手段。一般会使用最近的数据备份+二进制日志来进行恢复。
- 先使用数据备份恢复，然后使用二进制日志，恢复备份时间到损坏之前的时间这个范围的数据即可。
- 起始点在数据备份时会记录在备份文件中，一般找到结束点也就是故障的时刻就行了。

# binlog的gtid记录模式的管理

# GTID介绍

GTID即是全局事务标识，对于binlog中的每一个事务，都会生成一个GTID号码。
DDL、DCL语句在GTID中，一个语句就是一个事务，有一个GTID号。
DML语句在GTID中，begin到commit是一个事务，有一个GTID号。

# GTID的配置

在配置文件中添加以下参数，配置完后重启MySQL服务即可。

gtid_mode=on - 开启GTID模式
enforce_gtid_consistency=on - 强制GTID一致性
它不影响已有的事务，仅影响开启GTID模式之后的事务，开启之后每执行一个事务，都会有对应的GTID号。
另外需要注意，如果主库开启了GTID配置，则从库也需要开启GTID配置。

# GTID的字符串组成

# server_uuid:TID
- 例如：如: 3bdc6cf2-b2c4-11...:1~n
# server_uuid
- 每个MySQL实例的server_uuid都是不一样的，它存放在/[数据路径]/auto.cnf中，如：server-uuid=3bdc6cf2-b2c4-11...
# TID
- TID是一个自增长的数据，从1开始(1~n)。

# GTID的幂等性

GTID的幂等性会影响到binlog日志恢复和主从复制。
如果拿有GTID的日志去进行恢复，会检查当前系统中是否有相同的GTID号，有相同的就自动跳过。
因为幂等性的检查，比如1-3事务已经做过，那么恢复时可能就会被跳过，即便该事务涉及的数据已经被删掉，所以需要完全恢复需要使用--skip-gtids参数。

# 查看GTID号

show master status; - 查看当前使用的bin日志和GTID号的总个数。
show binlog events in '[当前使用日志]'; - 会在每一条事务前多设置一个GTID号，方便截取binlog日志数据进行恢复。

# 基于GTID号的binlog日志数据恢复

# 先确认MySQL当前使用的是哪个binlog日志文件。
- show master status;
# 查看事件，找出要进行恢复操作的起始事件GTID号和结束事件GTID号。
- show binlog events in '当前使用日志';
- 然后我们记录开始GTID号和结GTID束号，可以跨多个事件。
# 导出数据时，指定GTID号的起始和结束，截取出日志，并添加跳过幂等性检查参数。
- mysqlbinlog --skip-gtids --include-gtids="3bdc6cf2-b2c4...:1-5" mysql-bin.000016 > crisback.sql
- --skip-gtids - 在导出时，忽略原有的gtid信息，恢复时生成新的gtid信息。
- --include-gtids="[GTID]" - 也就是指定GTID："server_uuid:TID"。
- --exclude-gtids="[GTID]" - 跳过导出指定GTID号的事务，可指定单个、多个、连续的，多个时在引号外用逗号隔开，例如：1、1-3、"[GTID]","[GTID]","[GTID]"...
# 恢复MySQL数据。

恢复之前需要先修改log_bin参数将二进制日志临时关闭，然后再进行恢复。

# 进入MySQL交互式管理界面
mysql -uroot -p

# 暂时binlog日志参数
> set sql_log_bin=0;

# 恢复数据
> source [sql文件];

# 恢复后启动binlog日志参数，或者关闭会话重新进入即可
set sql_log_bin=1;

1
2
3
4
5
6
7
8
9
10
11

# 二进制日志的清理

数据如果已经备份了的话，二进制相应的数据就可以不需要了。
另外对于数据库相关的文件或者对象进行操作时，应当尽量使用数据库内部的命令，而非操作系统层面的命令。

# 配置自动清理

# 查看当前清理策略参数
- show variables like '%expire%';
# 修改配置参数
- 在配置文件中添加以下参数，配置完后重启MySQL服务即可。
- expire_log_days=[过期时间周期]; - 一般会设为大于2倍数据库全备周期的过期时间
  - 比如：每隔7天做一个全备，就设为15天。

# 手动清理

purge binary logs to '[二进制日志名]'; - 删除指定日志
purge binary logs before '2008-04-02 22:45:25'; - 删除指定时间之前日志
reset master; - 全部清除，后缀号也从0开始。
- 主从环境中，主库如果使用，从库必崩溃，因为从库需要的binlog位置之后的数据已经没有了。

# 滚动binlog日志

flush logs; - 滚动出一个新的二进制日志并使用。
另外数据库重启时，也会触发binlog日志的滚动。或者在二进制日志文件达到最大大小时，也会触发滚动，默认最大大小为1GB，可以通过max_binlog_size参数进行调整。

# 慢日志(slowlog)

# 介绍

慢日志用于记录MySQL中运行较慢的语句，然后我们就可以分析这些执行慢的SQL看如何进行优化。

# 配置

在配置文件中添加以下参数，配置完后重启MySQL服务即可。

slow_query_log=1 - 开启慢日志
slow_query_log_file=[文件路径].log - 指定慢日志文件路径
long_query_time=0.7 - 设定慢查询时间阈值/单位秒，也就是多久算慢
- 默认是10秒，不太合适。
- 慢慢达到标准后，可以要求越来越快，设的越来越小，最终优化到0.1秒。
log_queries_not_using_indexes=on - 没走索引的语句也记录，默认是开启的

# 分析

慢日志文件可以直接通过vim打开，超过时间阈值的语句会被完整的记录下来。但我们一般会先通过工具对慢日志进行排序，然后再看看常用SQL哪些比较慢。
mysqldumpslow -s c -t 10 [慢日志文件]
- -s c - 表示按语句使用次数排序
- -t n - 仅显示前n个
另外我们还可以通过第三方的分析工具来进行分析，比如pt-query-diagest或Anemometer。

#数据库 #MySQL #存储引擎 #二进制日志 #binlog #慢日志

← MySQL存储引擎与事务 MySQL数据备份→

MySQL二进制日志与慢日志

# 二进制日志(binlog)

# 介绍

# 配置

# server_id=[Num] - MySQL服务实例ID (Num<65535)

# log_bin=/data/binlog/mysql-bin - 开启和指定二进制日志文件存放路径和前缀

# binlog_format=row - 指定binlog日志行变化格式

# sync_binlog=1 - 控制binlog日志刷写磁盘的策略

# binlog的记录方式

# binlog的记录单元

# 二进制日志管理

# 查看二进制日志文件存放位置

# 滚动一个新的binlog日志

# 查看所有已存在的二进制日志

# 查看正在使用的二进制日志

# 查看二进制事件

# 查看二进制日志文件内容

# 基于二进制日志的恢复

# 先确认MySQL当前使用的是哪个binlog日志文件。

# 然后我们查看binlog日志事件找出要进行恢复操作的起始事件号和结束事件号。

# 截取二进制日志并导出为SQL文件。

# 恢复MySQL数据。

# 二进制日志恢复的缺陷

# binlog的gtid记录模式的管理

# GTID介绍

# GTID的配置

# GTID的字符串组成

# server_uuid:TID

# server_uuid

# TID

# GTID的幂等性

# 查看GTID号

# 基于GTID号的binlog日志数据恢复

# 先确认MySQL当前使用的是哪个binlog日志文件。

# 查看事件，找出要进行恢复操作的起始事件GTID号和结束事件GTID号。

# 导出数据时，指定GTID号的起始和结束，截取出日志，并添加跳过幂等性检查参数。

# 恢复MySQL数据。

# 二进制日志的清理

# 配置自动清理

# 查看当前清理策略参数

# 修改配置参数

# 手动清理

# 滚动binlog日志

# 慢日志(slowlog)

# 介绍

# 配置

# 分析

# `server_id=[Num]` - MySQL服务实例ID (Num<65535)

# `log_bin=/data/binlog/mysql-bin` - 开启和指定二进制日志文件存放路径和前缀

# `binlog_format=row` - 指定binlog日志行变化格式

# `sync_binlog=1` - 控制binlog日志刷写磁盘的策略