MySQL恢复数据覆盖原有 mysql数据恢复方式

转载

fjfdh 2023-12-26 11:48:51

文章标签 MySQL恢复数据覆盖原有 mysql sql 数据 文章分类 MySQL 数据库

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对数据进行恢复

binlog2sql 在github地址: https://github.com/danfengcao/binlog2sql

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

mysql版本5.7

python 版本 2.7.16

安装

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
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, '你妹的');

此时数据库中的数据是

![image-20210220153155284](/Users/jack/Library/Application Support/typora-user-images/image-20210220153155284.png)

此时我们咔全删了 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 %