本文将分享，如何基于binlog恢复数据。

一、binLog恢复数据

根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损耗，不过这还是可以接受的，一般来说，binlog 有两个重要的使用场景：

MySQL主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。

MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

1.1 . 开启 binlog

(1)配置/etc/mysql/mysql.conf.d/mysqld.cnf ，编辑以下参数：

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

在其中，server-id 是服务器的唯一标识符，log_bin 是binlog文件的路径和名称。你可以根据需要更改这些值。

(2)保存更改后的配置文件，并重新启动MySQL服务，使更改生效。

(3)确认binlog已经成功开启，可以使用以下命令登录MySQL并执行：

SHOW MASTER STATUS;

如果输出类似如下信息，则表示binlog已经成功开启：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107      | test         |                  |
+------------------+----------+--------------+------------

或者以下命令：可以看到log_bin的状态是ON

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                            |
+---------------------------------+--------------------------------+
5 rows in set (0.00 sec)

1.2、编辑配置

#这个参数用来启用binlog，并指定了binlog的文件名前缀。在这个例子中，binlog文件会以 dx_logbin 开头命名。binlog文件记录了数据库的所有更改操作，包括增删改等。
log-bin=adx_logbin
#这个参数指定了单个binlog文件的最大大小，单位是字节。当binlog文件大小达到这个值时，MySQL会自动创建一个新的binlog文件来继续记录日志。
max_binlog_size=104857600
#这个参数指定了binlog文件的过期时间，单位是天。超过指定天数的binlog文件会被自动删除。这个设置有助于控制磁盘空间的使用。
expire_logs_days=7
#这个参数用来指定需要记录binlog的数据库名称。在这个例子中，adx_db 是需要记录binlog的数据库，
#binlog-do-db=adx_db
#这个参数用来指定不需要记录binlog的数据库名称。在这个例子中，javaboy_no_db 是不需要记录binlog的数据库，但是由于前面有#注释了，所以实际上是被注释掉了，不会生效。
#binlog-ignore-db=javaboy_no_db
#这个参数用来控制binlog的写入方式。当设置为0时，表示不强制立即将binlog日志写入磁盘。这样会提高性能，但在数据库宕机时可能会丢失一部分数据。
sync_binlog=0

#这个参数指定了MySQL服务器的唯一标识符。在复制和多主模式下，每个服务器都需要有一个唯一的ID来标识自己。 server-id=1

在看一下是否开启binlog

其中我们还要关注两个属性

log_bin_basename: /var/lib/mysql/adx_logbin
#这个配置指定了二进制日志文件的基本名字为adx_logbin，不包括文件扩展名。实际的二进制日志文件会以这个基本名字开头，后面紧跟一个数字标识，再加上文件扩展名（通常是.log）。例如，可能生成的二进制日志文件包括adx_logbin.000001、adx_logbin.000002等。
#这个设置意味着生成的二进制日志文件将以adx_logbin作为基本名字。
log_bin_index: /var/lib/mysql/adx_logbin.index

这个配置指定了二进制日志索引文件的名字为二进制日志索引文件记录了所有的二进制日志文件名字及其对应的位置信息，通常以.index作为文件扩展名。通过这个索引文件，MySQL可以快速地定位到各个二进制日志文件，并进行相应的操作，比如数据库恢复、复制等。

查看一下现在的 adx_logbin.index 文件：

1.3、常用binlog相关命令

1、查看所有 binlog 日志列表

show master logs;

2、查看 master 状态

用于查看当前主服务器的二进制日志（binlog）信息。执行这个命令可以获取以下信息，File：当前正在写入的二进制日志文件名，Position：在当前二进制日志文件中的位置，即已经写入的字节数。

show master status;

3、刷新 binlog

用于关闭当前的二进制日志文件，将当前的日志文件重命名为一个旧的日志文件，例如通过添加一个序号或时间戳。并创建一个新的空的二进制日志文件，用于接收后续的二进制日志事件。

flush logs

这个命令在进行数据库备份时特别有用，因为它可以确保备份操作可以在一个一致的时间点开始，并且不会受到正在写入的二进制日志的影响。另外，当你希望重新开始二进制日志的记录时，也可以使用这个命令来关闭当前的日志文件并开启一个新的日志文件。

需要注意的是，执行 FLUSH LOGS; 会导致当前的二进制日志文件被关闭，这可能会影响到主从复制的正常运行。因此，在执行这个命令之前，需要谨慎考虑是否会对数据库的其他操作产生影响。

5、查看 binlog

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

该命令用于显示指定二进制日志文件中的二进制日志事件。这个命令可以提供对二进制日志中存储的操作和更改的详细信息。

下面是各参数的含义：

IN 'log_name**'**: 可选参数，指定要查看的特定二进制日志文件名。

FROM****pos: 可选参数，指定从日志文件中的特定位置开始显示日志事件。

LIMIT [offset,] row_count: 可选参数，限制要显示的日志事件的数量，并可以设置偏移量。

