Mysql 数据库模拟误操作基于binlog恢复

mysql5.7 

1.导入测试库emlpoyees数据库

向数据库中导入employees_db文件

进入数据库 > source employees.sql;

2.创建表

> use emlpoyees;

> create table test1 like employees;

> alter table test1 drop primary key; 删除主键

##> alter table test1 drop foregin key; ## 删除外键

插入数据 

> insert into test1 select * from employees limit 10000;

3.mysqldump 备份employees 库

/usr/local/mysql-5.7/bin/mysqldump -uroot -poracle -S/home/mysql/mysqldb/db_master/mysql.sock --master-data=2 --single-transaction --databases employees --set-gtid-purged=OFF --compact > employees5.dump

4.，模拟业务

> insert into test1 select * from employees limit 10000;

> delete from test1 limit 100;

> create table test2 like employees;

5.及时发现问题，进行回滚

1） 脚本初始化创建新实例

编辑initdb.info

[mysql@bogon ~]$ vi initdb.info

#IP SOFT_DIR INSTANCE_BASE_DIR INSTANCE_NAME_PREFIX INSTANCE_NAME INSTANCE_PORT SERVER_ID ADMIN_USER ADMIN_PASSWD

192.168.1.210 /usr/local/mysql-5.7/ /home/mysql/mysqldb db slave 33333 3333301 root oracle

[mysql@bogon ~]$ sh init57_v1.3_20180402.sh

2)使用mysqldump 恢复（在employees5.dump的路径下）

[mysql@bogon ~]$ /usr/local/mysql-5.7/bin/mysql -uroot -poracle -S/home/mysql/mysqldb/db_slave/mysql.sock test < employees5.dump

3）查找对应的binlog点，应用binlog

i. 找start点

[mysql@bogon ~]$ vi employees5.dump

-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000013', MASTER_LOG_POS=276137;

ii.找stop点，（delete的时刻）

[mysql@bogon binlog]$ mysqlbinlog -v -v -v master-bin.000013 >1.txt

[mysql@bogon binlog]$ vi 1.txt

/delete 查找delete点

# at 551869

#180807 16:01:41 server id 6666601 end_log_pos 551920 CRC32 0x3ee37a2d Rows_query

# delete from test1 limit 100

# at 551920

#180807 16:01:41 server id 6666601 end_log_pos 551984 CRC32 0x7d5e7aff Table_map: `employees`.`test1` mapped to number 156

ii. 应用binlog恢复

[mysql@bogon binlog]$ mysqlbinlog --start-position=276137 --stop-position=551869 --skip-gtids /home/mysql/mysqldb/db_master/binlog/master-bin.000013|mysql -uroot -poracle -S/home/mysql/mysqldb/db_slave/mysql.sock employees

[mysql@bogon ~]$ mysqlbinlog --start-position=554871 --skip-gtids /home/mysql/mysqldb/db_master/binlog/master-bin.000013|mysql -uroot -poracle -S/home/mysql/mysqldb/db_slave/mysql.sock employees