删库不跑路-详解MySQL数据恢复

假设从备份文件中恢复表 mytest.t_innodb，恢复前提是设置了 innodb_file_per_table = on

1.  起一个新实例
2.  在实例上建一个和原来一模一样的表
3.  执行 alter table t_innodb discard tablespace;，删除表空间，这个操作会把 t_innodb.ibd 删除
4.  从备份文件中找到 t_innodb.ibd 这个文件，复制到对应的数据目录，并授权
5.  执行 alter table t_innodb IMPORT tablespace; 加载表空间
6.  执行 flush table t_innodb;check table t_innodb; 检查表
7.  使用 mysqldump 导出数据，然后再导入到要恢复的数据库

注意：

1.  在新实例上恢复再dump出来是为了避免风险，如果是测试，可以直接在原库上操作步骤 2-6
2.  只在 8.0 以前的版本有效

4 跳过误操作SQL

跳过误操作 SQL 一般用于执行了无法闪回的操作比如 drop tabledatabase

4.1 使用备份文件恢复跳过

4.1.1 不开启 GTID

使用备份文件恢复的步骤和基于时间点恢复的操作差不多，区别在于多一个查找 binlog 操作

举个例子，我这里建立了两个表 a 和 b，每分钟插入一条数据，然后做全量备份，再删除表 b，现在要跳过这条 SQL。

删除表 b 后的数据库状态

chgnqm-3306>>show tables;  
+------------------+  
| Tables_in_mytest |  
+------------------+  
| a                |  
+------------------+  
1 row in set (0.00 sec) 

  1.找出备份时的日志位置

[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'  
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=38414; 

  2.找出执行了 drop table 语句的 pos 位置

[mysql@mysql-test mysql_test]$  mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'drop table `b`';  
# at 120629  
#190818 19:48:30 server id 83  end_log_pos 120747 CRC32 0x6dd6ab2a     Query    thread_id=29488    exec_time=0    error_code=0  
SET TIMESTAMP=1566128910/*!*/;  
DROP TABLE `b` /* generated by server */ 

  从结果中我们可以看到 drop 所在语句的开始位置是 120629，结束位置是 120747

  3.从 binglog 中提取跳过这条语句的其他记录

# 第一条的 start-position 为备份文件的 pos 位置，stop-position 为 drop 语句的开始位置  
mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql  
# 第二条的 start-position 为 drop 语句的结束位置  
mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql 

  4.恢复备份文件

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql 

  全量恢复后状态

chgnqm-3306>>show tables;  
+------------------+  
| Tables_in_mytest |  
+------------------+  
| a                |  
| b                |  
+------------------+  
2 rows in set (0.00 sec)  
chgnqm-3306>>select count(*) from a;  
+----------+  
| count(*) |  
+----------+  
|       71 |  
+----------+  
1 row in set (0.00 sec) 

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!