持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第29天，点击查看活动详情
6. 二进制日志 ( binlog )
-
binlog基本概念
binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。
binlog的特点
- binlog在MySQL的Server层实现(引擎共用)
-
binlog为逻辑日志,记录的是一条SQL语句的原始逻辑
- binlog不限制大小,追加写入,不会覆盖以前的日志.
- 默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。
开启Binlog日志有以下两个最重要的使用场景:
- 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
- 数据恢复：通过mysqlbinlog工具来恢复数据。
-
binlog日志的三种模式
-
ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。
缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
-
STATMENT（statement-based replication, SBR）：记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）, slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。
优点：日志量小，减少磁盘IO，提升存储和恢复速度
缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。
-
-
MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。
企业场景如何选择binlog的模式
- 如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement。
- 如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
- 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row 模式；但是要注意，该模式的binlog日志量增长非常快.
-
Binlog写入机制
1) binlog文件结构
MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。
比较常用的log event有：
Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。
binlog落盘策略
binlog 的写入顺序: binlog cache (write) -> OS cache -> (fsync) disk.
write表示: 写入文件系统缓存,fsync表示持久化到磁盘的时机
binlog刷数据到磁盘由参数sync_binlog进行配置
- sync_binlog=0 的时候，表示每次提交事务都只 write,不 fsync;
- sync_binlog=1 的时候，表示每次提交事务都会执行 fsync;
- sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
注意: 不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。如果设置成0，主动重启丢失的数据不可控制。设置成1，效率低下，设置成N(N>1)，则主机重启，造成最多N个事务的binlog日志丢失，但是性能高，丢失数据量可控。
-
binlog 写入流程
1. 根据记录模式和操作触发event事件生成log event.
2. 事务执行过程中，先把日志(log event) 写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
3. binlog cache，系统为每个线程分配了一片binlog cache内存 (每个线程都有自己的binlog cache，共用一份binlog文件) .
4. 事务提交的时候，执行器把binlog cache里完整的事务写入binlog中。并清空binlog cache.
-
redo log 和 binlog的区别
-
redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用
-
redo log是物理日志，记录的是“在XXX数据页上做了XXX修改”；binlog是逻辑日志，记录的是原始逻辑，其记录是对应的SQL语句
-
redo log是循环写的，空间一定会用完，需要write pos和check point搭配；binlog是追加写，写到一定大小会切换到下一个，并不会覆盖以前的日志
-
Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力
CrashSafe指MySQL服务器宕机重启后，能够保证： 所有已经提交的事务的数据仍然存在。 所有没有提交的事务的数据自动回滚。
-
-
Binlog命令操作
1. 使用sql命令查看binlog文件
-
启用Binlog
vim /etc/my.cnf --增加下面几个参数 binlog-format=ROW log-bin=mysqlbinlog server-id=1 -- 重启mysql systemctl restart mysqldbinlog-format: 文件模式
log-bin: mysql会根据这个配置自动设置log_bin为on状态，自动设置log_bin_index文件为你指定的文件名后跟.index
server-id=1: 随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器，那就可以随便指定了
-
启动成功之后，我们可以登陆查看我们的配置是否起作用
mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysqlbinlog | | log_bin_index | /var/lib/mysql/mysqlbinlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------------------+log_bin: 是否开启binlog日志
log_bin_basename: 基础文件名
log_bin_index : binlog文件的索引文件，管理所有binlog文件
sql_log_bin: 表示当前会话是否记录 bin log，默认值on(当sql_log_bin关闭后，主库服务器上的改动不记录bin log，不会复制到从库)。
- 查看binlog文件列表
mysql> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mysqlbinlog.000001 | 177 | | mysqlbinlog.000002 | 177 | | mysqlbinlog.000003 | 154 | +--------------------+-----------+- 查看正在写入的binlog文件
show master status; -
-
查看binlog文件信息
使用show binlog events命令查询到的每一行数据就是一个binlog管理事件
mysql> show binlog events; +--------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +--------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysqlbinlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 | | mysqlbinlog.000001 | 123 | Previous_gtids | 1 | 154 | | | mysqlbinlog.000001 | 154 | Stop | 1 | 177 | | +--------------------+-----+----------------+-----------+-------------+-----------参数说明
-
Log_name：当前事件所在的binlog文件名称；
-
Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数。
第一个事件位置并不是从0开始，而是从4。Mysql通过文件中的前4个字节，来判断这是不是一个binlog文件。这种方式很常见，很多格式文件，如pdf、doc、jpg等，都会通常前几个特定字符判断是否是合法文件。
-
Event_type：表示事件的类型；
-
Server_id：表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置；
-
End_log_position：下一个事件的开始位置；
-
Info：当前事件的描述信息。
-
查看指定binlog文件内容
mysql> show binlog events in 'mysqlbinlog.000001'\G; *************************** 1. row *************************** Log_name: mysqlbinlog.000001 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 123 Info: Server ver: 5.7.30-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysqlbinlog.000001 Pos: 123 Event_type: Previous_gtids Server_id: 1 End_log_pos: 154 Info: *************************** 3. row *************************** Log_name: mysqlbinlog.000001 Pos: 154 Event_type: Stop Server_id: 1 End_log_pos: 177 Info: 3 rows in set (0.00 sec)
-
2. 使用mysqlbinlog命令查看binlog文件
-
mysql给我们提供了一个用于查看binlog日志的工具，叫做mysqlbinlog
[root@localhost mysql]# mysqlbinlog mysqlbinlog.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #220125 6:40:35 server id 1 end_log_pos 123 CRC32 0x1e570724 Start: binlog v 4, server v 5.7.30-log created 220125 6:40:35 at startup ROLLBACK/*!*/; BINLOG ' 4wvwYQ8BAAAAdwAAAHsAAAAAAAQANS43LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADjC/BhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA ASQHVx4= '/*!*/; # at 123 #220125 6:40:35 server id 1 end_log_pos 154 CRC32 0x43fa19f1 Previous-GTIDs # [empty] # at 154 #220125 6:41:17 server id 1 end_log_pos 177 CRC32 0x205de899 Stop SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;-
输出指定position位置的binlog日志
mysqlbinlog --start-position='154' --stop-position='177' -
输出指定position位置的binlog日志到指定文件中
mysqlbinlog --start-position='154' --stop-position='177' mysqlbinlog.000001 > test.log -
输出指定position位置的binlog日志到压缩文件中
mysqlbinlog --start-position="指定开始位置" --stop-position="指定结束位置" binlog文件|gzip > 压缩文件名 -
输出指定开始时间的binlog日志
mysqlbinlog --start-datetime="yyyy-MM-dd HH:mm:ss" binlog文件
-
