MySQL:生产误删除数据恢复方法-蒲公英云

MySQL:生产误删除数据恢复方法

因为生产上误执行语句，需要找回原数据

delete from `xxx` where a = 1;

步骤

1、解析主的binlog找到执行删除语句时对应的pos点，如下：
# at 272065343
#160815 16:27:46 server id 1  end_log_pos 272065370     Xid = 12731823587
COMMIT/*!*/;
# at 272065370
#160815 16:27:21 server id 1  end_log_pos 272065441     Query   thread_id=83836962      exec_time=26    error_code=0
SET TIMESTAMP=1471249641/*!*/;
SET @@session.sql_mode=0/*!*/;
BEGIN
/*!*/;
# at 272065441
#160815 16:27:21 server id 1  end_log_pos 272065566     Query   thread_id=83836962      exec_time=26    error_code=0
SET TIMESTAMP=1471249641/*!*/;
delete from `xxx` where a = 1;
/*!*/;
# at 272065566
#160815 16:27:21 server id 1  end_log_pos 272065593     Xid = 12731793231
COMMIT/*!*/;
2、找一台空机器，恢复备份
/usr/bin/innobackupex-1.5.1 --user=root --host=127.0.0.1 --port=3306 --defaults-file=/data/mysql/mysql3307/my.cnf --apply-log /data/mysqlbak/db_bak/20160815_3306
/usr/bin/innobackupex-1.5.1 --user=root --host=127.0.0.1 --port=3306 --defaults-file=/data/mysql/mysql3307/my.cnf --copy-back /data/mysqlbak/db_bak/20160815_3306
3、因为只需要恢复一张表 xxx，修改my.cnf添加参数
replicate-wild-do-table = x.xxx 
4、搭建从库，恢复到制定位置
change master to master_host='192.168.x.x', 
                            master_port=3306,
                            master_user='qqq', 
                            master_password='qqq', 
                            master_log_file='mysql-bin.001002', 
                            master_log_pos=203021225;
START SLAVE UNTIL master_log_file='mysql-bin.001003',master_log_pos=272065343;