【mysql 系列2】通过bin-log恢复数据

待我称王封你为后i 2022-01-21 23:05 188阅读 0赞

## 1 mysql数据恢复原理 ##

bin-log记录了所有的DDL和DML(除了数据查询语句)语句，当mysql发生灾难性错误时，可以通过bin-log做完整恢复，基于时间点的恢复，和基于位置的恢复。  
不过完全基于bin-log的日志恢复，通常是不现实的。 如果你一个数据库运作了10年，不可能把所有的bin-log日志全部都保存下来（bin-log的体积成长还是很快的，特别对于一些增删改比较频繁的业务）。即使你保存下来， 重头跑一遍这样的bin-log， 这个时间估计你也受不了。 所以一般的数据恢复都是恢复数据备份到某个时间点，然后重跑bin-log部分的新增量。 所以日常应用中别指望完全通过bin-log恢复数据， 还是要定时备份数据。

通常情况下有个误解就是bin-log在恢复数据的时候，并非是撤销对数据的操作，而是回到某个备份点以后，执行增量的bin-log文件，跳过你想撤销的数据操作来达到恢复数据的。 重申一下bin-log只记录DDL和DML的操作记录，并不保存数据的snapshot。

## 2. 数据恢复实例 ##

### 2.1 实例环境 ###

#查看mysql版本
    SHOW VARIABLES LIKE '%version%';
    
    Variable_name            Value                         
    -----------------------  ------------------------------
    innodb_version           5.7.26                        
    protocol_version         10                            
    slave_type_conversions                                 
    tls_version              TLSv1,TLSv1.1                 
    version                  5.7.26-log                    
    version_comment          MySQL Community Server (GPL)  
    version_compile_machine  x86_64                        
    version_compile_os       Linux

### 2.2 创建数据库（test\_db） ###

CREATE DATABASE test_db;

### 2.3 业务场景1 ###

**2.3.1 假如1:00，我们执行了一次数据备份**

/usr/bin/mysqldump -uroot -pXXXX -lF -B test_db > /root/test_db_bak.sql

由于我使用了-F选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作，查看一下

mysql> show master status;
    
    File              Position  Binlog_Do_DB  Binlog_Ignore_DB  Executed_Gtid_Set  
    ----------------  --------  ------------  ----------------  -------------------
    mysql-bin.000007       123

**2.3.2 假如2:00 对数据库做了一些业务操作**

# 创建业务表，插入数据
    CREATE TABLE IF NOT EXISTS `tb1` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(16) NOT NULL,
    `age` INT(10) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    
    INSERT INTO tb1 VALUES (1, 'aaa', 10),(2, 'bbb', 20);
    INSERT INTO tb1 VALUES (3, 'ccc', 30);

**2.3.3 假如3:00 在前面的数据基础上又做了一些变更操作**

UPDATE tb1 SET NAME='aaa-000' WHERE id=1;
    INSERT INTO tb1 VALUES (4, 'ddd', 40);

**2.3.4 假如4:00 做了一次错误操作删除了数据表（比如数据库被恶意删除）**

delete from tb1;

**2.3.5 假如5:00 我们才发现这个问题。此时又进行了一些其他业务操作**

INSERT INTO tb1 VALUES (5, 'eeee', 50);

### 2.4 业务场景2 ###

针对于上述场景中的数据如何恢复呢？ 首先我们先保留现场，然后分析原因。  
**2.4.1 保护现场**

执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，这样出问题的那个log文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；

mysql> flush logs;
    mysql> show master status;

