MySQL-数据恢复场景实验

情景1：误删数据

背景描述

  • 9点的时候，删除一个库或一张表数据
  • 后来发现数据丢了，需紧急恢复

数据恢复思路

存在全量备份和binlog的情景

  • 利用全量备份进行数据恢复

    • 物理备份

      • 库级别：使用xtrabackup进行库级别的恢复到一个新库即可
      • 表级别：拷贝idb文件（innodb还需要执行alter table t discard/import tablespace命令）

    • 逻辑备份

      • 库级别：导入整库备份的SQL文件

      • 表级别：恢复单个的sql文件

        sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `order`/!d;q' /backup/order.sql   
grep 'INSERT INTO `order`' /backup/dbname.sql > order_data.sql

  • 利用增量的binlog文件恢复数据

没有全备和binlog的情景

1、恢复delete的数据
可以考虑percona-data-recovery-tool-for-innodb工具
2、恢复drop 表的数据
可以考虑使用undrop-for-innodb工具
3、有从库的话，考虑使用主从切换
四、对于rm类删除
在没有备份的情况下，如果服务器没有重启，可以复制/proc/$pid/fd文件来进行恢复

准备测试数据

create database devdb;
use devdb;
create table customers(
id int not null auto_increment,
name char(20) not null,
age int not null,
primary key(id)
)engine=InnoDB;

insert into customers values(1,"biu1","24");
insert into customers values(2,"biu2","22");
insert into customers values(3,"biu3","27");
commit;

全量备份

mysqldump -uroot -p -B -F -R -x --master-data=2 devdb|gzip >/tmp/devdb_$(date +%F).sql.gz

参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

再次变更数据

insert into customers values(4,"biu4","21");
insert into customers values(5,"biu5","25");
insert into customers values(6,"biu6","29");
update customers set age=28 where id=4;
insert into customers values(7,"biu7","18");
delete from customers ;
commit;

drop database devdb;

恢复数据

当发现重要表数据删除时，立即锁表（可选）

flush tables with read lock; -- 给表加上读锁

在另一台机器或实例上恢复数据

解压备份文件（可选)
gzip -d /tmp/devdb_$(date +%F).sql.gz
确定上次全备的binlog文件名称及其位置
grep 'CHANGE' /tmp/devdb_$(date +%F).sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;

全备时刻的binlog文件位置：mysql-bin.000002的106行，因此在该文件之前的binlog文件中的数据都已经包含在全备中，该文件之后的数据是全备之后的增量数据

将全量备份文件及binlog文件拷贝到新机器
全量备份还原
mysql -uroot -proot < /tmp/devdb_$(date +%F).sql
还原增量数据
# 1. 将binlog日志转换成sql
mysqlbinlog --start-position=106 mysql-bin.000002 > incr002.sql
mysqlbinlog mysql-bin.000003 > incr003.sql
......

# 2. 定位到删除数据的行并将其注释


# 2.2 也可以通过时间和位置点恢复


# 3. 导入binlog中的增量数据
mysql -uroot -proot < incr002.sql

注意：

恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog

检查数据完整性
show databases;
select * from devdb.customers;