1. mysql的二进制日志

• 1. mysql的二进制日志
  • 1.1. 概述
  • 1.2. MySQL 中二进制日志 (binlog) 3 种不同的格式（Mixed,Statement,Row）
    • 1.2.1. Row
    • 1.2.2. Statement
    • 1.2.3. Mixed
  • 1.3. binglog格式设置
  • 1.4. 二进制日志文件的清理
    • 1.4.1. 自动清理binglog
    • 1.4.1. 修改过期时间
    • 1.4.2. 手动清除binlog文件
    • 1.4.2. 如果以上方式无法清理，使用如下特殊方式
  • 1.5. 二进制日志文件的分析
    • 1.5.1. SHOW BINLOG EVENTS查看
    • 1.5.2. mysqlbinlog方式查看
    • 1.5.3. 如何在ROW格式中输出原生的DML语句？
  • 1.6. 解析binLog恢复数据
• 2. mysql的中继日志

1.1. 概述

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句（DML（增、删、改））。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。

1.2. MySQL 中二进制日志 (binlog) 3 种不同的格式（Mixed,Statement,Row）

MySQL 5.5 中对于二进制日志 (binlog) 有 3 种不同的格式可选：Mixed,Statement,Row，默认格式是 Statement。
总结一下这三种格式日志的优缺点。
MySQL Replication 复制可以是基于一条语句 (Statement Level) ，也可以是基于一条记录 (Row Level)，可以在 MySQL 的配置参数中设定这个复制级别，不同复制级别的设置会影响到 Master 端的 bin-log 日志格式。

1.2.1. Row

日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。
优点：在 row 模式下，bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或 function ，以及 trigger 的调用和触发无法被正确复制的问题。

缺点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条 update 语句：

1.2.2. Statement

每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。

优点：在 statement 模式下，首先就是解决了 row 模式的缺点，不需要记录每一行数据的变化，减少了 bin-log 日志量，节省 I/O 以及存储资源，提高性能。
因为他只需要记录在 master 上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点：在 statement 模式下，由于他是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，
以保证所有语句在 slave 端杯执行的时候能够得到和在 master 端执行时候相同的结果。另外就是，由于 MySQL 现在发展比较快，很多的新功能不断的加入，使 MySQL
的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在 statement 中，目前已经发现的就有不少情况会造成 MySQL 的复制出现问题，
主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，
可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行来记录的变化，所以不会出现类似的问题。

1.2.3. Mixed

从官方文档中看到，之前的 MySQL 一直都只有基于 statement 的复制模式，直到 5.1.5 版本的 MySQL 才开始支持 row 复制。从 5.0 开始，MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给 MySQL Replication 又带来了更大的新挑战。另外，看到官方文档说，从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。

新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

1.3. binglog格式设置

log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"

也可以在运行时动态修改binlog的格式。例如

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

1.4. 二进制日志文件的清理

当开启mysql数据库主从时，会产生大量如mysql-bin.00000* log的文件，这会大量耗费您的硬盘空间。
有三种解决方法：

1.关闭mysql主从，关闭binlog；（重启数据库生效）
2.开启mysql主从，设置expire_logs_days， mysql 8开始expire_logs_days 废弃 启用binlog_expire_logs_seconds设置binlog自动清除日志时间 ；（可以动态修改）
3.手动清除binlog文件，> PURGE MASTER LOGS TO ‘MySQL-bin.010′;

1.4.1. 自动清理binglog

flush logs;

1.4.1. 修改过期时间

# 2. vim /etc/my.cnf  //修改expire_logs_days,x是自动删除的天数，一般将x设置为短点，如10
expire_logs_days = x  //二进制日志自动删除的天数。默认值为0,表示“没有自动删除”

mysql8.0以下版本

-- mysql8.0以下版本查看当前数据库日志binlog保存时效 以天为单位，默认0 永不过期
show variables like '%expire_logs_days%';
-- mysql8.0以下版本通过设置全局参数expire_logs_days修改binlog保存时效 以天为单位，默认0 永不过期
set global expire_logs_days=5;

mysql8.0以上版本

-- mysql8.0以下版本查看当前数据库日志binlog保存时效 以秒为单位
show variables like '%binlog_expire_logs_seconds%';
-- mysql8.0以下版本通过设置全局参数binlog_expire_logs_seconds修改binlog保存时间 以秒为单位；默认2592000 30天
-- 14400   4小时；86400  1天；259200  3天；
 set global binlog_expire_logs_seconds=259200;

修改之后flush logs之后就会自动清理

1.4.2. 手动清除binlog文件

1. 删除10天前的MySQL binlog日志,附录2有关于PURGE MASTER LOGS手动删除用法及示例

> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); 
> show master logs;

2. 也可以重置master，删除所有binlog文件：（不要随便使用）

reset master;  //附录3有清除binlog时，对从mysql的影响说明

