MySQL binlog是MySQL数据库的DDL 和DML (除了数据查询语句select、show等)操作的逻辑重放,他像Oracle redo 但不同于Oracle redo,MySQL binlog记录的不光是逻辑日志,还是数据库层的逻辑操作,众所周知,由于MySQL架构设计问题,MySQL作为一个支持多种存储引擎的开放式数据库,那么他就会有不同于存储引擎的日志记录,它就是binlog,不同于innodb 的redo。
Binlog 的日志格式
记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型:
STATEMENT:基于SQL语句的复制(statement-based replication, SBR),每一条会修改数据的sql都会记录在binlog中,不记录每一行的变化,减少了binlog日志量,但是由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制,像一些特定函数的功能,slave与master要保持一致会有很多相关问题。
ROW:基于行的复制(row-based replication, RBR),不记录sql语句上下文相关信息,仅保存哪条记录被修改,所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题,但是由于记录的内容详细也会产生大量的日志内容
注:将二进制日志格式设置为ROW时,有些更改仍然使用基于语句的格式,包括所有DDL语句,例如CREATE TABLE, ALTER TABLE,或 DROP TABLE。
MIXED:混合模式复制(mixed-based replication, MBR),一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。
在 MySQL 5.7.7 之前,默认的格式是 STATEMENT,在 MySQL 5.7.7 及更高版本中,默认值是 ROW。日志格式通过 binlog-format 指定 binlog_format=ROW
mysql> show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
binlog的作用
①MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
②数据恢复:通过使用 mysqlbinlog工具来使恢复数据
binlog参数
对支持事务的引擎如InnoDB而言,必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘呢,这跟参数 sync_binlog 相关。
sync_binlog=0:表示MySQL不控制binlog的刷新,由文件系统去控制它缓存的刷新;
sync_binlog=N:表示每 N 次事务,MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
sync_binlog=1:设为1是最安全的,在系统故障时最多丢失一个事务的更新,但是会对性能有所影响。
如果 sync_binlog=0 或 sync_binlog大于1,当发生电源故障或操作系统崩溃时,可能会丢失一部分已提交但其binlog未被同步到磁盘的事务信息,数据库在启动时将无法恢复这部分事务。
在MySQL 5.7.7之前,默认值 sync_binlog 是0,MySQL 5.7.7和更高版本使用默认值1,这是最安全的选择。
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
binlog日志文件
二进制日志索引文件(文件名后缀为.index)用于记录所有有效的的二进制文件
二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML语句事件
binlog是一个二进制文件集合,每个binlog文件以一个4字节的魔数开头,接着是一组Events:
max_binlog_size 控制binlog文件的大小,其最小值是4096字节,最大值和默认值是 1GB (1073741824字节)。事务被写入到binlog的一个块中,它不会在几个二进制日志之间被拆分。如果遇到很大的事务,为了保证事务的完整性,需要将该事务的日志都记录到当前日志文件中,直到事务结束,所以会出现binlog文件大于 max_binlog_size 的情况。
-rw-r-----. 1 mysql mysql 2322 11月 29 16:04 mysql-bin.000003
-rw-r-----. 1 mysql mysql 156 11月 29 16:04 mysql-bin.000004
-rw-r-----. 1 mysql mysql 54 11月 29 16:04 mysql-bin.index
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)
查看binlog内容
创建测试数据
mysql> create database CAP;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| cap |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> use cap
Database changed
mysql> create table cap_tab(id int,name varchar(20)) ;
Query OK, 0 rows affected (0.03 sec)
mysql> show create table cap_tab;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cap_tab | CREATE TABLE `cap_tab` (
`id` int DEFAULT NULL,
`name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> insert into cap_tab values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into cap_tab values(2,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into cap_tab values(3,'wangwu');
Query OK, 1 row affected (0.01 sec)
mysql>
①mysqlbinlog可以读取二进制binlog文件
mysqlbinlog可以读取二进制binlog文件
# mysqlbinlog 的执行格式
mysqlbinlog [options] log_file ...
# 查看bin-log二进制文件(shell方式)
mysqlbinlog -v --base64-output=decode-rows mysql-bin.000003
# 查看bin-log二进制文件(带查询条件)
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
--start-datetime="2019-03-01 00:00:00" \
--stop-datetime="2019-03-10 00:00:00" \
--start-position="5000" \
--stop-position="20000"
[mysql@MySQL8-SmallA ~]$ /home/mysql/mysql8.0/bin/mysqlbinlog -v --base64-output=decode-rows mysql-bin.000003
# at 1095
#211129 15:53:00 server id 15133306 end_log_pos 1200 CRC32 0xc5edd39a Query thread_id=16 exec_time=0 error_code=0 Xid = 258
SET TIMESTAMP=1638172380/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database CAP
/*!*/;
# at 1200
#211129 15:54:02 server id 15133306 end_log_pos 1277 CRC32 0xf7acc5b4 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no original_committed_timestamp=1638172442415422 immediate_commit_timestamp=1638172442415422 transaction_length=208
# original_commit_timestamp=1638172442415422 (2021-11-29 15:54:02.415422 CST)
# immediate_commit_timestamp=1638172442415422 (2021-11-29 15:54:02.415422 CST)
/*!80001 SET @@session.original_commit_timestamp=1638172442415422*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1277
#211129 15:54:02 server id 15133306 end_log_pos 1408 CRC32 0x348c22ce Query thread_id=16 exec_time=0 error_code=0 Xid = 264
use `cap`/*!*/;
SET TIMESTAMP=1638172442/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table cap_tab(id int,name varchar(20))
/*!*/;
# at 1408
#211129 15:56:04 server id 15133306 end_log_pos 1487 CRC32 0x4e2447e1 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=yes original_committed_timestamp=1638172564133521 immediate_commit_timestamp=1638172564133521 transaction_length=291
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1638172564133521 (2021-11-29 15:56:04.133521 CST)
# immediate_commit_timestamp=1638172564133521 (2021-11-29 15:56:04.133521 CST)
/*!80001 SET @@session.original_commit_timestamp=1638172564133521*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1487
#211129 15:56:04 server id 15133306 end_log_pos 1561 CRC32 0xdae607cb Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1638172564/*!*/;
BEGIN
/*!*/;
# at 1561
#211129 15:56:04 server id 15133306 end_log_pos 1619 CRC32 0x570c1789 Table_map: `cap`.`cap_tab` mapped to number 164
# at 1619
#211129 15:56:04 server id 15133306 end_log_pos 1668 CRC32 0x5ea7a781 Write_rows: table id 164 flags: STMT_END_F
### INSERT INTO `cap`.`cap_tab`
### SET
### @1=1
### @2='zhangsan'
# at 1668
#211129 15:56:04 server id 15133306 end_log_pos 1699 CRC32 0xde2ce549 Xid = 266
COMMIT/*!*/;
# at 1699
#211129 15:56:19 server id 15133306 end_log_pos 1778 CRC32 0xe88800c5 Anonymous_GTID last_committed=7 sequence_number=8 rbr_only=yes original_committed_timestamp=1638172579377645 immediate_commit_timestamp=1638172579377645 transaction_length=287
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1638172579377645 (2021-11-29 15:56:19.377645 CST)
# immediate_commit_timestamp=1638172579377645 (2021-11-29 15:56:19.377645 CST)
/*!80001 SET @@session.original_commit_timestamp=1638172579377645*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1778
#211129 15:56:19 server id 15133306 end_log_pos 1852 CRC32 0x2aa5d962 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1638172579/*!*/;
BEGIN
/*!*/;
# at 1852
#211129 15:56:19 server id 15133306 end_log_pos 1910 CRC32 0x9996dafb Table_map: `cap`.`cap_tab` mapped to number 164
# at 1910
#211129 15:56:19 server id 15133306 end_log_pos 1955 CRC32 0x39301e35 Write_rows: table id 164 flags: STMT_END_F
### INSERT INTO `cap`.`cap_tab`
### SET
### @1=2
### @2='lisi'
# at 1955
#211129 15:56:19 server id 15133306 end_log_pos 1986 CRC32 0xee8ae974 Xid = 267
COMMIT/*!*/;
# at 1986
#211129 15:56:29 server id 15133306 end_log_pos 2065 CRC32 0xdc0da22e Anonymous_GTID last_committed=8 sequence_number=9 rbr_only=yes original_committed_timestamp=1638172589905510 immediate_commit_timestamp=1638172589905510 transaction_length=289
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1638172589905510 (2021-11-29 15:56:29.905510 CST)
# immediate_commit_timestamp=1638172589905510 (2021-11-29 15:56:29.905510 CST)
/*!80001 SET @@session.original_commit_timestamp=1638172589905510*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2065
#211129 15:56:29 server id 15133306 end_log_pos 2139 CRC32 0x488c9b24 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1638172589/*!*/;
BEGIN
/*!*/;
# at 2139
#211129 15:56:29 server id 15133306 end_log_pos 2197 CRC32 0x4d2cfbe7 Table_map: `cap`.`cap_tab` mapped to number 164
# at 2197
#211129 15:56:29 server id 15133306 end_log_pos 2244 CRC32 0xbbfbafe6 Write_rows: table id 164 flags: STMT_END_F
### INSERT INTO `cap`.`cap_tab`
### SET
### @1=3
### @2='wangwu'
# at 2244
#211129 15:56:29 server id 15133306 end_log_pos 2275 CRC32 0xed8f27bb Xid = 268
COMMIT/*!*/;
# at 2275
#211129 16:04:04 server id 15133306 end_log_pos 2322 CRC32 0x5304b043 Rotate to mysql-bin.000004 pos: 4
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*/;
mysqlbinlog恢复数据
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
--start-datetime="2019-03-01 00:00:00" \
--stop-datetime="2019-03-10 00:00:00" \
--start-position="5000" \
--stop-position="20000"
mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql
②命令行解析
SHOW BINLOG EVENTS
[IN 'log_name'] //要查询的binlog文件名
[FROM pos]
[LIMIT [offset,] row_count]
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------+
| log_bin | ON |
| log_bin_basename | /data/log/mysql-bin |
| log_bin_index | /data/log/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------+
6 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2275 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 15133306 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000003 | 125 | Previous_gtids | 15133306 | 156 | |
| mysql-bin.000003 | 156 | Anonymous_Gtid | 15133306 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 235 | Query | 15133306 | 451 | use `mysql`; ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*F861720E101148897B0F5239DB926E756B1C28B3' /* xid=55 */ |
| mysql-bin.000003 | 451 | Anonymous_Gtid | 15133306 | 528 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 528 | Query | 15133306 | 623 | use `mysql`; flush privileges |
| mysql-bin.000003 | 623 | Anonymous_Gtid | 15133306 | 700 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 700 | Query | 15133306 | 846 | use `mysql`; create table cap(id int ,name varchar(20)) engine innodb /* xid=165 */ |
| mysql-bin.000003 | 846 | Anonymous_Gtid | 15133306 | 923 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 923 | Query | 15133306 | 1018 | use `mysql`; flush privileges |
| mysql-bin.000003 | 1018 | Anonymous_Gtid | 15133306 | 1095 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1095 | Query | 15133306 | 1200 | create database CAP /* xid=258 */ |
| mysql-bin.000003 | 1200 | Anonymous_Gtid | 15133306 | 1277 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1277 | Query | 15133306 | 1408 | use `cap`; create table cap_tab(id int,name varchar(20)) /* xid=264 */ |
| mysql-bin.000003 | 1408 | Anonymous_Gtid | 15133306 | 1487 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1487 | Query | 15133306 | 1561 | BEGIN |
| mysql-bin.000003 | 1561 | Table_map | 15133306 | 1619 | table_id: 164 (cap.cap_tab) |
| mysql-bin.000003 | 1619 | Write_rows | 15133306 | 1668 | table_id: 164 flags: STMT_END_F |
| mysql-bin.000003 | 1668 | Xid | 15133306 | 1699 | COMMIT /* xid=266 */ |
| mysql-bin.000003 | 1699 | Anonymous_Gtid | 15133306 | 1778 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1778 | Query | 15133306 | 1852 | BEGIN |
| mysql-bin.000003 | 1852 | Table_map | 15133306 | 1910 | table_id: 164 (cap.cap_tab) |
| mysql-bin.000003 | 1910 | Write_rows | 15133306 | 1955 | table_id: 164 flags: STMT_END_F |
| mysql-bin.000003 | 1955 | Xid | 15133306 | 1986 | COMMIT /* xid=267 */ |
| mysql-bin.000003 | 1986 | Anonymous_Gtid | 15133306 | 2065 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 2065 | Query | 15133306 | 2139 | BEGIN |
| mysql-bin.000003 | 2139 | Table_map | 15133306 | 2197 | table_id: 164 (cap.cap_tab) |
| mysql-bin.000003 | 2197 | Write_rows | 15133306 | 2244 | table_id: 164 flags: STMT_END_F |
| mysql-bin.000003 | 2244 | Xid | 15133306 | 2275 | COMMIT /* xid=268 */ |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+
29 rows in set (0.00 sec)
mysql>
binlog相关参数
+------------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------------+----------------------+
| binlog_cache_size | 4194304 | 每个线程binlog cache大小,写满会写入binlog
| binlog_checksum | CRC32 | binlog校验码,关闭后只通过binlog event长度校验。
| binlog_direct_non_transactional_updates | OFF | OFF,非事务操作记录到cache,事务操作记录binlog,commit后在按照先后顺序写入binlog。
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER | binlog无法写入时abort。处于prepared阶段且提交binlog成功的事务会commit。处于prepared阶段但未提交binlog成功的事务会
rollback。
| binlog_format | ROW | binlog记录格式,默认row
| binlog_group_commit_sync_delay | 0 | binlog组提交延迟毫秒数,当sync_binlog=0 or sync_binlog=1,这个延迟设置是应用在每一个组提交上;当
sync_binlog=N,N>1,这个延迟设置是累积了N个group commit之后才开始计算延迟。
| binlog_group_commit_sync_no_delay_count | 0 | 设置累积了多少个group commit以后,就忽略binlog_group_commit_sync_delay的延迟设置,开始执行binlog sync。
| binlog_gtid_simple_recovery | ON | 是否开启gtid简单恢复,
| binlog_max_flush_queue_time | 0 | 5.7.9开始已废弃,在旧版本,这个参数控制在group commit以前继续从flush队列里读取事务的时间。
| binlog_order_commits | ON | 控制binlog的顺序是否与事务提交顺序一致。
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 | 将大事物拆分成若干个binlog event,以每个binlog event为单位处理事务,限制每个事务最大8K
| binlog_row_image | FULL | 在row格式的binlog模式下,该参数控制binlog记录的详细程度
| binlog_row_metadata | MINIMAL | 将表元数据记录到binlog,在主从同步可以通过binlog进行表结构转换,也能通过binlog获得完整元数据,但binlog大小也会变大
| binlog_row_value_options | | 对于row格式的binlog模式,是否记录binlog对应的SQL;开启后可用mysqlbinlog -vvv来查看SQL
| binlog_rows_query_log_events | OFF | 为1是打开此项,将SQL语句以注释的形式打印到binlog
| binlog_stmt_cache_size | 32768 | 在事务里有非事务语句时,会在binlog statement cache里暂存相关SQL,该参数决定cache大小,每个线程独立分配
| binlog_transaction_compression | OFF | binlog压缩,性能会下降1%左右
| binlog_transaction_compression_level_zstd | 3 | 压缩等级,默认是3,随着等级增加,消耗的资源也会增加
| binlog_transaction_dependency_history_size | 25000 | MySQL采用一个vector的变量存储已经提交的事务的HASH值,所有已经提交的事务的所修改的主键和非空的UniqueKey的值经过HASH后
与该vector中的值对比,由此来判断当前提交的事务是否与已经提交的事务更新了同一行,并以此确定依赖关系
| binlog_transaction_dependency_tracking | COMMIT_ORDER | COMMIT_ORDER,根据同时进入prepare和commit来判断可以并行复制
| innodb_api_enable_binlog | OFF |
| log_statements_unsafe_for_binlog | ON | 控制当出现error 1592,是否将错误记录到error log
| max_binlog_cache_size | 8589934592 | binlog buffer最大大小限制(字节)
| max_binlog_size | 1073741824 | 每个binlog大小,超过大小会重新写一个,但不绝对,如果遇到长事务需要该事物结束才能切换下一个binlog。
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 | binlog写入方式,默认1,即每个事务刷新一次binlog。
+------------------------------------------------+----------------------+
_binlog_cache_size | 8589934592 | binlog buffer最大大小限制(字节)
| max_binlog_size | 1073741824 | 每个binlog大小,超过大小会重新写一个,但不绝对,如果遇到长事务需要该事物结束才能切换下一个binlog。
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 | binlog写入方式,默认1,即每个事务刷新一次binlog。
+------------------------------------------------+----------------------+