Mysql-binlog 介绍

介绍
mysql-binlog是MySQL数据库的二进制日志，以事件形式记录了所有的 DDL 和 DML 语句（除了数据查询语句），还记录语句所执行的消耗时间。MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog有多种格式

1、 Statement：每一条会修改数据的sql都会记录在binlog中
2、 Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改
3、 Mixed:是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog

binlog 查看配置命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 8.0.13 |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------------+
| Variable_name                   | Value                                             |
+---------------------------------+---------------------------------------------------+
| log_bin                         | ON                                                |
| log_bin_basename                | D:\tools\mysql-8.0.13-winx64\data\mysql-bin       |
| log_bin_index                   | D:\tools\mysql-8.0.13-winx64\data\mysql-bin.index |
| log_bin_trust_function_creators | OFF                                               |
| log_bin_use_v1_row_events       | OFF                                               |
| sql_log_bin                     | ON                                                |
+---------------------------------+---------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

binlog配置

1
2
3
4
#文件名设置
log-bin=mysql-bin
#日志格式
binlog_format=ROW

binlog 文件操作命令

1
2
3
4
5
6
#会多一个最新的bin-log日志
flush logs
#查看最后一个bin-log日志的相关信息
show master status
#清空所有的bin-log日志
reset master

sql 操作演示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
mysql> create database binlog;
Query OK, 1 row affected (0.06 sec)

mysql> use binlog;
Database changed
mysql> create table test(id int auto_increment not null primary key, val int,data varchar(20));
Query OK, 0 rows affected (0.15 sec)
#分一个新的日志文件  000002
mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test(val, data) values (1, 'zhang');
Query OK, 1 row affected (0.10 sec)

mysql> insert into test(val, data) values (2, 'wang');
Query OK, 1 row affected (0.04 sec)
#分一个新的日志文件  000003
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test(val, data) values (3, 'zhao');
Query OK, 1 row affected (0.11 sec)
#分一个新的日志文件  000003
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql>  delete from test where id=3
mysql> drop table test;
Query OK, 0 rows affected (0.09 sec)

