数据库二进制日志

二进制日志记录三种格式

1. 基于“语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
2. 基于“行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式
3. 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

格式配置

MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)

二进制日志文件的构成

有两类文件

1. 日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
   如： mariadb-bin.000001

2. 索引文件：mysql|mariadb-bin.index，文本格式

二进制日志相关的服务器变量

sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项

log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以

binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，默认STATEMENT

max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值

binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）

max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。

sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除

二进制日志文件相关配置

查看mariadb自行管理使用中的二进制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

查看使用的二进制文件

SHOW MASTER STATUS

在线查看二进制文件的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

例：
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
格式：

mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime=
 --base64-output[=name]
        -v -vvv

范例：

mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v
mysqlbinlog  --start-datetime="2018-01-30 20:30:10"   --stop-datetime="2018-01-
30 20:35:22" mariadb-bin.000003 -vvv

二进制日志文件的格式

at 328
151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1    
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

清除指定二进制日志

 PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
 
 例：
 PURGE BINARY LOGS TO 'mariadb-bin.000003';  #删除mariadb-bin.000003之前的日志
 PURGE BINARY LOGS BEFORE '2017-01-23';
 PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

删除所有二进制日志，index文件重新记数

RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #

切换日志文件

FLUSH LOGS;

注：二进制日志涉及备份集群配置等，尤为重要