日志管理：

排错

1.1.1 错误日志

查看：

1
2
3
4
5
6
7
mysql> select @@log_error;
+----------------------+
| @@log_error          |
+----------------------+
| /data/3307/mysql.log |
+----------------------+
#默认日志路径 ：DATADIR/hostname.err

1.1.2配置方式

修改：

1
2
vim /data/3307/my.cnf
log_error=/data/3307/mysql.log //多实例下日志路径

数据恢复

binlog（二进制日志）

作用：数据恢复、主从复制

如何配置

  • log_bin //开关、设定存放位置
  • server_id（5.6不需要添加此参数，5.7以上必须加）
  • *生产要求：**日志和数据分开存放
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@db01 ~]# mkdir /data/binlog -p
[root@db01 ~]# vim /data/3307/my.cnf 
server_id=7                  
log_bin=/data/3307/mysql-bin
[root@db01 ~]# chmod -R mysql. /data
[root@db01 ~]# systemctl restart mysqld3307
系统下查看：
[root@db01 ~]# ls /data/3307/mysql-bin.* 
/data/3307/mysql-bin.000001  /data/3307/mysql-bin.000002   //自动向前滚动
/data/3307/mysql-bin.index //多个二进制日志的索引

mysql中查看：
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| log_bin                         | ON                         |
| log_bin_basename                | /data/3307/mysql-bin       | #控制bin日志前缀的参数
| log_bin_index                   | /data/3307/mysql-bin.index |
| log_bin_trust_function_creators | OFF                        |
| log_bin_use_v1_row_events       | OFF                        |
| sql_log_bin                     | ON                         |#临时关闭开关
+---------------------------------+----------------------------+

binlog记录了？

    1. 记录了数据库中所有变更类的操作
    • DDL
    • DCL
    • DML
    1. 对于DDL和DCL，记录发生过的语句。
    1. DML（IUD）
    • 前提：记录提交的事物IUD
      关于记录格式：
    • ROW：RBR 行记录模式，记录的是行的变化
    • STATEMENT：SBR 语句记录模式，记录操作语句本身 //对于函数方面的操作(如当前时间)，可能会造成数据不严谨
    • MIXED：MBR 混合记录模式
1
2
3
delete from city where id>1000;
RBR,逐行记录日志，日志量很大，但是足够严谨，不会出现记录错误。//5.7版本默认是RBR模式，是企业建议模式
SBR,只记录语句本身，日志量很少，可读性较强。对于函数类的操作，将来恢复会造成错误

查看：

1
2
3
4
5
6
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+

二进制日志事件（event）

简介

二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event

1
2
3
4
5
6
         position号码(字节偏移量)
begin;      120  - 340
DML1        340  - 460
DML2        460  - 550
commit;     550  - 760
作用：方便对日志进行截取，指定部分日志进行数据恢复
event的组成

三部分构成:

  • (1) 事件的开始标识
  • (2) 事件内容
  • (3) 事件的结束标识

Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号

二进制日志的查看

查看二进制日志所在位置

mysql> show variables like '%log_bin%';

1
2
3
4
[root@db01 ~]# ll /data/3307/mysql-bin.*
-rw-r----- 1 mysql mysql   1010 4月   1 17:09 /data/3307/mysql-bin.000001
-rw-r----- 1 mysql mysql 690963 4月   2 23:44 /data/3307/mysql-bin.000002
-rw-r----- 1 mysql mysql    196 4月  15 11:09 /data/3307/mysql-bin.index

1.2.5查看正在使用的二进制日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show binary logs;  //所有二进制日志
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1010 |
| mysql-bin.000002 |      6909 |
+------------------+-----------+
Log_name :目前MySQL存在的二进制日志名字
File_size ：目前MySQL用到了哪个position号

mysql> show master status; //确认正在使用的二进制日志
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     6909 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
查看二进制日志内容

横向查看：查看二进制日志事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show binlog events in 'mysql-bin.000007';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000007 |   4 | Format_desc    |         7 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000007 | 123 | Previous_gtids |         7 |         154 |                                       |                         
+------------------+------+-----------------+---------+-----------+---------------------------------------+
bin_log头部标记：
    5.6 前123个Position号
    5.7 为前154个Position号
注释：以上输出中每一行为一个事件
Log_name：日志名
Pos：事件开始的Position *****
Event_type：事件类型
Server_id：发生在哪台机器的事件 //主从时区分机器
End_log_pos：事件结束的位置号 *****
Info：事件内容

