MySQL二进制日志

二进制日志（备份）

• 只记录增删改，整个数据库的修改过程
• 利用二进制日志可以通过恢复操作来恢复数据库
• 有的版本默认没有开启二进制日志
• 不依赖于存储引擎类型

二进制日志记录三种格式

• 语句型：statement 记录执行语句 更加节约空间，不详细，重复执行结果有可能不一样，在不同的时间点，结果有差异
• 行型：row 记录所有产生的操作，更加占用空间，但是更加详尽，记录的全面的
  例如不会随着时间产生差异
• 混合型：mixed, 由mysql自己决定，结合了相中特性，不靠谱，还不如都都用行踏实

#MariaDB 默认使用MIXED方式
MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)
#MySQL 8.0 默认使用ROW方式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.07 sec)

启用二进制日志

• 通过log_bin和sql_log_bin开启二进制日志
• mysql8.0默认是开启的
• mariadb 默认是不开启的
• 修改配置文件在[mysql]中添加log-bin=路径/文件名

MySQL root@(none):(none)> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1             |
+---------------+
1 row in set
Time: 0.006s
MySQL root@(none):(none)> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1         |
+-----------+
1 row in set
Time: 0.005s

• 两个开关都开启，才能开启二进制日志
• sql_log_bin 是非全局会话级别的可以临时生效的变量，临时性的关闭，在还原过程中不需要记录，
• log_bin 不支持动态修改，是服务器选项可以写到配置文件中直接创建函数是不允许的，需要改东西
• 生产中单独存放二进制文件

二进制日志文件的构成

有两类文件

1. 日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
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，即不自动删除

• binlog_format 规定了哪些格式记录在二进制文件中默认值有的不一样
• max_binlog_size= 设置二进制文件的最大值
• expore_logs_days= 二进制日志自动删除的天数，可以写30天、60天90天
• binlog_cache_size= 生成二进制文件时缓存使用的大小
• sync_binlog=1 | 0 写磁盘时的操作，类似于事务日志

二进制日志相关配置操作

• 查看mariadb自行管理使用中的二进制日志文件列表，及大小
  SHOW {BINARY | MASTER} LOGS
• 查看使用中的二进制日志文件
  SHOW MASTER STATUS
• 在线查看二进制文件中的指定内容
  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

二进制日志事件的格式

# 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

二进制日志其他操作

• 只要重启服务就新增binlog
• 清理二进制文件
• purge binary logs to 删掉之前的二进制日志文件，并更新index
• reset master； 把所有的二进制都删掉了
• flush logs; 刷新二进制文件
  希望新发生的事件记录到新日志中，可以刷新

清除指定二进制日志

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 #

切换日志文件：

[root@centos8 ~]#mysqladmin flush-binary-log
[root@centos8 ~]#mysqladmin flush-logs
[root@centos8 ~]#mysql
MariaDB [hellodb]> flush logs;