**2.4.2 读取binlog日志，分析问题**

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             1          123  Server ver: 5.7.26-log, Binlog ver: 4                                                                                                                                                                          
    mysql-bin.000007     123  Previous_gtids          1          154                                                                                                                                                                                                                 
    mysql-bin.000007     154  Anonymous_Gtid          1          219  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                           
    mysql-bin.000007     219  Query                   1          489  use `test_db`; CREATE TABLE IF NOT EXISTS `tb1` (                                                                                                                                                              
                                                                      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,                                                                                                                                                                 
                                                                      `name` varchar(16) NOT NULL,                                                                                                                                                                                   
                                                                      `age` int(10) NOT NULL,                                                                                                                                                                                        
                                                                      PRIMARY KEY (`id`)                                                                                                                                                                                             
                                                                      ) ENGINE=InnoDB DEFAULT CHARSET=utf8                                                                                                                                                                           
    mysql-bin.000007     489  Anonymous_Gtid          1          554  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                           
    mysql-bin.000007     554  Query                   1          629  BEGIN                                                                                                                                                                                                          
    mysql-bin.000007     629  Rows_query              1          705  # insert into tb1 values (1, 'aaa', 10),(2, 'bbb', 20)                                                                                                                                                         
    mysql-bin.000007     705  Table_map               1          758  table_id: 154 (test_db.tb1)                                                                                                                                                                                    
    mysql-bin.000007     758  Write_rows              1          819  table_id: 154 flags: STMT_END_F                                                                                                                                                                                
    mysql-bin.000007     819  Xid                     1          850  COMMIT /* xid=3114 */                                                                                                                                                                                          
    mysql-bin.000007     850  Anonymous_Gtid          1          915  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                           
    mysql-bin.000007     915  Query                   1          990  BEGIN                                                                                                                                                                                                          
    mysql-bin.000007     990  Rows_query              1         1051  # INSERT INTO tb1 VALUES (3, 'ccc', 30)                                                                                                                                                                        
    mysql-bin.000007    1051  Table_map               1         1104  table_id: 154 (test_db.tb1)                                                                                                                                                                                    
    mysql-bin.000007    1104  Write_rows              1         1152  table_id: 154 flags: STMT_END_F                                                                                                                                                                                
    mysql-bin.000007    1152  Xid                     1         1183  COMMIT /* xid=3123 */                                                                                                                                                                                          
    mysql-bin.000007    1183  Anonymous_Gtid          1         1248  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                           
    mysql-bin.000007    1248  Query                   1         1323  BEGIN                                                                                                                                                                                                          
    mysql-bin.000007    1323  Rows_query              1         1387  # update tb1 set name='aaa-000' where id=1                                                                                                                                                                     
    mysql-bin.000007    1387  Table_map               1         1440  table_id: 154 (test_db.tb1)                                                                                                                                                                                    
    mysql-bin.000007    1440  Update_rows             1         1506  table_id: 154 flags: STMT_END_F                                                                                                                                                                                
    mysql-bin.000007    1506  Xid                     1         1537  COMMIT /* xid=3132 */                                                                                                                                                                                          
    mysql-bin.000007    1537  Anonymous_Gtid          1         1602  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                           
    mysql-bin.000007    1602  Query                   1         1677  BEGIN                                                                                                                                                                                                          
    mysql-bin.000007    1677  Rows_query              1         1738  # INSERT INTO tb1 VALUES (4, 'ddd', 40)                                                                                                                                                                        
    mysql-bin.000007    1738  Table_map               1         1791  table_id: 154 (test_db.tb1)                                                                                                                                                                                    
    mysql-bin.000007    1791  Write_rows              1         1839  table_id: 154 flags: STMT_END_F                                                                                                                                                                                
    mysql-bin.000007    1839  Xid                     1         1870  COMMIT /* xid=3141 */                                                                                                                                                                                          
    mysql-bin.000007    1870  Anonymous_Gtid          1         1935  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                           
    mysql-bin.000007    1935  Query                   1         2010  BEGIN                                                                                                                                                                                                          
    mysql-bin.000007    2010  Rows_query              1         2049  # delete from tb1                                                                                                                                                                                              
    mysql-bin.000007    2049  Table_map               1         2102  table_id: 154 (test_db.tb1)                                                                                                                                                                                    
    mysql-bin.000007    2102  Delete_rows             1         2193  table_id: 154 flags: STMT_END_F                                                                                                                                                                                
    mysql-bin.000007    2193  Xid                     1         2224  COMMIT /* xid=3172 */                                                                                                                                                                                          
    mysql-bin.000007    2224  Anonymous_Gtid          1         2289  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                           
    mysql-bin.000007    2289  Query                   1         2364  BEGIN                                                                                                                                                                                                          
    mysql-bin.000007    2364  Rows_query              1         2426  # INSERT INTO tb1 VALUES (5, 'eeee', 50)                                                                                                                                                                       
    mysql-bin.000007    2426  Table_map               1         2479  table_id: 154 (test_db.tb1)                                                                                                                                                                                    
    mysql-bin.000007    2479  Write_rows              1         2528  table_id: 154 flags: STMT_END_F                                                                                                                                                                                
    mysql-bin.000007    2528  Xid                     1         2559  COMMIT /* xid=3182 */

注： 或许你的log-bin显示的和我不太一样，看不到sql。 请注意event类型， QUERY\_EVENT是可以看到sql语句的，ROWS\_EVENT 不会显示sql语句。 mysql5.7 默认是使用ROWS\_EVENT方式的， 所以你需要在my.cnf修改参数  
binlog\_rows\_query\_log\_events=1  
然后重启mysql。即可

分析结果： pos【1870-2193】 执行了一条删除操作（delete from tb1）， 这是一条非法数据。 前面的业务和后面发生的业务都是正常业务。 所以我们只需要将 pos【1870- 2193 】这段恢复即可。

第一部分已经说明，log-bin 并没有恢复某条命令的过程。 所以我们的思路是：

a) 使用1:00的备份文件恢复到那个时间点；  
b) 重做mysql-bin.000007 到pos1870以前（pos1839）  
c) 跳过pos1870-pos2193，从pos2193以后（pos2224）继续执行到log文件结束  
d) 重做mysql-bin.000008 所有的日志

**a) 恢复1:00的最新备份数据，作为数据起点**

/usr/bin/mysql -uroot -pXXXX -v < /root/test_db_bak.sql

**b) 重做mysql-bin.000007 到pos1870以前（pos1839）**

/usr/bin/mysqlbinlog --stop-position=1870 --database=test_db /usr/mysql/data/mysql-bin.000007 | /usr/bin/mysql -uroot -pXXXXX -v test_db

恢复语法格式：  
\# mysqlbinlog mysql-bin.000007 | mysql -u用户名 -p密码 数据库名

常用选项：  
\--start-position            起始pos点  
\--stop-position             结束pos点  
\--start-datetime                         起始时间点  
\--stop-datetime                          结束时间点  
\--database                  指定只恢复指定的数据库(一台主机上往往有多个数据库，只限本地log日志)  
不常用选项：      
\--user=name                      Connect to the remote server as username.连接到远程主机的用户名  
\--password\[=name\]                Password to connect to remote server.连接到远程主机的密码  
\--host=name                      Get the binlog from server.从远程主机上获取binlog日志  
\--read-from-remote-server   Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

**c) 跳过pos1870-pos2193，从pos2193继续执行到log文件结束**

/usr/bin/mysqlbinlog --start-position=pos2224 --database=test_db /usr/mysql/data/mysql-bin.000007 | /usr/bin/mysql -uroot -pXXXXX -v test_db

**d) 重做mysql-bin.000008 所有的日志**

/usr/bin/mysqlbinlog --database=test_db /usr/mysql/data/mysql-bin.000008 | /usr/bin/mysql -uroot -pXXXXX -v test_db

到此结束