mysql log_bin整理-526互联

二进制日志记录开关，二进制日志用于记录mysql数据更新的日志文件，对于非变更数据的操作不予记录，比如select、show等。

show variables like 'log_bin';

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

说明开关是没有开启的。

show variables like '%log_bin%';

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_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)

开启二进制日志

修改my.cnf配置文件

vim /etc/my.cnf

这个仅仅是针对linux系统的，my.cnf文件是mysql的配置文件，不同系统，my.cnf的位置不尽相同。如果不知道当前系统读取mysql配置的地方，修改一个错误的配置，会使配置不生效。如何查看当前正在使用的mysql配置？可以使用以下命令查找：

[root@cdh1 ~]# which mysqld
/usr/sbin/mysqld
[root@cdh1 ~]# /usr/sbin/mysqld --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

通过上面两个命令，我们了解到，my.cnf是按照上面的顺序读取的，如果前一个没有就读下一个配置。

一般Debian下，/etc/mysql/my.cnf才是MySQL读取的配置文件，而不是/etc/my.cnf。

my.cnf即mysql配置文件通常分成多个部分，每个部分的开头是一个用方括号括起来的分段名称。MySQL程序通常读取跟它同名的分段部分，许多客户端程序还会读取client部分，这是一个存放公用设置的地方。服务器通常读取mysqld这一段。一定要确认配置项放在了文件正确的分段中，否则配置是不会生效的。

运行vim /etc/my.cnf后，可看到my.cnf的内容：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
skip-name-resolve
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 64M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

在[mysqld]后追加以下内容：

server-id=1
log-bin=mysql-bin
binlog-format=ROW

server-id Mysql的Id属性的唯一值，同步时用于区分主从、主主实例等。
log-bin 打开二进制日志功能。主从复制里，只有主节点才会打开二进制日志功能。
binlog-format 二进制日志的模式与配置

binlog-format 一共有三种模式：

基于SQL语句的复制（Statement-Based Replication，SBR）。
基于行的复制（Row-Based Replication，RBR）。
混合模式复制（Mixed-Based Replication，MBR）。

mysql默认使用statement level模式，对于主从复制来说，需要使用行的复制。

添加完后，重启mysql

[root@cdh1 ~]# service mysqld restart
Redirecting to /bin/systemctl restart  mysqld.service

然后登陆数据库查看：

[root@cdh1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/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.02 sec)

binlog是开启了。

查看二进制文件的名称和大小

使用命令show binary logs;查看

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql>

查看binlog内容,执行命令system mysqlbinlog <log文件路径>：

mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000001;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230910 15:31:13 server id 1  end_log_pos 123 CRC32 0x197399f9 	Start: binlog v 4, server v 5.7.32-log created 230910 15:31:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
wXD9ZA8BAAAAdwAAAHsAAAABAAQANS43LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADBcP1kEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfmZcxk=
'/*!*/;
# at 123
#230910 15:31:13 server id 1  end_log_pos 154 CRC32 0xfe36874d 	Previous-GTIDs
# [empty]
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*/;
mysql>

如果发现上述命令观察不容易理解，可以使用show binlog events in ‘mysql-bin.000001’;来观察。

mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

mysql>

指定查看某行之后的内容：

mysql> show binlog events in 'mysql-bin.000001' from 123;
+------------------+-----+----------------+-----------+-------------+------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------+
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |      |
+------------------+-----+----------------+-----------+-------------+------+
1 row in set (0.00 sec)

mysql>

指定分页参数：

mysql> show binlog events in 'mysql-bin.000001' from 4 limit 2;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

mysql>

limit表示指定数量。通过from和limit达到的分页的目的。

将二进制文件转换为文本文件：

使用mysqlbinlog命令将binlog二进制文件转换为文本文件：

[root@cdh1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 > /log.txt
root@cdh1 /]# cat /log.txt 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230910 15:31:13 server id 1  end_log_pos 123 CRC32 0x197399f9 	Start: binlog v 4, server v 5.7.32-log created 230910 15:31:13 at startup
ROLLBACK/*!*/;
BINLOG '
wXD9ZA8BAAAAdwAAAHsAAAAAAAQANS43LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADBcP1kEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfmZcxk=
'/*!*/;
# at 123
#230910 15:31:13 server id 1  end_log_pos 154 CRC32 0xfe36874d 	Previous-GTIDs
# [empty]
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*/;
[root@cdh1 /]#

