(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)