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.dump4.，模拟业务> 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_PASSWD192.168.1.210 /usr/local/mysql-5.7/ /home/mysql/mysqldb db slave 33333 3333301 root oracle[mysql@bogon ~]$ sh init57_v1.3_20180402.sh2)使用mysqldump 恢复（在employees5.dump的路径下）[mysql@bogon ~]$ /usr/local/mysql-5.7/bin/mysql -uroot -poracle -S/home/mysql/mysqldb/db_slave/mysql.sock test < employees5.dump3）查找对应的binlog点，应用binlogi. 找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 156ii. 应用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