RBR模式下记录已提交的事务：

事务语句
纵向查看：查看二进制日志内容

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
89
90
91
92
93
94
95
96
97
98
99
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)
mysql> begin;
mysql> update city SET Name='Tilburo' where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from city where id=10;
+----+---------+-------------+---------------+------------+
| ID | Name    | CountryCode | District      | Population |
+----+---------+-------------+---------------+------------+
| 10 | Tilburo | NLD         | Noord-Brabant |     193238 |
+----+---------+-------------+---------------+------------+
mysql> commit;

[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000007 |grep -v "SET" >/tmp/aa.txt
[root@db01 ~]# mysqlbinlog /data/3307/mysql-bin.000007 |grep -v "SET"
DELIMITER /*!*/;
# at 4
#200415 11:09:01 server id 7  end_log_pos 123 CRC32 0x529742df  Start: binlog v 4, server v 5.7.26-log created 200415 11:09:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
zXqWXg8HAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADNepZeEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ad9Cl1I=
'/*!*/;
# at 123
#200415 11:09:01 server id 7  end_log_pos 154 CRC32 0xf70987e6  Previous-GTIDs
# [empty]

//从154字节开始看
# at 154
#200415 14:55:40 server id 7  end_log_pos 219 CRC32 0x5c02f208  Anonymous_GTID  last_committed=0        sequence_number=1   rbr_only=no

# at 219
#200415 14:55:40 server id 7  end_log_pos 316 CRC32 0x1e0dd89b  Query   thread_id=5     exec_time=0     error_code=0
/*!\C utf8 *//*!*/;
create database test1
/*!*/;
//创建了一个库，DDL语句直接记录语句本事。

# at 316
#200415 15:04:23 server id 7  end_log_pos 381 CRC32 0x7c43eb07  Anonymous_GTID  last_committed=1        sequence_number=2   rbr_only=yes

# at 381
#200415 15:03:46 server id 7  end_log_pos 454 CRC32 0x6f398c9d  Query   thread_id=5     exec_time=0     error_code=0
BEGIN
/*!*/;
# at 454
//事务开始

#200415 15:03:46 server id 7  end_log_pos 512 CRC32 0x223247ee  Table_map: `world`.`city` mapped to number 112
# at 512
#200415 15:03:46 server id 7  end_log_pos 618 CRC32 0xdac21634  Update_rows: table id 112 flags: STMT_END_F

BINLOG '
0rGWXhMHAAAAOgAAAAACAAAAAHAAAAAAAAEABXdvcmxkAARjaXR5AAUD/v7+Awb+I/4D/hQA7kcy
Ig==
0rGWXh8HAAAAagAAAGoCAAAAAHAAAAAAAAEAAgAF///gCgAAAAdUaWxidXJnA05MRA1Ob29yZC1C
cmFiYW501vICAOAKAAAAB1RpbGJ1cm8DTkxEDU5vb3JkLUJyYWJhbnTW8gIANBbC2g==
'/*!*/;
//RBR模式下，针对DML语句binlog中记录每行数据的变化过程

# at 618
#200415 15:04:23 server id 7  end_log_pos 649 CRC32 0xba219dfb  Xid = 42
COMMIT/*!*/;
DELIMITER ;
# End of log file
//事务结束


[root@db01 /data/binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002 //解码查看
# at 381
#200415 15:03:46 server id 7  end_log_pos 454 CRC32 0x6f398c9d  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1586934226/*!*/;
BEGIN
/*!*/;
# at 454
#200415 15:03:46 server id 7  end_log_pos 512 CRC32 0x223247ee  Table_map: `world`.`city` mapped to number 112
# at 512
#200415 15:03:46 server id 7  end_log_pos 618 CRC32 0xdac21634  Update_rows: table id 112 flags: STMT_END_F
### UPDATE `world`.`city`
### WHERE
###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Tilburg' /* STRING(35) meta=65059 nullable=0 is_null=0 */
###   @3='NLD' /* STRING(3) meta=65027 nullable=0 is_null=0 */
###   @4='Noord-Brabant' /* STRING(20) meta=65044 nullable=0 is_null=0 */
###   @5=193238 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Tilburo' /* STRING(35) meta=65059 nullable=0 is_null=0 */
###   @3='NLD' /* STRING(3) meta=65027 nullable=0 is_null=0 */
###   @4='Noord-Brabant' /* STRING(20) meta=65044 nullable=0 is_null=0 */
###   @5=193238 /* INT meta=0 nullable=0 is_null=0 */
# at 618
#200415 15:04:23 server id 7  end_log_pos 649 CRC32 0xba219dfb  Xid = 42
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

1.2.6 基于二进制日志数据恢复案例

按需截取日志
  • 基于position号的截取 *****
    • --start-position=###
    • --stop-position=###
      截取二进制日志核心在于，找起点和终点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
备份：
mysql> show binlog events in 'mysql-bin.000007';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000007 | 219 | Query          |         7 |         316 | create database test1                 |

[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=316 /data/3307/mysql-bin.000007 >/tmp/bin.sql

恢复：
mysql> drop database test1; //为了恢复而恢复
mysql> show databases;
mysql> set sql_log_bin=0;  //临时关闭当前会话二进制日志记录
mysql> source /tmp/bin.sql  //开始恢复
  • 基于时间点截取（了解）
  • 基于时间点的截取(了解)
    • --start-datetime
    • --stop-datetime
      for example: 2004-12-25 11:25:56
案例恢复详解

案例: 使用binlog日志进行数据恢复
模拟:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#1. 
create database binlog charset utf8mb4;
#2. 
use binlog;
create table t1(id int);
#3. 
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
#4. 
drop database binlog;

恢复：

1
2
3
4
5
6
7
#查看当前mysql使用的binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     3016 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
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
#根据binlog找到对应的字节偏移号
mysql> show binlog events in 'mysql-bin.000007';
+------------------+------+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+------+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000007 | 1800 | Query          |         7 |        1916 | create database binlog charset utf8mb4 | //开始1800
| mysql-bin.000007 | 1916 | Anonymous_Gtid |         7 |        1981 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000007 | 1981 | Query          |         7 |        2082 | use `binlog`; create table t1(id int)  |
| mysql-bin.000007 | 2082 | Anonymous_Gtid |         7 |        2147 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000007 | 2147 | Query          |         7 |        2221 | BEGIN                                  |
| mysql-bin.000007 | 2221 | Table_map      |         7 |        2268 | table_id: 117 (binlog.t1)              |
| mysql-bin.000007 | 2268 | Write_rows     |         7 |        2308 | table_id: 117 flags: STMT_END_F        |
| mysql-bin.000007 | 2308 | Xid            |         7 |        2339 | COMMIT /* xid=105 */                   |
| mysql-bin.000007 | 2339 | Anonymous_Gtid |         7 |        2404 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000007 | 2404 | Query          |         7 |        2478 | BEGIN                                  |
| mysql-bin.000007 | 2478 | Table_map      |         7 |        2525 | table_id: 117 (binlog.t1)              |
| mysql-bin.000007 | 2525 | Write_rows     |         7 |        2565 | table_id: 117 flags: STMT_END_F        |
| mysql-bin.000007 | 2565 | Xid            |         7 |        2596 | COMMIT /* xid=107 */                   |
| mysql-bin.000007 | 2596 | Anonymous_Gtid |         7 |        2661 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000007 | 2661 | Query          |         7 |        2735 | BEGIN                                  |
| mysql-bin.000007 | 2735 | Table_map      |         7 |        2782 | table_id: 117 (binlog.t1)              |
| mysql-bin.000007 | 2782 | Write_rows     |         7 |        2822 | table_id: 117 flags: STMT_END_F        |
| mysql-bin.000007 | 2822 | Xid            |         7 |        2853 | COMMIT /* xid=109 */                   | //结束2853
| mysql-bin.000007 | 2853 | Anonymous_Gtid |         7 |        2918 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000007 | 2918 | Query          |         7 |        3016 | drop database 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
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
[root@db01 ~]# mysqlbinlog --start-position=1800 --stop-position=2822 /data/3307/mysql-bin.000007 >/tmp/bin2.sql;  //截取需要的日志
[root@db01 ~]# cat /tmp/bin2.sql 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200415 11:09:01 server id 7  end_log_pos 123 CRC32 0x529742df  Start: binlog v 4, server v 5.7.26-log created 200415 11:09:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
zXqWXg8HAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADNepZeEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ad9Cl1I=
'/*!*/;
# at 1800
#200415 14:55:40 server id 7  end_log_pos 1916 CRC32 0x7d0a9649         Query   thread_id=5     exec_time=3793  error_code=0
SET TIMESTAMP=1586933740/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database binlog charset utf8mb4
/*!*/;
# at 1916
#200415 14:55:40 server id 7  end_log_pos 1981 CRC32 0x0335ab8d         Anonymous_GTID  last_committed=8        sequence_number=9       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1981
#200415 14:55:40 server id 7  end_log_pos 2082 CRC32 0xe4331fa1         Query   thread_id=5     exec_time=3793  error_code=0
use `binlog`/*!*/;
SET TIMESTAMP=1586933740/*!*/;
create table t1(id int)
/*!*/;
# at 2082
#200415 14:55:40 server id 7  end_log_pos 2147 CRC32 0xd9ff1631         Anonymous_GTID  last_committed=9        sequence_number=10      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2147
#200415 14:55:40 server id 7  end_log_pos 2221 CRC32 0x1b004ae2         Query   thread_id=5     exec_time=3793  error_code=0
SET TIMESTAMP=1586933740/*!*/;
BEGIN
/*!*/;
# at 2221
#200415 14:55:40 server id 7  end_log_pos 2268 CRC32 0x9d41f6ed         Table_map: `binlog`.`t1` mapped to number 117
# at 2268
#200415 14:55:40 server id 7  end_log_pos 2308 CRC32 0xcc11e52b         Write_rows: table id 117 flags: STMT_END_F

BINLOG '
7K+WXhMHAAAALwAAANwIAAAAAHUAAAAAAAEABmJpbmxvZwACdDEAAQMAAe32QZ0=
7K+WXh4HAAAAKAAAAAQJAAAAAHUAAAAAAAEAAgAB//4BAAAAK+URzA==
'/*!*/;
# at 2308
#200415 14:55:40 server id 7  end_log_pos 2339 CRC32 0x891d519e         Xid = 105
COMMIT/*!*/;
# at 2339
#200415 14:55:40 server id 7  end_log_pos 2404 CRC32 0xe5f12f2c         Anonymous_GTID  last_committed=10       sequence_number=11      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2404
#200415 14:55:40 server id 7  end_log_pos 2478 CRC32 0x2f6a2f99         Query   thread_id=5     exec_time=3793  error_code=0
SET TIMESTAMP=1586933740/*!*/;
BEGIN
/*!*/;
# at 2478
#200415 14:55:40 server id 7  end_log_pos 2525 CRC32 0xc226ccc1         Table_map: `binlog`.`t1` mapped to number 117
# at 2525
#200415 14:55:40 server id 7  end_log_pos 2565 CRC32 0x7caf0d51         Write_rows: table id 117 flags: STMT_END_F

BINLOG '
7K+WXhMHAAAALwAAAN0JAAAAAHUAAAAAAAEABmJpbmxvZwACdDEAAQMAAcHMJsI=
7K+WXh4HAAAAKAAAAAUKAAAAAHUAAAAAAAEAAgAB//4CAAAAUQ2vfA==
'/*!*/;
# at 2565
#200415 14:55:40 server id 7  end_log_pos 2596 CRC32 0x133f90ed         Xid = 107
COMMIT/*!*/;
# at 2596
#200415 14:55:40 server id 7  end_log_pos 2661 CRC32 0xc76646d7         Anonymous_GTID  last_committed=11       sequence_number=12      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2661
#200415 14:55:40 server id 7  end_log_pos 2735 CRC32 0x117fc984         Query   thread_id=5     exec_time=3793  error_code=0
SET TIMESTAMP=1586933740/*!*/;
BEGIN
/*!*/;
# at 2735
#200415 14:55:40 server id 7  end_log_pos 2782 CRC32 0x238f82b5         Table_map: `binlog`.`t1` mapped to number 117
# at 2782
#200415 14:55:40 server id 7  end_log_pos 2822 CRC32 0xa5de37c9         Write_rows: table id 117 flags: STMT_END_F

BINLOG '
7K+WXhMHAAAALwAAAN4KAAAAAHUAAAAAAAEABmJpbmxvZwACdDEAAQMAAbWCjyM=
7K+WXh4HAAAAKAAAAAYLAAAAAHUAAAAAAAEAAgAB//4DAAAAyTfepQ==
'/*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;
1
2
mysql> set sql_log_bin=0; #关闭当前回话的binlog记录
mysql> source /tmp/bin2.sql

验证数据是否恢复：

1
2
3
4
5
6
7
8
mysql> select id from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+