MySQL二进制日志

简介

二进制日志包含数据库的所有更改的记录，包括数据和结构，以及每个语句执行的时间。它由一组二进制日志文件和索引组成。
这意味着将记录诸如CREATE，ALTER，INSERT，UPDATE和DELETE之类的语句，但不会记录对数据没有影响的语句，例如SELECT和SHOW。如果要记录这些（以性能为代价），请使用查询日志。
二进制日志以二进制文本格式存储，因此无法使用常规编辑器查看。需要用mysqlbinlog工具查看

二进制日志记录格式

二进制日志记录的格式有三种

• 基于语句
  MariaDB 10.2.3之前的默认格式，基于语句的日志记录记录所有对表的数据或结构进行更改的SQL语句。
• 基于行
  记录影响各个表行的事件
• 混合
  MariaDB 10.2.4的默认格式。语句和基于行的日志记录的组合。在混合日志记录中，默认情况下使用基于语句的日志记录，但是当MariaDB确定语句对于基于语句的复制可能不安全时，它将使用基于行的格式。

相关的变量

binlog_format={STATEMENT|ROW|MIXED}

想象一种场景，如果一条SQL语句使用了now()函数，插入了当前时间，这时候如果根据SQL语句，这里的时间就会发生变化，而如果基于行的记录格式则数据不会发生变化，这里只是举例，可能不准确，因为插入now()会先将now()转换为当前时间再记录，这里只是为了更好理解

开启方式

在配置文件/etc/my.cnf中的mysqld配置段加入一行

log-bin=bin.log  #这里的bin.log为二进制日志文件名默认在MySQL的data目录下，也可以写绝对路径

可以看到多了两个文件一个是bin.index是二进制的索引文件，而bin后面有一串数字的就是二进制日志了。因为这里配置的时候写了文件的扩展名（.log）这时候默认会被忽略这个扩展名。
每次服务器启动，刷新日志或者达到了最大的大小（由max_binlog_size配置项确定）时，都会创建一个带有新扩展名的新二进制日志。

查看二进制日志

mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER LOGS;

查看所有二进制文件以及文件大小

mysqlbinlog

简介

MySQL的二进制日志是一组包含“事件”的文件，表示对MariaDB数据库内容的修改。这些事件以二进制格式编写。该mysqlbinlog可以实用用于查看纯文本这些事件。

语法

shell> mysqlbinlog [options] log_file ...

常用选项

• -h, --host=name
  指定数据库服务器
• -p[passwd], --password[=passwd]
  用于连接远程服务器的密码
• -P num, --port=num
  连接数据库的端口
• -S, --socket=name
  连接数据库的套接字文件
• –start-datetime=datetime
  读取在这个日期时间之后的binlog
• -j pos, --start-position=pos
  开始读取binlog的位置
• –stop-datetime=name
  读取在这个日期时间之前的binlog
• -u, --user=username
  连接数据库的用户名

示例

查看 /usr/local/mysql/data/bin.000001 日志

mysqlbinlog  /usr/local/mysql/data/bin.000001

从8215位置开始到7917位置结束，查看 /usr/local/mysql/data/bin.000001日志

mysqlbinlog --start-position=7917 --stop-position=8215 /usr/local/mysql/data/bin.000001  

查看2018-11-2 00:54:20这个时间开始的binlog

mysqlbinlog --start-datetime='2018-11-2 00:54:20'  /usr/local/mysql/data/bin.000001  

二进制日志事件格式

看先看一个事件示例

# at 8337
#181102  0:46:00 server id 1  end_log_pos 8458 CRC32 0x6a5e0f57 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1541133960/*!*/;
/*!40000 ALTER TABLE `toc` ENABLE KEYS */
/*!*/;
DELIMITER ;

事件的起始位置：# at 8337
事件发生的日期时间：#181102 0:46:00
事件发生的服务器id：server id 1
事件的结束位置：end_log_pos 8458
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID： thread_id=2
语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
错误代码：error_code=0 表示没错误
事件内容：SET TIMESTAMP=1541133960/!/;