mysql binlog update_mysql binlog 简单介绍与基于binlog数据恢复

mysql binlog 简单介绍与基于binlog数据恢复

通过备份文件恢复

binlog(本节重点)

binlog 二进制日志文件

show variables like 'log_bin';

二进制日志文件(binary log) 记录了对mysql 数据库执行更改的所有操作, 但是不包括 select 和 show 这类操作,

因为这类操作对数据本身没有修改. 然而 操作本身没有对数据进行修改也可能会记录二进制日志,

binlog文件位置可以通过查询得到

mysql> show variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| datadir | /var/lib/mysql/ |

+---------------+-----------------+

其产生的binlog 文江将在 该目录下存储

通过查询可得到当前mysql服务正在使用的 是哪个文件

mysql> show master status\G

*************************** 1. row ***************************

File: binlog.000055

Position: 1065

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

我们来看一下该路径下都有啥

# pwd

/var/lib/mysql

# ls

'#innodb_temp' binlog.000055 client-key.pem ib_logfile0 mysql.ibd server-cert.pem undo_002

auto.cnf binlog.index credit_txp ib_logfile1 nh_merchant server-key.pem vcc

binlog.000051 bootdo demo ibdata1 performance_schema springbootv2

binlog.000052 ca-key.pem ec_common ibtmp1 private_key.pem sys

binlog.000053 ca.pem foo internationalization public_key.pem test

binlog.000054 client-cert.pem ib_buffer_pool mysql renren@002dfast undo_001

#

binlog文件一般是在mysql重启时切换binlog文件, 也可以手动切换当前使用的binlog文件,例如我们当前使用的binlog文件是 "binlog.000055", 可以通过 flush logs 手动切换 binlog 文件

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: binlog.000056

Position: 155

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

可以看到mysql服务使用的binlog文件改为了 binlog.000056

看一下 binlog文件的配置参数

max_binlog_size

binlog_cache_size

sync_binlog

binlog-do-db

binlog-ignore-db

log-save-update

binlog_format

说明:

max_binlog_size 指定二进制文件最大值, 如果超过该值将会产生一个新的二进制文件,后缀名+1, 默认大小为1G

当使用innodb 存储引擎时, 所有未提交的二进制日志将会记录到一个缓存中, 等事务提价时将从缓存中数据写入二进制文件, 而该缓存的大小有 binlog_cache_size 指定, 默认大小为32KB, 此外, 该缓冲是 基于session 的 , 即一个会话就会分配 一个指定大小的缓存, 因此不能设置过大, 但是当一个事务的记录大于指定的缓存时, mysql会把缓存中的日志写入一个临时文件中, 因此该值还不能设置过小. 可以通过 show global status 查看binlog_cache_use(记录使用缓冲写入二进制文件次数) 、binlog_cache_disk_use(记录使用临时文件写入二进制文件测试) 的状态 来判断是 binlog_cache_size 大小设置是否合适,

mysql> show variables like 'binlog_cache_size'; 大小默认32k

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| binlog_cache_size | 32768 |

+-------------------+-------+

1 row in set (0.01 sec)

mysql> show global status like 'binlog_cache%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 0 | 未使用过临时文件写入二进制文件

| Binlog_cache_use | 3 |

+-----------------------+-------+

2 rows in set (0.00 sec)

sync_binlog, 使用缓冲的来写入二进制日志的时候, sync_binlog=N 即表示写到缓冲N次即同步磁盘

binlog-do-db 与 binlog-ignore-db 表示需要写入或者忽略那些库的日志写入二进制日志文件,默认空

log-save-update 是当前mysql服务作为slave 节点, 则当前节点是不会从master节点获取二进制日志文件在写入自己的二进制文件的, 如果要开启就是要在从节点再次写入的话需要配置 log-save-update. 另外,如果mysql主从配置如果为 master=>slave=>slave 的话,则该参数必须设置

binlog_format 二进制日志文件格式

Row

日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。

优点： 在 row 模式下，bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或 function ，以及 trigger 的调用和触发无法被正确复制的问题。

缺点： 在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条 update 语句：

UPDATE product SET owner_member_id = 'b' WHERE owner_member_id = 'a'

执行之后，日志中记录的不是这条 update 语句所对应的事件 (MySQL 以事件的形式来记录 bin-log 日志) ，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多个事件。自然，bin-log 日志的量就会很大。尤其是当执行 alter table 之类的语句的时候，产生的日志量是惊人的。因为 MySQL 对于 alter table 之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中。

Statement

每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。

优点： 在 statement 模式下，首先就是解决了 row 模式的缺点，不需要记录每一行数据的变化，减少了 bin-log 日志量，节省 I/O 以及存储资源，提高性能。因为他只需要记录在 master 上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点： 在 statement 模式下，由于他是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在 slave 端杯执行的时候能够得到和在 master 端执行时候相同的结果。另外就是，由于 MySQL 现在发展比较快，很多的新功能不断的加入，使 MySQL 的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在 statement 中，目前已经发现的就有不少情况会造成 MySQL 的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行来记录的变化，所以不会出现类似的问题。

Mixed

从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。

在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。

新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

以上是对binlog 基本参数的配置做一个简要说明

下面将通过开源binlog2sql 来尝试恢复数据

binlog2sql 是大众点评开源快速回滚的工具, 其原理如名字是通过mysql的binlog对数据进行恢复

下面走一个案例来测试恢复数据

mysql版本5.7

python 版本 2.7.16

安装

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

shell> pip install -r requirements.txt

注意: shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql binlog2sql路径下的requirements.txt文件需要调整 cat requirements.txt PyMySQL0.7.11 wheel0.29.0 mysql-replication==0.13 需要将PyMySQL由0.7.11调整为0.9.3 shell> pip install -r requirements.txt

测试

准备数据库 test, 表user, 确认当前使用的binlog文件是什么

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000002

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

当前使用的binlog 文件是 mysql-bin.000002

手动向user 中插入几条数据

INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '老赵');

INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '老高');

INSERT INTO `test`.`user`(`id`, `name`) VALUES (3, '你妹的');

此时数据库中的数据是


​

此时我们咔全删了 DELETE TABLE user;

使用binlog2sql 对案发时间的日志做筛查

删除数据

DELETE FROM `user`; # 案发时间大约在 2021-02-20 16:27:00 - 2021-02-20 16:29:00

使用binlog2sql 生成的回滚数据恢复数据

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -tuser --start-file='mysql-bin.000002' --start-datetime='2021-02-20 16:27:00' --stop-datetime='2021-02-20 16:29:00' -B > rollback.sql | cat

结果:

jack@JackdeMacBook-Pro binlog2sql % python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dtest -tuser --start-file='mysql-bin.000002' --start-datetime='2021-02-20 16:27:00' --stop-datetime='2021-02-20 16:29:00' -B > rollback.sql | cat

INSERT INTO `test`.`user`(`id`, `name`) VALUES (3, '你妹的'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '老高'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '老赵'); #start 4911 end 5175 time 2021-02-20 16:27:08

jack@JackdeMacBook-Pro binlog2sql %

jack@JackdeMacBook-Pro binlog2sql % cat rollback.sql

INSERT INTO `test`.`user`(`id`, `name`) VALUES (3, '你妹的'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '老高'); #start 4911 end 5175 time 2021-02-20 16:27:08

INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '老赵'); #start 4911 end 5175 time 2021-02-20 16:27:08

jack@JackdeMacBook-Pro binlog2sql %

经过排查然后执行恢复数据 👌

参考

MySQL技术内幕++InnoDB存储引擎

记录报错