mysql> drop database binlog;
Query OK, 0 rows affected (0.09 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       643 |
| mysql-bin.000002 |       789 |
| mysql-bin.000003 |       495 |
| mysql-bin.000004 |       841 |
+------------------+-----------+
4 rows in set (0.00 sec)
#恢复mysql-bin.000001 创建的数据（不能包含删除数据库sql，不然恢复不成功，这里单独把删除sql存放在了第二个binlog文件）
D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog  ..\data\mysql-bin.000001 | mysql -u root -p
Enter password: ******
#binlog解析成txt文件
D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog  ..\data\mysql-bin.000003 > d:/log.txt

日志文件查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      4883 |
+------------------+-----------+
1 row in set (0.01 sec)

mysql> show binlog events;
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| mysql-bin.000001 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.13, Binlog ver: 4    |
| mysql-bin.000001 |  124 | Previous_gtids |         1 |         155 |                                      |
| mysql-bin.000001 |  155 | Anonymous_Gtid |         1 |         230 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 |  230 | Query          |         1 |         309 | BEGIN                                |
| mysql-bin.000001 |  309 | Table_map      |         1 |         368 | table_id: 66 (binlog.test)           |
| mysql-bin.000001 |  368 | Write_rows     |         1 |         418 | table_id: 66 flags: STMT_END_F       |
| mysql-bin.000001 |  418 | Xid            |         1 |         449 | COMMIT /* xid=13 */                  |
| mysql-bin.000001 |  449 | Anonymous_Gtid |         1 |         524 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 |  524 | Query          |         1 |         603 | BEGIN                                |
| mysql-bin.000001 |  603 | Table_map      |         1 |         662 | table_id: 66 (binlog.test)           |
| mysql-bin.000001 |  662 | Write_rows     |         1 |         711 | table_id: 66 flags: STMT_END_F       |
| mysql-bin.000001 |  711 | Xid            |         1 |         742 | COMMIT /* xid=98 */                  |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
...略
82 rows in set (0.01 sec)
# 查看指定binlog文件
mysql> show binlog events in 'mysql-bin.000001';
#获取指定内容
mysql> show binlog events from 4800;
+------------------+------+------------+-----------+-------------+--------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                           |
+------------------+------+------------+-----------+-------------+--------------------------------+
| mysql-bin.000001 | 4800 | Write_rows |         1 |        4852 | table_id: 66 flags: STMT_END_F |
| mysql-bin.000001 | 4852 | Xid        |         1 |        4883 | COMMIT /* xid=302 */           |
+------------------+------+------------+-----------+-------------+--------------------------------+
2 rows in set (0.00 sec)

日志文件内容查看

当有数据变更会在本地数据库数据存放目录下面生成以下：

1
2
3
4
5
6
#索引文件
mysql-bin.index
#binlog文件
mysql-bin.000001
#将binlog文件解析成 txt
D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog  ..\data\mysql-bin.000001 > d:/log1.txt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190731 17:41:00 server id 1  end_log_pos 124 CRC32 0xe904f22c 	Start: binlog v 4, server v 8.0.13 created 190731 17:41:00
BINLOG '
LGJBXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjEzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgEs8gTp
'/*!*/;
# at 124
#190731 17:41:00 server id 1  end_log_pos 155 CRC32 0xd12224e5 	Previous-GTIDs
# [empty]
# at 155
#190731 17:41:15 server id 1  end_log_pos 230 CRC32 0x25b4eff3 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes	original_committed_timestamp=1564566075183241	immediate_commit_timestamp=1564566075183241	transaction_length=294
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1564566075183241 (2019-07-31 17:41:15.183241 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1564566075183241 (2019-07-31 17:41:15.183241 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1564566075183241*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 230
#190731 17:41:15 server id 1  end_log_pos 309 CRC32 0xb95da169 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1564566075/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
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 utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
BEGIN
/*!*/;
# at 309
#190731 17:41:15 server id 1  end_log_pos 368 CRC32 0x7203817f 	Table_map: `binlog`.`test` mapped to number 115
# at 368
#190731 17:41:15 server id 1  end_log_pos 418 CRC32 0x8be3841b 	Write_rows: table id 115 flags: STMT_END_F

BINLOG '
O2JBXRMBAAAAOwAAAHABAAAAAHMAAAAAAAEABmJpbmxvZwAEdGVzdAADAwMPAjwABgEBAAIBIX+B
A3I=
O2JBXR4BAAAAMgAAAKIBAAAAAHMAAAAAAAEAAgAD/wABAAAAAQAAAAV6aGFuZxuE44s=
'/*!*/;
# at 418
#190731 17:41:15 server id 1  end_log_pos 449 CRC32 0xfc026d64 	Xid = 177
COMMIT/*!*/;
# at 449
#190731 17:41:22 server id 1  end_log_pos 524 CRC32 0x113e4745 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes	original_committed_timestamp=1564566082696808	immediate_commit_timestamp=1564566082696808	transaction_length=293
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1564566082696808 (2019-07-31 17:41:22.696808 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1564566082696808 (2019-07-31 17:41:22.696808 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1564566082696808*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 524
#190731 17:41:22 server id 1  end_log_pos 603 CRC32 0xe309f990 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1564566082/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
BEGIN
/*!*/;
# at 603
#190731 17:41:22 server id 1  end_log_pos 662 CRC32 0xc07d94f6 	Table_map: `binlog`.`test` mapped to number 115
# at 662
#190731 17:41:22 server id 1  end_log_pos 711 CRC32 0x42d695e3 	Write_rows: table id 115 flags: STMT_END_F

BINLOG '
QmJBXRMBAAAAOwAAAJYCAAAAAHMAAAAAAAEABmJpbmxvZwAEdGVzdAADAwMPAjwABgEBAAIBIfaU
fcA=
QmJBXR4BAAAAMQAAAMcCAAAAAHMAAAAAAAEAAgAD/wACAAAAAgAAAAR3YW5n45XWQg==
'/*!*/;
# at 711
#190731 17:41:22 server id 1  end_log_pos 742 CRC32 0x8bde4f70 	Xid = 178
COMMIT/*!*/;
# at 742
#190731 17:41:38 server id 1  end_log_pos 789 CRC32 0xcf82cc85 	Rotate to mysql-bin.000003  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*/;

#恢复第一条数据
D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog  ..\data\mysql-bin.000002 --stop-position=603 | mysql -uroot -p
Enter password: ******
#恢复第二条数据
D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog  ..\data\mysql-bin.000002 --start-position=603 --stop-position=742 | mysql -uroot -p
Enter password: ******

恢复数据mysql-bin.000003

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
BEGIN
/*!*/;
# at 309
#190731 17:41:44 server id 1  end_log_pos 368 CRC32 0x6286ca84 	Table_map: `binlog`.`test` mapped to number 115
# at 368
#190731 17:41:44 server id 1  end_log_pos 417 CRC32 0x5004c380 	Write_rows: table id 115 flags: STMT_END_F

BINLOG '
WGJBXRMBAAAAOwAAAHABAAAAAHMAAAAAAAEABmJpbmxvZwAEdGVzdAADAwMPAjwABgEBAAIBIYTK
hmI=
WGJBXR4BAAAAMQAAAKEBAAAAAHMAAAAAAAEAAgAD/wADAAAAAwAAAAR6aGFvgMMEUA==
'/*!*/;
# at 417
#190731 17:41:44 server id 1  end_log_pos 448 CRC32 0x8433520a 	Xid = 180
COMMIT/*!*/;
#恢复第三条数据
D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog  ..\data\mysql-bin.000003 --start-position=309 --stop-position=417 | mysql -uroot -p
Enter password: ******

文件转码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
D:\tools\mysql-8.0.13-winx64\bin>mysqlbinlog  --base64-output=DECODE-ROWS -v  ..\data\mysql-bin.000005 > d:/log6.txt

BEGIN
/*!*/;
# at 309
#190731 19:29:49 server id 1  end_log_pos 368 CRC32 0x0bc503bf 	Table_map: `binlog`.`test` mapped to number 62
# at 368
#190731 19:29:49 server id 1  end_log_pos 416 CRC32 0x5a878e6d 	Write_rows: table id 62 flags: STMT_END_F
### INSERT INTO `binlog`.`test`
### SET
###   @1=4
###   @2=4
###   @3='tan'
# at 416
#190731 19:29:49 server id 1  end_log_pos 447 CRC32 0x5064407d 	Xid = 39
COMMIT/*!*/;

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
是否启用了日志
mysql>show variables like 'log_bin';

怎样知道当前的日志
mysql> show master status;

查看mysql binlog模式
show variables like 'binlog_format';

获取binlog文件列表
show binary logs;

查看当前正在写入的binlog文件
show master status\G

查看指定binlog文件的内容
show binlog events in 'mysql-bin.000002';