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

刺骨的言语ヽ痛彻心扉 2022-09-25 08:19 285阅读 0赞

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

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;