show binlog events in 'adx_logbin.000001';

通过这个工具就能查看你执行过什么sql，例如327行我执行了创建库操作，586行我执行了创建表操作。

1.4 基于binLog恢复数据

通常数据库会做定时备份，假设每天凌晨1:00点定时备份全量数据库，如果第二天数据丢失了，可以先通过备份，先将数据恢复到当天的凌晨1:00的数据，再基于binlog恢复凌晨一点到数据丢失的那一刻的数据，这样就完全找回数据了。

备份命令：

mysqldump -uroot -p --flush-logs --lock-tables -B student2>/root/student2.bak.sql

mysqldump: 这是用于备份数据库的命令。
-uroot: 表示使用 root 用户身份连接到数据库进行备份。
-p: 是一个选项，表示在输入密码之前会提示用户输入密码。
--flush-logs: 这个选项表示在备份完成后将刷新日志文件，确保备份过程中的日志都被记录下来。
--lock-tables: 这个选项表示在备份时对数据库表进行锁定，以确保备份的一致性。
-B student2: 表示备份名为 student2 的数据库。
> /root/student2.sql: 这部分表示将备份内容导出到 /root 目录下的 student2.sql 文件中。

可以通过cat命令查看导出的sql，老铁没毛病。

假设我现在删除了student2这张表。

如何恢复？先查询最新的binlog（最后一个binlog）。

show binlog events in 'adx_logbin.000002';

可以看到，在 1403-1507 这个 Pos 中发生了删库跑路事件，那么我们只需要回放该文件将数据恢复到 1403 这个位置即可。

由于 adx_logbin.000002 文件是在当前凌晨1:00备份之后产生的新文件，因此这个文件从起始到 1403 这个 Pos 之间的操作，就是凌晨1:00到删库之前的操作了。

那么我们来看下通过 binlog 来恢复数据的命令：（没有这个student2库的话先手动建立一个）

mysqlbinlog /var/lib/mysql/adx_logbin.000002 --stop-position=1403 --database=student2 | mysql -uroot -p

mysqlbinlog: 是用于解析 MySQL 二进制日志文件的工具。通过这个命令，你可以查看和分析二进制日志中的内容。
/var/lib/mysql/adx_logbin.000002: 这是指定的二进制日志文件路径，即要解析的二进制日志文件。
--stop-position=1403: 这个选项指定了解析二进制日志文件时要停止的位置。在这种情况下，命令会解析从文件开头到指定位置（1403）之间的内容。
--database=student2: 这个选项指定了只解析属于数据库 'student2' 的相关操作。这意味着命令只会处理涉及 'student2' 数据库的内容。
|: 这是管道符号，用于将 mysqlbinlog 命令的输出传递给后面的 mysql 命令。
mysql -uroot -p: 这是执行实际恢复操作的部分。它使用 mysql 命令以 root 用户身份连接到 MySQL 数据库，并执行从 mysqlbinlog 命令得到的结果。
mysqlbinlog: 是用于解析 MySQL 二进制日志文件的工具。通过这个命令，你可以查看和分析二进制日志中的内容。
/var/lib/mysql/adx_logbin.000002: 这是指定的二进制日志文件路径，即要解析的二进制日志文件。
--stop-position=1403: 这个选项指定了解析二进制日志文件时要停止的位置。在这种情况下，命令会解析从文件开头到指定位置（1403）之间的内容。
--database=student2: 这个选项指定了只解析属于数据库 'student2' 的相关操作。这意味着命令只会处理涉及 'student2' 数据库的内容。
|: 这是管道符号，用于将 mysqlbinlog 命令的输出传递给后面的 mysql 命令。

mysql -uroot -p: 这是执行实际恢复操作的部分。它使用 mysql 命令以 root 用户身份连接到 MySQL 数据库，并执行从 mysqlbinlog 命令得到的结果。

数据恢复啦～

二、主从复制的原理

2.1、主从复制的原理：

主库在每次事务提交的时候会将执行的更新操作记录在binlog日志中，接着，从库的IO线程会通过连接主库的log dump线程，读取主库的binlog日志并将读取到的数据写入到从库的relay log中继日志中，最后,从库的SQL线程会读取并重新执行中继日志中的事件。

2.2、binlog什么时候刷盘？

事务执行过程中，日志信息会先被写入到binlog cache,事务提交的时候，binlog cache中的数据会被写入到操作系统内核的page cache中(write())。
sync_binlog参数的值决定了什么时候page cache中的binlog刷新到磁盘的binlog文件中。
sync_binlog值为0（默认值）时，刷盘操作fsync()由操作系统自行决定
sync_binlog值为1时,binlog在写入到page cache后马上被刷新到磁盘
sync_binlog值N(N>1)时,表示page cache中的binlog在积累了N个事务的操作后才刷新到磁盘

2.3、面试题：更新语句执行过程？