1.4.2. 如果以上方式无法清理，使用如下特殊方式

查看当前保留时间
show variables like '%expire_logs_days%';

修改index文件，删除保留时间之前的文件
vi mysql-bin.index

收工rm 文件

3. PURGE MASTER LOGS手动删除用法及示例,MASTER和BINARY是同义词
   删除指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除MySQL BIN-LOG 日志，这样被给定的日志成为第一个。

> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'

实例：

> PURGE MASTER LOGS TO 'MySQL-bin.010';  //清除MySQL-bin.010日志
> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';   //清除2008-06-22 13:00:00前binlog日志
> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  //清除3天前binlog日志BEFORE，变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。

1.5. 二进制日志文件的分析

1.5.1. SHOW BINLOG EVENTS查看

首先通过SHOW BINLOG EVENTS查看二进制日志中的内容

mysql> show binlog events in 'mysql-bin.000025';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000025 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000025 | 120 | Query       |         1 |         188 | BEGIN                                 |
| mysql-bin.000025 | 188 | Table_map   |         1 |         236 | table_id: 79 (test.t1)                |
| mysql-bin.000025 | 236 | Write_rows  |         1 |         278 | table_id: 79 flags: STMT_END_F        |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+

1.5.2. mysqlbinlog方式查看

#输出整个文件
mysqlbinlog mysql-bin.000316 -vv --base64-output=decode-rows >/tmp/test.log

#按照起始位点信息输出：
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000315 --start-position=475 --stop-position=95076397 | tail -50

#按照起始时间输出
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000315 --start-datetime='2020-04-09 18:10:00' --stop-datetime='2020-04-09 18:30:00'

对于STATEMENT格式的binlog，所有的DML操作都记录在QUERY_EVENT中，而对于ROW格式的binlog，所有的DML操作都记录在ROWS_EVENT中，ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应insert，update和delete操作。

• 对于insert操作，WRITE_ROWS_EVENT包含了要插入的数据
• 对于update操作，UPDATE_ROWS_EVENT不仅包含了修改后的数据，还包含了修改前的值。
• 对于delete操作，仅仅需要指定删除的主键（在没有主键的情况下，会给定所有列

1.5.3. 如何在ROW格式中输出原生的DML语句？

MySQL实际上提供了一个参数，可以用于输出原生的DML语句，但是该语句仅仅是其注释的作用，并不会被应用。
mysql 5.6.2 引入的两个参数binlog_rows_query_log_events和binlog_row_image
对于使用row格式的binlog,个人觉得很有用;
binlog_rows_query_log_events =1
在row模式下..开启该参数,将把sql语句打印到binlog日志里面.默认是0(off);
虽然将语句放入了binlog,但不会执行这个sql,就相当于注释一样.但对于dba来说,在查看binlog的时候,很有用处.

binlog_row_image='minimal'
取值

• full（默认值）
• minimal
• noblob
  默认为full,在binlog为row格式下,full将记录update前后所有字段的值,minimal时,只记录更改字段的值和where字段的值,noblob时,记录除了blob和text的所有字段的值,如果update的blob或text字段,也只记录该字段更改后的值,更改前的不记录;

大家都知道row格式下的binlog增长速度太快,对存储空间,主从传输都是一个不小的压力.因为每个event记录了所有更改数据前后的值,不管数据是否有改动都会记录.binlog_row_image的引入就大大减少了binlog记录的数据.在结合binlog_rows_query_log_events,对于dba日常维护binlog是完全没有压力的,而且节省了硬盘空间开销,减小I/O,减少了主从传输压力;

mysql> flush logs;
mysql> set binlog_rows_query_log_events=1;
       set global binlog_rows_query_log_events=1;
mysql> insert into t1 values(3,'c');

ps:如果binlog_row_image的值为非full时, 对于一些利用binlog进行闪回的类似操作,将会存在问题.所以根据自己实际情况来决定如何使用吧!!!

1.6. 解析binLog恢复数据

用mysqlbinlog解析出日志，然后把里面的statement语句直接拷贝出来执行，这个方法是有风险的。因为有些语句的执行结果是依赖于上下文命令的，直接执行的结果很可能是错误的。
所以，用binlog来恢复数据的标准做法是，用 mysqlbinlog工具解析出来，然后把解析结果整个发给MySQL执行。

mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

这个命令的意思是，将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析出来，放到MySQL去执行。

错误处理：

mysqlbinlog: unknown variable 'default-character-set=utf8'

在执行，

mysqlbinlog --no-defaults mysql-bin.000546 -vv --base64-output=decode-rows>/tmp/test.log

查了下两个方法可以解决这个问题：

1.一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
2.二是用mysqlbinlog --no-defaults mysql-bin.000004 命令打开

2. mysql的中继日志

中继日志的清理

relay_log_purge=1

或者

set global relay_log_purge=1；
flush logs;