文章目录
- 一、什么是MySQL的 binlog?
- 二、binlog操作
- 1、查看默认情况
- 2、查看binlog
- 三、binlog格式
- 四、binlog的写入机制
- 五、binlog与redolog对比
- 1、binlog与redolog对比
- 2、拓展:两阶段提交
一、什么是MySQL的 binlog?
MySQL的 binlog 是记录所有数据库表结构变更(例如 CREATE
、ALTER TABLE
)以及表数据修改(INSERT
、UPDATE
、DELETE
)的二进制日志。binlog 不会记录 SELECT
和 SHOW
这类操作,因为这类操作对数据本身并没有修改,但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。
MySQL binlog 以事件形式记录,还包含语句所执行的消耗的时间,MySQL 的二进制日志是事务安全型的。
binlog 的主要目的是:
- 用于数据恢复
- 用于 数据复制
二、binlog操作
1、查看默认情况
查看记录二进制日志是否开启:在MySQL8中默认情况下,二进制文件是开启的。
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.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)
2、查看binlog
当MySQL创建二进制日志文件时,先创建一个以“filename”为名称、以“.index”为后缀的文件,再创建一个以“filename”为名称、以“.000001”为后缀的文件。
MySQL服务重新启动一次
,以“.000001”为后缀的文件就会增加一个,并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同;如果日志长度超过了max_binlog_size
的上限(默认是1GB
),就会创建一个新的日志文件。
查看当前的二进制日志文件列表及大小。指令如下:
mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| atguigu-bin.000001 | 156 | No |
+--------------------+-----------+-----------+
1 行于数据集 (0.02 秒)
(1) 下面命令将事务以 伪SQL的形式
表现出来
mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"
结果:
mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"
#220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10
exec_time=0 error_code=0
SET TIMESTAMP=1641345397/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio
n_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 324
#220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map:
`atguigu14`.`student` mapped to number 85
# at 391
#220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id
85 flags: STMT_END_F
BINLOG '
dfHUYRMBAAAAQwAAAIcBAAAAAFUAAAAAAAEACWF0Z3VpZ3UxNAAHc3R1ZGVudAADAw8PBDwAHgAG
AQEAAgEhkJj4dA==
dfHUYR8BAAAATwAAANYBAAAAAFUAAAAAAAEAAgAD//8AAQAAAAblvKDkuIkG5LiA54+tAAEAAAAL
5byg5LiJX2JhY2sG5LiA54+tkQSSyQ==
'/*!*/;
### UPDATE `atguigu`.`student`
### WHERE
### @1=1
### @2='张三'
### @3='一班'
### SET
### @1=1
### @2='张三_back'
### @3='一班'
# at 470
#220105 9:16:37 server id 1 end_log_pos 501 CRC32 0xca01d30f Xid = 15
COMMIT/*!*/;
(2)前面的命令同时显示binlog格式的语句,使用如下命令不显示它
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"
结果:
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"
#220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10
exec_time=0 error_code=0
SET TIMESTAMP=1641345397/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio
n_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 324
#220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map:
`atguigu14`.`student` mapped to number 85
# at 391
#220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id
85 flags: STMT_END_F
### UPDATE `atguigu14`.`student`
### WHERE
### @1=1
### @2='张三'
### @3='一班'
### SET
### @1=1
### @2='张三_back'
### @3='一班'
# at 470
#220105 9:16:37 server id 1 end_log_pos 501 CRC32 0xca01d30f Xid = 15
(3)上面的办法读取出binlog日志的全文内容比较多,不容易分辨查看到pos点信息,下面介绍一种更为方便的查询命令:
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
结果:
mysql> show binlog events in 'atguigu-bin.000002';
+--------------------+-----+----------------+-----------+-------------+---------------
--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+--------------------+-----+----------------+-----------+-------------+---------------
--------------------------------------------------------------+
| atguigu-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver:
8.0.26, Binlog ver: 4 |
| atguigu-bin.000002 | 125 | Previous_gtids | 1 | 156 |
|
| atguigu-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| atguigu-bin.000002 | 235 | Query | 1 | 324 | BEGIN
|
| atguigu-bin.000002 | 324 | Table_map | 1 | 391 | table_id: 85
(atguigu14.student) |
| atguigu-bin.000002 | 391 | Update_rows | 1 | 470 | table_id: 85
flags: STMT_END_F |
| atguigu-bin.000002 | 470 | Xid | 1 | 501 | COMMIT /*
xid=15 */ |
| atguigu-bin.000002 | 501 | Anonymous_Gtid | 1 | 578 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| atguigu-bin.000002 | 578 | Query | 1 | 721 | use
`atguigu14`; create table test(id int, title varchar(100)) /* xid=19 */ |
| atguigu-bin.000002 | 721 | Anonymous_Gtid | 1 | 800 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| atguigu-bin.000002 | 800 | Query | 1 | 880 | BEGIN
|
| atguigu-bin.000002 | 880 | Table_map | 1 | 943 | table_id: 89
(atguigu14.test) |
| atguigu-bin.000002 | 943 | Write_rows | 1 | 992 | table_id: 89
flags: STMT_END_F |
| atguigu-bin.000002 | 992 | Xid | 1 | 1023 | COMMIT /*
xid=21 */
+--------------------+-----+----------------+-----------+-------------+---------------
--------------------------------------------------------------+
14 行于数据集 (0.02 秒)
binlog 有三种格式,各有优缺点:
statement
: 每一条会修改数据的sql都会记录在binlog中优点
:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。缺点
:某些语句和函数如UUID
,LOAD DATA INFILE
等在复制过程可能导致数据不一致甚至出错。row
:5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息,仅保存哪条记录语句被修改。优点
:会非常清楚的记录下每一行数据修改的细节,很安全。缺点
:会比其他两种模式大很多,在一些大表中清除大量数据时在 binlog 中会生成很多条语句,可能导致从库延迟变大。mixed
: 混合模式,根据语句来选用是 statement 还是 row 模式。
MySQL的binlog默认采用row
这种格式:
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 行于数据集 (0.02 秒)
binlog的写入时机也非常简单,事务执行过程中,先把日志写到 binlog cache
,事务提交的时候,再把binlog cache写到binlog文件中。(类比redo log)
因为一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache
。
write和fsync的时机,可以由参数 sync_binlog
控制,默认是 0
sync_binlog=0
:为0的时候,表示每次提交事务都只write,由系统自行判断什么时候执行fsync。虽然性能得到提升,但是机器宕机,page cache里面的binglog 会丢失。
sync_binlog=1
:为了安全起见,可以设置为 1,表示每次提交事务都会执行fsync,就如同redo log
刷盘流程一样。- -
sync_binlog=N
:一种折中方式,可以设置为N(N>1),表示每次提交事务都write,但累积N个事务后才fsync。
1、binlog与redolog对比
- redo log是
物理日志
,记录内容是”在某个数据页上做了什么修改“,属于InnoDB存储引擎层产生的。而binlog是逻辑日志
,记录内容是语句的原始逻辑,类似于”给ID=2这一行的c字段加1“,属于MySQL Server层。 - 在执行更新语句过程,会记录redo log与binlog两块日志,以基本的事务为单位,redo log在事务执行过程中可以不断写入,而binlog只有在提交事务时才写入,所以redo log与binlog的
写入时机
不一样。
2、拓展:两阶段提交
redo log与binlog两份日志之间的逻辑不一致,会出现什么问题?
由于binlog没写完就异常,这时候binlog里面没有对应的修改记录。
为了解决两份日志之间的逻辑一致问题,InnoDB存储引擎使用两阶段提交方案。
使用两阶段提交后,写入binlog时发生异常也不会有影响
最后就查看binlog日志,是否写入成功,如果成功就redolog就commit阶段就提交正常流程下去,如果失败,就回滚
另一个场景,redo log设置commit阶段发生异常,那会不会回滚事务呢?
并不会回滚事务,它会执行上图框住的逻辑,虽然redo log是处于prepare阶段,但是能通过事务id找到对应的binlog日志,所以MySQL认为是完整的,就会提交事务恢复数据