以具体执行一条更新操作：UPDATE t_user SET name = 'adx' WHERE id = 1;为例，分析mysql更新操作的执行流程：

update tb_student A set A.age='19' where A.name=' 张三 ';
执行器负责具体的执行，会调用存储引擎接口，通过主键索引树获取id=1这一行记录:
如果id = 1这一行所在的数据页在缓冲池中，直接返回给执行器更新；
如果id = 1这一行所在的数据页不在缓冲池中，就将数据页从磁盘读入缓冲池，再返回记录给执行器；
执行器得到记录后，会看一下更新后的记录与更新前的记录是否一样：
如果一样则不进行后序更新流程；
如果不一样，就将更新前后的记录都当做参数传给InnoDB层，让它执行真正的更新操作
开启事务，InnoDB层更新记录前，先将更新前的列的旧值记录在undo log中，生成的undo log会写入缓冲池的Undo页面，同时，因为修改了Undo页面，也会记录对应的redo log
InnoDB层开始更新记录，会先更新缓冲池中的数据页（标记其为脏页），接着记录修改的信息到redo log中为了减少磁盘IO，不会立即将脏页写入磁盘，而是由一个后台线程在合适的时机将脏页刷新到磁盘。（WAL技术）。Mysql的写操作不是立刻写入磁盘，而是先写到redo log,然后在合适的时机再将修改的行数据写到磁盘。
至此，一条记录更新完了
更新完，记录对应的bin log到bin log cache中
事务提交，将binlog cache中的数据写入到操作系统内核的page cache中，由操作系统决定何时刷盘。（sync_binlog值为默认值0时）执行器负责具体的执行，会调用存储引擎接口，通过主键索引树获取id=1这一行记录:
如果id = 1这一行所在的数据页在缓冲池中，直接返回给执行器更新；
如果id = 1这一行所在的数据页不在缓冲池中，就将数据页从磁盘读入缓冲池，再返回记录给执行器；
执行器得到记录后，会看一下更新后的记录与更新前的记录是否一样：
如果一样则不进行后序更新流程；
如果不一样，就将更新前后的记录都当做参数传给InnoDB层，让它执行真正的更新操作
开启事务，InnoDB层更新记录前，先将更新前的列的旧值记录在undo log中，生成的undo log会写入缓冲池的Undo页面，同时，因为修改了Undo页面，也会记录对应的redo log
InnoDB层开始更新记录，会先更新缓冲池中的数据页（标记其为脏页），接着记录修改的信息到redo log中。为了减少磁盘IO，不会立即将脏页写入磁盘，而是由一个后台线程在合适的时机将脏页刷新到磁盘。（WAL技术）。Mysql的写操作不是立刻写入磁盘，而是先写到redo log,然后在合适的时机再将修改的行数据写到磁盘。
至此，一条记录更新完了
更新完，记录对应的bin log到bin log cache中
事务提交，将binlog cache中的数据写入到操作系统内核的page cache中，由操作系统决定何时刷盘。（sync_binlog值为默认值0时）

2.4、两阶段提交

redo log刷盘与binlog刷盘是两个独立的逻辑，两个操作如果不能同时成功，会导致主从数据库不一致。

如果在redo log刷盘后，mysql突然宕机，binlog还没来得及写入，这样，mysql重启后，主库通过redolog可以得到最新的数据库数据，但是从库通过binlog复制的数据库与主库则会出现不一致。

如果在binlog刷盘后，mysql突然宕机，redo log还没来得及写入，这样，mysql重启后，主库通过redolog无法恢复事务的执行操作，得到的是旧数据。但是从库通过binlog复制的数据库却是最新的，与主库出现不一致。

当客户端执行commit或者自动提交事务时，Mysql会开启一个内部事务XA，并且会分两阶段完成对XA事务的提交：

具体就是将redo log的操作拆成了两个阶段：prepare阶段和commit阶段,中间穿插对bin log的写入。

在prepare阶段：会将XA事务的id(XID)写入redo log,并将redo log的事务状态设置为prepare，然后将redo log持久化到磁盘（innodb_flush_log_at_trx_commit = 1 ）。

在commit阶段：把XID写入bin log,并将binlog持久化到磁盘（sync_binlog = 1），接着调用引擎的提交事务接口将redo log事务状态设置为commit。（bin log写入成功为事务提交成功的标志）

我们假设mysql分别在下面的A、B时刻发生异常重启：

mysql重启后扫描redo log，发现处于prepare状态的redo log，就拿着redo log中的XID去binlog中查找是否有这个XID：

1）发现binlog中没有XID（A时刻），说明redo log完成刷盘，但是binlog还未完成刷盘，则回滚事务。

2）发现binlog中有XID（B时刻），说明redo log和binlog都已经刷盘，则提交事务。

因此，在mysql异常重启后，binlog已经刷盘，说明redo log也已经刷盘，这样一来，重启后主库提交事务，从库如果用binlog复制主库数据，可以保证主从数据库数据一致。