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;
