一、简介
binlog日志也称二进制日志，记录了所有的DDL和DML( 除了数据查询语句 )语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
一般来说开启二进制日志大概会有1%的性能损耗。二进制有两个最重要的使用场景:
- 主从复制
- 恢复数据
二、master节点开启binlog
[root@mysql mysql-5.7.36]# cat /etc/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data server-id=1 #日志前缀为mysql-bin log-bin=mysql-bin #日志过期时间 expire_logs_days=30 #定义存储格式 binlog_format=row [client] socket = /tmp/mysql.sock [root@mysql mysql-5.7.36]#
三、查看bin-log相关信息
3.1 show variables like 'log_%';
log_bin：ON 表示已开启binlog日志
log_bin_basename：binlog日志的存储位置
log_bin_index：binlog日志索引文件的位置
mysql> show variables like 'log_%'; +----------------------------------------+---------------------------------------+ | Variable_name | Value | +----------------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_builtin_as_identified_by_password | OFF | | log_error | ./mysql.err | | log_error_verbosity | 3 | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_statements_unsafe_for_binlog | ON | | log_syslog | OFF | | log_syslog_facility | daemon | | log_syslog_include_pid | ON | | log_syslog_tag | | | log_throttle_queries_not_using_indexes | 0 | | log_timestamps | UTC | | log_warnings | 2 | +----------------------------------------+---------------------------------------+ 21 rows in set (0.00 sec) mysql>
3.2 show variables like 'binlog%';
binlog_format：binlog日志存储格式
mysql> show variables like 'binlog%'; +--------------------------------------------+--------------+ | Variable_name | Value | +--------------------------------------------+--------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | +--------------------------------------------+--------------+ 15 rows in set (0.00 sec) mysql>
3.3 binlog日志文件查看乱码
直接用vim查看binlog日志文件是乱码的，这是经由base64编码之后的结果，可以在通过 mysqlbinlog 查看 binlog 日志时添加参数进行解码
mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000001
四、binlog编码格式
binlog有三种编码格式：row statement mixed
可用 show variables like 'binlog_format'; 查看默认的编码格式，也可在 /etc/my.cnt 设置编码格式
4.1 row 基于行的复制(row-based replication, RBR)
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发或 now() 无法被正确复制的问题
优点：保持数据的绝对一致性。因为不管sql是什么，引用了什么函数，它只记录执行后的效果
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨
4.2 statement 基于SQL语句的复制(statement-based replication, SBR)
binlog会记录每次一执行写操作的语句。
相对row模式节省空间，但是可能产生不一致性，例如：update table_name set create_date=now();
如果用binlog日志进行恢复，由于执行时间不同可能产生的数据就不同 ( master落库数据时create_date为2021-08-08 11:10:30 ，但binlog从库落库执行语句时create_date的时间可能就变为2021-08-08 11:11:23 ，主要是语句执行时间为异步)
优点： 节省空间
缺点： 有可能造成数据不一致
4.3 mixed 混合模式复制(mixed-based replication, MBR)
statement的升级版，一定程度上解决了因一些情况而造成的statement模式不一致问题，例如以下情况，会按照 ROW的方式进行处理
- 当函数中包含 UUID() 时
- 包含 AUTO_INCREMENT 字段的表被更新时
- 执行 INSERT DELAYED 语句时
- 用 UDF 时
优点：节省空间，同时兼顾了一定的一致性
缺点：还有些极个别情况依旧会造成不一致，另外statement和mixed对于需要对binlog的监控的情况都不方便
五、通过binlog恢复数据
5.1 建库建库建数据
create database test; use test create table t1(id int(11),name char(50)); insert into t1 set id=1,name='tom'; insert into t1 set id=2,name='jer';
5.2 删除一条数据，模拟数据丢失，再恢复
5.3 恢复数据
执行命令 flush logs; 开启新的日志记录，这样就不会收到后面操作的干扰
5.3.1 使用pos恢复数据
show binlog events in 'mysql-bin.000013'; 查看日志结构，选出插入那条数据前后的pos，进行恢复
#进入到binlog目录下 cd /usr/local/mysql/data/ #数据恢复 mysqlbinlog --start-position=558 --stop-position=818 --database=test mysql-bin.000003 | mysql -uroot -pyy123456 -v test
5.3.2 使用时间恢复数据（也可用对应at的值进行恢复）
#进入到binlog目录下 cd /usr/local/mysql/data/ #数据恢复
mysqlbinlog --start-datetime='2022-05-09 10:51:12' --stop-datetime='2022-05-09 10:51:42' --database=test mysql-bin.000002 | mysql -uroot -pyy123456 -v test
5.3.3 日志结构
- 【Log_name】:日志名称
- 【pos】上次操作点的序号
- 【event_type】事件类型
| 事件类型 | 说明 |
| FORMAT_DESCRIPTION_EVENT | 代表binlog日志的第一条，且只会在第一次出现 |
| Previous_gtids | 开启GTID模式（主从复制）后，每个binlog开头都会有一个（在后简称PE）事件，它的值是上一个binlog的PE+GTID信息 |
| Query | 记录更新操作的语句，包括：create，insert，update，delete |
| Write_rows | 在开启ROW模式记录的binlog文件中，记录了插入的行记录 |
| Delete_rows | 在开启ROW模式记录的binlog文件，记录了删除的行记录 |
| Update_rows | 在开启ROW模式记录的binlog文件，记录了更新的行记录 |
| Xid | 当事务提交时，无论哪种模式都会记录 |
| Rotate | 当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时，binlog会发生切换，这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件，用于指定下一个日志的名称和位置 |
| Gtid_log | 在启用GTID模式（主从复制模式）后，MySQL将为每个事务都分配了个GTID |
| Stop | 当MySQL数据库停止时，会在当前的binlog末尾添加一个事件表示数据库停止 |
5.3.4 日志内容
| 内容 | 说明 |
| postion（描述） | at后面的数字代表在binlog日志文件的第几个字节开始（at 4 ） |
| timestamp（事件发生的时间戳） | 即第二行的（#220509 10:34:34） |
| server id（服务器标识） | （1），代表执行的主机编号，/etc/my.cnt里所配置的 |
| end_log_pos（结束字节数） | 结束的字节位置 123 |
| Query（类型） | 事件类型 |
| thread_id | 处理的线程编号 |
| exec_time | 执行花费的时间 |
| error_code | 错误码 |
| SET TIMESTAMP=1652064328/*!*/; | 代表执行的时间戳 |
| ### INSERT INTO `test`.`t1` | 代表执行的语句，遇到下一个#at 则为下一个binlog日志事件 |
六、其他
6.1 binlog文件大小
默认是1G，如果超过了1G，或者使用 flush logs; ，或者重启mysql的时候，就会新增一个binlog文件
可用 show variables like 'max_binlog_size'; 查看文件大小
6.2 过期删除
可以使用 expire_logs_days=30 来配置日志保存时间，最好不要自己去删除binlog日志，这样会导致过期删除出错，如果非要删除，要记得更新一下 xxxxx.mysql-bin.index
6.3 其他命令
- 查看全部的日志 show master logs;
- 查看日志的最后一次操作 show master status;
- 刷新binlog日志，也就是新开启一个日志文件 flush logs (这个在恢复数据的时候很有用)
6.4 sync_binlog
- sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
- sync_binlog=1，强一致，每次事物提交都进行磁盘同步。
- sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