mysql binlog

简介

事物日志

• innodb事务日志包括redo log和undo log。
• undo log指事务开始之前， 在操作任何数据之前,首先将需操作的数据备份到一个地方
• redo log指事务中操作的任何数据,将最新的数据备份到一个地方
  事务日志的目的：实例或者介质失败，事务日志文件就能派上用场。

1.redo log不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。具体 的落盘策略可以进行配置 。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。RedoLog是为了实现事务的持久性而出现的产物

2.undo log用来回滚行记录到某个版本。事务未提交之前，Undo保存了未提交之前的版本数据，Undo中的数据可作为数据旧版本快照供其他并发事务进行快照读。是为了实现事务的原子性而出现的产物,在Mysql innodb存储引擎中用来实现多版本并发控制

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

这两种日志有以下三点不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是 “在某个数据页上做了什么修改” ；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 “给 ID=2 这一行的 c 字段加 1” 。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。 “追加写” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

格式

Mysql binlog日志有ROW,Statement,MiXED三种格式；可通过my.cnf配置文件及 set global binlog_format=‘ROW/STATEMENT/MIXED’ 进行修改，命令行 show variables like ‘binlog_format’ 命令查看binglog格式；。

• Row level: 仅保存记录被修改细节，不记录sql语句上下文相关信息优点：能非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息，因此不会发生某些特定情况下的procedure、function、及trigger的调用触发无法被正确复制的问题，任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性
  缺点:由于所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多；比如一条update语句，如修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。
  tip: - row模式生成的sql编码需要解码，不能用常规的办法去生成，需要加上相应的参数(–base64-output=decode-rows -v)才能显示出sql语句; - 新版本binlog默认为ROW level，且5.6新增了一个参数：binlog_row_image；把binlog_row_image设置为minimal以后，binlog记录的就只是影响的列，大大减少了日志内容
• Statement level: 每一条会修改数据的sql都会记录在binlog中优点：只需要记录执行语句的细节和上下文环境，避免了记录每一行的变化，在一些修改记录较多的情况下相比ROW level能大大减少binlog日志量，节约IO，提高性能；还可以用于实时的还原；同时主从版本可以不一样，从服务器版本可以比主服务器版本高
  缺点：为了保证sql语句能在slave上正确执行，必须记录上下文信息，以保证所有语句能在slave得到和在master端执行时候相同的结果；另外，主从复制时，存在部分函数（如sleep）及存储过程在slave上会出现与master结果不一致的情况，而相比Row level记录每一行的变化细节，绝不会发生这种不一致的情况
• Mixedlevel level: 以上两种level的混合使用经过前面的对比，可以发现ROW level和statement level各有优势，如能根据sql语句取舍可能会有更好地性能和效果；Mixed level便是以上两种leve的结合。不过，新版本的MySQL对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更；因此，现在一般使用row level即可。
  选取规则如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录
  如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用statement模式记录

开启binlog

1. 查看是否开启 binlog日志（默认不开启，log_bin值为OF）及日志位置

show variables like '%log_bin%';

2. 若没有开启

# 修改  /etc/my.cnf,添加
log-bin=mysql-bin #添加这一行就ok
binlog-format=ROW #选择row模式
server_id=1 #配置mysql replaction需要定义，不能和canal的slaveId重复
expire_logs_days=5         #日志过期时间为5天

4. binlog命令 mysql>

mysql数据存储目录

show variables like '%dir%';

查看binlog的目录

show global variables like "%log_bin%";

查看当前服务器使用的biglog文件及大小

show binary logs;

查看最新一个binlog日志文件名称和Position

show master status;

事件查询命令

# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
 # FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
#LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

查看 binlog 内容

show binlog events;

查看具体一个binlog文件的内容 （in 后面为binlog的文件名）

show binlog events in 'master.000003';

设置binlog文件保存事件，过期删除，单位天

set global expire_log_days=3; 

删除当前的binlog文件

reset master; 

删除slave的中继日志

reset slave;

删除指定日期前的日志索引中binlog日志文件

purge master logs before '2019-03-09 14:00:00';

删除指定日志文件

purge master logs to 'master.000003';

mysqlbinlog shell命令

shell > mysqlbinlog [options] logfile1 logfile2 ...

-d, --database=name      仅显示指定数据库的转储内容。
-o, --offset=#           跳过前N行的日志条目。
-r, --result-file=name   将输入的文本格式的文件转储到指定的文件。
-s, --short-form         使用简单格式。
--set-charset=name       在转储文件的开头增加'SET NAMES character_set'语句。
--start-datetime=name    转储日志的起始时间。
--stop-datetime=name     转储日志的截止时间。
-j, --start-position=#   转储日志的起始位置。
--stop-position=#        转储日志的截止位置。

> mysqlbinlog mysql-bin.000001
#查询mysql-bin.000001中记录的操作，会显示sql语句,　要进入到mysql的安装目录中
>   mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001;

数据恢复

mysqlbinlog --start-position=2200  -database=dname  /var/lib/mysql/mysql-bin.000001 /usr/bin/mysql -u root -p xxxxxx -v dname

5. 阿里开源框架canel