忽略注释内容，可以看到一些数据库操作的记录。这便是binlog的查看方式。

通过binlog文件恢复mysql数据

在test库中新增一张student表：

mysql> CREATE TABLE IF NOT EXISTS `student` ( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
Query OK, 0 rows affected (0.02 sec)

向表中插入三条测试数据：

mysql> INSERT INTO student (name,sex,age) VALUES ('张三',1,18);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student (name,sex,age) VALUES ('李四',2,19);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student (name,sex,age) VALUES ('王五',1,20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (name,sex,age) values ('葫芦瓢',1,35);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-----------+------+------+
| id | name      | sex  | age  |
+----+-----------+------+------+
|  1 | 张三      |    1 |   18 |
|  2 | 李四      |    2 |   19 |
|  3 | 王五      |    1 |   20 |
|  4 | 葫芦瓢    |    1 |   35 |
+----+-----------+------+------+
4 rows in set (0.00 sec)

现在要对王五进行删除：

mysql> delete from student where name = '王五';
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-----------+------+------+
| id | name      | sex  | age  |
+----+-----------+------+------+
|  1 | 张三      |    1 |   18 |
|  2 | 李四      |    2 |   19 |
|  4 | 葫芦瓢    |    1 |   35 |
+----+-----------+------+------+
3 rows in set (0.00 sec)

查看下binlog日志

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 |      1974 |
| mysql-bin.000005 |       154 |
| mysql-bin.000006 |       154 |
| mysql-bin.000007 |       177 |
| mysql-bin.000008 |       413 |
| mysql-bin.000009 |       177 |
| mysql-bin.000010 |      1913 |
+------------------+-----------+
7 rows in set (0.00 sec)

通过show binary logs;命令查看当前最新的二进制文件，发现最新的二进制文件是mysql-bin.000010

查看binlog文件内容

这里有两种方式：

mysql命令
mysql工具

对于mysql命令如下：

mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000010;

使用mysql工具,将binlog转换为普通的文本文件，然后阅读文件里的内容

[root@cdh1 /]# mysqlbinlog /var/lib/mysql/mysql-bin.000010 > /log1.txt
[root@cdh1 /]# cat /log1.txt

通过阅读文件内容如下：

mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000010;

use `mytest`/*!*/;
SET TIMESTAMP=1699672231/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table if not exists `student` (`id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8
/*!*/;
# at 495
#231111 11:11:24 server id 1  end_log_pos 560 CRC32 0x388f7839 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 560
#231111 11:11:24 server id 1  end_log_pos 634 CRC32 0xd78113a4 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1699672284/*!*/;
BEGIN
/*!*/;
# at 634
#231111 11:11:24 server id 1  end_log_pos 691 CRC32 0x65990a83 	Table_map: `mytest`.`student` mapped to number 109
# at 691
#231111 11:11:24 server id 1  end_log_pos 747 CRC32 0xd3b5afcd 	Write_rows: table id 109 flags: STMT_END_F

BINLOG '
3PBOZRMBAAAAOQAAALMCAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQyDCpll
3PBOZR4BAAAAOAAAAOsCAAAAAG0AAAAAAAEAAgAE//ABAAAABgDlvKDkuIkBAAAAEgAAAM2vtdM=
'/*!*/;
# at 747
#231111 11:11:24 server id 1  end_log_pos 778 CRC32 0x3bf1fa1a 	Xid = 18
COMMIT/*!*/;
# at 778
#231111 15:11:45 server id 1  end_log_pos 843 CRC32 0x626bcf13 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 843
#231111 15:11:45 server id 1  end_log_pos 917 CRC32 0xbac0b136 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699686705/*!*/;
BEGIN
/*!*/;
# at 917
#231111 15:11:45 server id 1  end_log_pos 974 CRC32 0xad33ae7a 	Table_map: `mytest`.`student` mapped to number 109
# at 974
#231111 15:11:45 server id 1  end_log_pos 1030 CRC32 0x50ab65d1 	Write_rows: table id 109 flags: STMT_END_F

BINLOG '
MSlPZRMBAAAAOQAAAM4DAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQx6rjOt
MSlPZR4BAAAAOAAAAAYEAAAAAG0AAAAAAAEAAgAE//ACAAAABgDmnY7lm5sCAAAAEwAAANFlq1A=
'/*!*/;
# at 1030
#231111 15:11:45 server id 1  end_log_pos 1061 CRC32 0xd305aad1 	Xid = 56
COMMIT/*!*/;
# at 1061
#231111 16:46:37 server id 1  end_log_pos 1126 CRC32 0x81fc9d92 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1126
#231111 16:46:37 server id 1  end_log_pos 1200 CRC32 0x261d0f7a 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699692397/*!*/;
BEGIN
/*!*/;
# at 1200
#231111 16:46:37 server id 1  end_log_pos 1257 CRC32 0x5e0a933c 	Table_map: `mytest`.`student` mapped to number 109
# at 1257
#231111 16:46:37 server id 1  end_log_pos 1313 CRC32 0x8fc14b9c 	Write_rows: table id 109 flags: STMT_END_F

BINLOG '
bT9PZRMBAAAAOQAAAOkEAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQw8kwpe
bT9PZR4BAAAAOAAAACEFAAAAAG0AAAAAAAEAAgAE//ADAAAABgDnjovkupQBAAAAFAAAAJxLwY8=
'/*!*/;
# at 1313
#231111 16:46:37 server id 1  end_log_pos 1344 CRC32 0x00dfa9c8 	Xid = 57
COMMIT/*!*/;
# at 1344
#231111 16:49:03 server id 1  end_log_pos 1409 CRC32 0xc5e77bba 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1409
#231111 16:49:03 server id 1  end_log_pos 1483 CRC32 0xf73e44f8 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699692543/*!*/;
BEGIN
/*!*/;
# at 1483
#231111 16:49:03 server id 1  end_log_pos 1540 CRC32 0xff33508a 	Table_map: `mytest`.`student` mapped to number 109
# at 1540
#231111 16:49:03 server id 1  end_log_pos 1599 CRC32 0xf0d2e310 	Write_rows: table id 109 flags: STMT_END_F

BINLOG '
/z9PZRMBAAAAOQAAAAQGAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQyKUDP/
/z9PZR4BAAAAOwAAAD8GAAAAAG0AAAAAAAEAAgAE//AEAAAACQDokavoiqbnk6IBAAAAIwAAABDj
0vA=
'/*!*/;
# at 1599
#231111 16:49:03 server id 1  end_log_pos 1630 CRC32 0x4f43662b 	Xid = 59
COMMIT/*!*/;
# at 1630
#231111 16:50:06 server id 1  end_log_pos 1695 CRC32 0x6c8aebf5 	Anonymous_GTID	last_committed=5	sequence_number=6	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1695
#231111 16:50:06 server id 1  end_log_pos 1769 CRC32 0xae6f20e5 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699692606/*!*/;
BEGIN
/*!*/;
# at 1769
#231111 16:50:06 server id 1  end_log_pos 1826 CRC32 0xf4a30819 	Table_map: `mytest`.`student` mapped to number 109
# at 1826
#231111 16:50:06 server id 1  end_log_pos 1882 CRC32 0xbede0a74 	Delete_rows: table id 109 flags: STMT_END_F

BINLOG '
PkBPZRMBAAAAOQAAACIHAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQwZCKP0
PkBPZSABAAAAOAAAAFoHAAAAAG0AAAAAAAEAAgAE//ADAAAABgDnjovkupQBAAAAFAAAAHQK3r4=
'/*!*/;
# at 1882
#231111 16:50:06 server id 1  end_log_pos 1913 CRC32 0xc2bd5e7e 	Xid = 61
COMMIT/*!*/;

Write_rows代表写入的操作，Delete_rows代表删除操作，日志里还有其他操作，分别是：

Start
Previous-GTIDs
Anonymous_GTID
Query
Table_map
Xid

这些都是binlog里的事件类型，也就是event_type,具体的解释可见官方文档。

从日志内容中我们能看到创建表的记录：

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/*!*/;
create table if not exists `student` (`id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8
/*!*/;
# at 495
#231111 11:11:24 server id 1  end_log_pos 560 CRC32 0x388f7839 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

注释中的end_log_pos代表每次操作的所在行，如果想恢复到该操作之前的状态，需要找到指定操作的end_log_pos行就要按照指定pos行数来恢复。

上述的操作中，我们插入了4条数据，可以看到有4条Write_rows操作

#231111 11:11:24 server id 1  end_log_pos 747 CRC32 0xd3b5afcd 	Write_rows: table id 109 flags: STMT_END_F

#231111 15:11:45 server id 1  end_log_pos 1030 CRC32 0x50ab65d1 	Write_rows: table id 109 flags: STMT_END_F

#231111 16:46:37 server id 1  end_log_pos 1313 CRC32 0x8fc14b9c 	Write_rows: table id 109 flags: STMT_END_F

#231111 16:49:03 server id 1  end_log_pos 1599 CRC32 0xf0d2e310 	Write_rows: table id 109 flags: STMT_END_F

对于需要提交事务的操作都有会有BEGIN和COMMIT的操作记录，所以我们在指定binlog命令运行范围时对于事务类的操作需要把BEGIN和COMMIT包含在范围内。

然后下一个操作开始前都是以at开头的，可以看下binlog里面的内容，看看at是不是对应上一次操作的end_log_pos对应的值。所以以at确定范围也具有参考意义。

我们一共插入了四条数据，其中第三条是插入王五数据的操作，由于删除的是王五的数据，如果想恢复王五的数据，就需要把插入王五的那条数据操作命令再运行一遍即可，但要注意不能丢了事务的操作：

BEGIN
/*!*/;
# at 1200
#231111 16:46:37 server id 1  end_log_pos 1257 CRC32 0x5e0a933c 	Table_map: `mytest`.`student` mapped to number 109
# at 1257
#231111 16:46:37 server id 1  end_log_pos 1313 CRC32 0x8fc14b9c 	Write_rows: table id 109 flags: STMT_END_F

BINLOG '
bT9PZRMBAAAAOQAAAOkEAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQw8kwpe
bT9PZR4BAAAAOAAAACEFAAAAAG0AAAAAAAEAAgAE//ADAAAABgDnjovkupQBAAAAFAAAAJxLwY8=
'/*!*/;
# at 1313
#231111 16:46:37 server id 1  end_log_pos 1344 CRC32 0x00dfa9c8 	Xid = 57
COMMIT/*!*/;
# at 1344

我们看到BEGIN和COMMIT限制的范围是1200和1344，所以只需要指定这块命令重新运行下即可：

mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000010 --start-position=1200 --stop-position=1344 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

--start-position --stop-position用于限制binlog的范围

我们可以看到王五的这条数据已经恢复了，

mysql> select * from student;
+----+-----------+------+------+
| id | name      | sex  | age  |
+----+-----------+------+------+
|  1 | 张三      |    1 |   18 |
|  2 | 李四      |    2 |   19 |
|  3 | 王五      |    1 |   20 |
|  4 | 葫芦瓢    |    1 |   35 |
+----+-----------+------+------+
4 rows in set (0.00 sec)

这种操作很鸡肋，如果数据多，你都不知道该恢复哪条数据，所以要对binlog数据进行转义操作，便于查看。

mysql> system mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000010;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#231111 10:32:06 server id 1  end_log_pos 123 CRC32 0xcda570d3 	Start: binlog v 4, server v 5.7.32-log created 231111 10:32:06 at startup
ROLLBACK/*!*/;
# at 123
#231111 10:32:06 server id 1  end_log_pos 154 CRC32 0xe3265f58 	Previous-GTIDs
# [empty]
# at 154
#231111 11:10:31 server id 1  end_log_pos 219 CRC32 0x0abf6480 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#231111 11:10:31 server id 1  end_log_pos 495 CRC32 0xc8e5a2a3 	Query	thread_id=2	exec_time=0	error_code=0
use `mytest`/*!*/;
SET TIMESTAMP=1699672231/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table if not exists `student` (`id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8
/*!*/;
# at 495
#231111 11:11:24 server id 1  end_log_pos 560 CRC32 0x388f7839 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 560
#231111 11:11:24 server id 1  end_log_pos 634 CRC32 0xd78113a4 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1699672284/*!*/;
BEGIN
/*!*/;
# at 634
#231111 11:11:24 server id 1  end_log_pos 691 CRC32 0x65990a83 	Table_map: `mytest`.`student` mapped to number 109
# at 691
#231111 11:11:24 server id 1  end_log_pos 747 CRC32 0xd3b5afcd 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
###   @1=1
###   @2='张三'
###   @3=1
###   @4=18
# at 747
#231111 11:11:24 server id 1  end_log_pos 778 CRC32 0x3bf1fa1a 	Xid = 18
COMMIT/*!*/;
# at 778
#231111 15:11:45 server id 1  end_log_pos 843 CRC32 0x626bcf13 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 843
#231111 15:11:45 server id 1  end_log_pos 917 CRC32 0xbac0b136 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699686705/*!*/;
BEGIN
/*!*/;
# at 917
#231111 15:11:45 server id 1  end_log_pos 974 CRC32 0xad33ae7a 	Table_map: `mytest`.`student` mapped to number 109
# at 974
#231111 15:11:45 server id 1  end_log_pos 1030 CRC32 0x50ab65d1 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
###   @1=2
###   @2='李四'
###   @3=2
###   @4=19
# at 1030
#231111 15:11:45 server id 1  end_log_pos 1061 CRC32 0xd305aad1 	Xid = 56
COMMIT/*!*/;
# at 1061
#231111 16:46:37 server id 1  end_log_pos 1126 CRC32 0x81fc9d92 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1126
#231111 16:46:37 server id 1  end_log_pos 1200 CRC32 0x261d0f7a 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699692397/*!*/;
BEGIN
/*!*/;
# at 1200
#231111 16:46:37 server id 1  end_log_pos 1257 CRC32 0x5e0a933c 	Table_map: `mytest`.`student` mapped to number 109
# at 1257
#231111 16:46:37 server id 1  end_log_pos 1313 CRC32 0x8fc14b9c 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
###   @1=3
###   @2='王五'
###   @3=1
###   @4=20
# at 1313
#231111 16:46:37 server id 1  end_log_pos 1344 CRC32 0x00dfa9c8 	Xid = 57
COMMIT/*!*/;
# at 1344
#231111 16:49:03 server id 1  end_log_pos 1409 CRC32 0xc5e77bba 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1409
#231111 16:49:03 server id 1  end_log_pos 1483 CRC32 0xf73e44f8 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699692543/*!*/;
BEGIN
/*!*/;
# at 1483
#231111 16:49:03 server id 1  end_log_pos 1540 CRC32 0xff33508a 	Table_map: `mytest`.`student` mapped to number 109
# at 1540
#231111 16:49:03 server id 1  end_log_pos 1599 CRC32 0xf0d2e310 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
###   @1=4
###   @2='葫芦瓢'
###   @3=1
###   @4=35
# at 1599
#231111 16:49:03 server id 1  end_log_pos 1630 CRC32 0x4f43662b 	Xid = 59
COMMIT/*!*/;
# at 1630
#231111 16:50:06 server id 1  end_log_pos 1695 CRC32 0x6c8aebf5 	Anonymous_GTID	last_committed=5	sequence_number=6	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1695
#231111 16:50:06 server id 1  end_log_pos 1769 CRC32 0xae6f20e5 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1699692606/*!*/;
BEGIN
/*!*/;
# at 1769
#231111 16:50:06 server id 1  end_log_pos 1826 CRC32 0xf4a30819 	Table_map: `mytest`.`student` mapped to number 109
# at 1826
#231111 16:50:06 server id 1  end_log_pos 1882 CRC32 0xbede0a74 	Delete_rows: table id 109 flags: STMT_END_F
### DELETE FROM `mytest`.`student`
### WHERE
###   @1=3
###   @2='王五'
###   @3=1
###   @4=20
# at 1882
#231111 16:50:06 server id 1  end_log_pos 1913 CRC32 0xc2bd5e7e 	Xid = 61
COMMIT/*!*/;
# at 1913
#231111 11:10:31 server id 1  end_log_pos 1978 CRC32 0x1ea427d5 	Anonymous_GTID	last_committed=6	sequence_number=7	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

从上可以看出，解码后可以看到每条执行语句的明文记录。

#231111 16:46:37 server id 1  end_log_pos 1313 CRC32 0x8fc14b9c 	Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
###   @1=3
###   @2='王五'
###   @3=1
###   @4=20

这样就可以知道根据具体业务参数搜索对指定数据进行恢复了，其中命令参数：

日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog查看
mysqlbinlog -d logfilename 指定数据库名称，只列出指定的数据库相关操作
mysqlbinlog -o 忽略掉日志中的前n行命令
mysqlbinlog -v 将行事件（数据变更）重构为sql语句
mysqlbinlog -vv 将行事件（数据变更）重构为sql语句，并输出注释信息

数据恢复的核心是找到关键操作行然后根据业务是选择指定行恢复，或者关键操作时间点之前的所有操作进行恢复。

删除二进制文件

删除某个日志文件之前的所有的二进制日志文件

首先先查看当前所有的二进制文件：

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
| mysql-bin.000002 |      1329 |
| mysql-bin.000003 |       154 |
| mysql-bin.000004 |      1974 |
| mysql-bin.000005 |       154 |
| mysql-bin.000006 |       154 |
| mysql-bin.000007 |       154 |
+------------------+-----------+
7 rows in set (0.00 sec)

比如，我想删除mysql-bin.000004之前的二进制日志文件，那么就是要删除mysql-bin.000001、mysql-bin.000002、mysql-bin.000003三个日志文件。

mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 |      1974 |
| mysql-bin.000005 |       154 |
| mysql-bin.000006 |       154 |
| mysql-bin.000007 |       154 |
+------------------+-----------+
4 rows in set (0.00 sec)

删除某个时间点之前的所有二进制日志文件

这个时候就要查看二进制日志文件是什么时候创建的，以及其他时间。

进入到二进制文件所在路径下，然后使用stat命令进行查看：

[root@cdh1 ~]# cd /var/lib/mysql
[root@cdh1 mysql]# ls
auto.cnf         dolphinscheduler  ibtmp1            mysql-bin.000007    private_key.pem  sys
ca-key.pem       ib_buffer_pool    mysql             mysql-bin.index     public_key.pem   test
ca.pem           ibdata1           mysql-bin.000004  mysql.sock          server-cert.pem
client-cert.pem  ib_logfile0       mysql-bin.000005  mysql.sock.lock     server-key.pem
client-key.pem   ib_logfile1       mysql-bin.000006  performance_schema  store
[root@cdh1 mysql]# stat mysql-bin.000005
  File: ‘mysql-bin.000005’
  Size: 154       	Blocks: 8          IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 2188570     Links: 1
Access: (0640/-rw-r-----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2023-11-01 20:03:56.870012935 +0800
Modify: 2023-11-01 20:03:56.858012935 +0800
Change: 2023-11-01 20:03:56.858012935 +0800
 Birth: -
[root@cdh1 mysql]#

stat命令的Birth字段表示文件的创建时间。

但是从输出可知，Birth为空。如果stat命令查看xfs文件系统的文件时，如果要Birth字段不显示空置，必须满足几个条件，一个是xfs的版本为v5，另外，对操作系统内核版本也有要求（如下所示）

stat “now prints file creation time when supported by the file system, on GNU Linux systems with glibc >= 2.28 and kernel >= 4.11.”

查看当前系统的内核版本：

[root@cdh1 mysql]# uname -r
3.10.0-327.4.5.el7.x86_64
[root@cdh1 mysql]#

3.10是小于4.11所以stat命令Birth字段显示为空值。

log_bin mysql log bin

log redo undo bin

log_bin_trust_function_creators

log_bin_trust_function_creators function creators

主从log_bin_trust_function_creators function

mysql-bin

errcode mysql-bin mariadb start

mysql二进制permission mysql-bin

log三大binlog mysql