mysql在SBR下恢复误删除的表

(system@localhost) [zeng]> select * from t;
+------+--------------------------+----------------------------------+
| id   | context                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 4    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |

将表备份一下：
mysqldump -usystem -psafe2016 -S /mysql/server/conf/mysql.sock zeng t >/mysql/backup/t.sql

(system@localhost) [zeng]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 |      235 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

(system@localhost) [zeng]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000014 |   3253595 |
| mysql-bin.000015 |       167 |
| mysql-bin.000016 |       235 |
+------------------+-----------+

错误更新操作：
(system@localhost) [zeng]> update t set context='shanghai' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

后面又增加了记录：
(system@localhost) [zeng]> insert into t values (5,'chongqing','');
Query OK, 1 row affected (0.01 sec)

查看表的记录数：

(system@localhost) [zeng]> select * from t;
+------+--------------------------+----------------------------------+
| id   | context                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | shanghai                 | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 4    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
| 5    | chongqing                |                                  |
+------+--------------------------+----------------------------------+
5 rows in set (0.00 sec)

如何恢复该表，跳过错误？

先把表备份一份：
create table t_bak as select * from t;
然后drop 表t:
drop table t;
利用前面备份的表t，用mysql命令恢复回去：
mysql -usystem -psafe2016 -S /mysql/server/conf/mysql.sock zeng </mysql/backup/t.sql

(system@localhost) [zeng]> desc t;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(100) | YES  |     | NULL    |       |
| context    | blob         | YES  |     | NULL    |       |
| hash_value | varchar(40)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

(system@localhost) [zeng]> select * from t;
+------+--------------------------+----------------------------------+
| id   | context                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 4    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
4 rows in set (0.00 sec)

需要将更新误操作后面插入的记录恢复回去，且要跳过误操作，需要先查看binlog，确认误操作前后的时间点或位置及所有对t表操作过的命令。

(system@localhost) [zeng]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000014 |   3253595 |
| mysql-bin.000015 |       167 |
| mysql-bin.000016 |      1929 |
+------------------+-----------+

(system@localhost) [zeng]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 |     1929 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

分析mysql-bin.000016确认时间点或位置：2015-08-07 16:05:46或314及操作命令。

314位置的SQL就是误操作的SQL:

# at 314
#150807 16:05:46 server id 2433306  end_log_pos 430 CRC32 0xb3ed1fbe    Query   thread_id=43    exec_time=0     error_code=0
use `zeng`/*!*/;
SET TIMESTAMP=1438934746/*!*/;
update t set context='shanghai' where id=1

540位置的SQL是需要重新执行的SQL:

/*!*/;
# at 540
#150807 16:07:57 server id 2433306  end_log_pos 653 CRC32 0xbf542069    Query   thread_id=43    exec_time=0     error_code=0
SET TIMESTAMP=1438934877/*!*/;
insert into t values (5,'chongqing','')
/*!*/;

再执行如下：

(system@localhost) [zeng]> insert into t values (5,'chongqing','')
    -> ;
Query OK, 1 row affected (0.01 sec)

再查看一下，需要的记录找回来了。
(system@localhost) [zeng]> select * from t;
+------+--------------------------+----------------------------------+
| id   | context                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 4    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
| 5    | chongqing                |                                  |
+------+--------------------------+----------------------------------+
5 rows in set (0.00 sec)