A3-01-01: 通过binlog日志恢复误删除的表

实验环境信息：

 

[root@localhost ~]# lsb_release -a|grep -v LSB
Distributor ID:    OracleServer
Description:    Oracle Linux Server release 7.2
Release:    7.2
Codename:    n/a
[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.22, for linux-glibc2.12 (x86_64) using  EditLine wrapper

 

 

 

3.1 MySQL通过binlog日志恢复数据--误删除的表

 

##step1. 时间点1-数据库a：创建数据库,创建表并插入数据
show master status;
CREATE DATABASE backuptest;
USE backuptest;
CREATE TABLE `student` (
    `studentid` INT (11) NOT NULL,
    `sname` CHAR (30) NOT NULL,
    `gender` enum ('male', 'female') DEFAULT NULL,
    `birth` date DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE = INNODB ;
INSERT INTO student
VALUES
    (1,'liu1','male',curdate()),
    (2,'chen2','female',date_add(curdate(), INTERVAL - 2 YEAR)),
    (3,'zhang3','male',date_add(curdate(), INTERVAL - 5 YEAR)),
    (4,'li4','female',date_add(curdate(), INTERVAL - 3 YEAR)),
    (5,'wang5','female',date_add(curdate(), INTERVAL - 4 YEAR));
commit;
select * from backuptest.student;
show master status;

 

 

 

 

  

##step 2. 时间点2-数据库a：备份时间点1之后的数据（数据备份）
mysqldump -uroot -p -F -R --all-databases > /data/mysql/mysql3306/backup/alldb_bak.sql

 

 

 

##step 3. 时间点3-数据库a：进行变更数据操作并提交
show master status;
use backuptest;
update backuptest.student set sname='li4888' where studentid=4;
commit;
select * from backuptest.student;
show master status;
update backuptest.student set sname='wang5888' where studentid=5;
commit;
select * from backuptest.student;
show master status;

 

 

 

##step 4. 时间点4-数据库a：误删除数据并提交
show master status;
drop table backuptest.student;
select * from backuptest.student;
show master status;

 

 

 

##step 5. 时间点5-数据库a: 分析数据库a的二进制日志文件并备份（二进制备份），同时将数据备份和二进制备份拷贝至数据库b
show binlog events in 'mysql-bin.000004';
mysqlbinlog --stop-position=802 --database=backuptest /data/mysql/mysql3306/logs/mysql-bin.000004 > /data/mysql/mysql3306/backup/binlog_bak.sql


scp /data/mysql/mysql3306/backup/alldb_bak.sql 172.16.26.33:/data/mysql/mysql3306/backup/
scp /data/mysql/mysql3306/backup/binlog_bak.sql 172.16.26.33:/data/mysql/mysql3306/backup/

 

 

  

##step 6. 时间点6-数据库b: 恢复数据库a的数据备份并验证(没有时间点3的变更操作的记录)；
mysql -uroot -p -e "create database backuptest;"
mysql -uroot -p -e "reset master;"
mysql -uroot -p -o backuptest < /data/mysql/mysql3306/backup/alldb_bak.sql 
mysql -uroot -p -e "select * from backuptest.student;"

 

 

 

##step 7. 时间点7-数据库b: 恢复数据库a的二进制备份并验证（成功恢复出时间点3的变更操作的记录）
mysql -uroot -p -o backuptest < /data/mysql/mysql3306/backup/binlog_bak.sql 
mysql -uroot -p -e "select * from backuptest.student;"