MySQL误操作恢复指南

检查是否开启日志记录

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Value值为ON， 则开启了MySQL日志记录

打开MySQL日志添加以下参数在my.cnf中

[mysqld]
server_id = 1
log_bin = /var/log/mysql/master-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full # 此处设置为full

误操作恢复实验

以下操作是我实际操作过的。按照 https://www.cnblogs.com/gomysql/p/3582058.html 这里的步骤

UPDATE忘加WHERE条件误操作恢复

1 创建测试表

create table t1 (
    id int unsigned not null auto_increment,
    name char(20) not null,
    sex enum('f','m') not null default 'm',
    address varchar(30) not null,
    primary key(id)
);

2 添加测试数据

insert into t1 (name,sex,address) values('daiiy','m','guangzhou');
insert into t1 (name,sex,address) values('tom','f','shanghai');
insert into t1 (name,sex,address) values('liany','m','beijing'); 
insert into t1 (name,sex,address) values('lilu','m','zhuhai');

3 将id等于2的用户的地址改为zhuhai，update时没有添加where条件

mysql> select * from t1;
+----+-------+-----+-----------+
| id | name  | sex | address   |
+----+-------+-----+-----------+
|  1 | daiiy | m   | guangzhou |
|  2 | tom   | f   | shanghai  |
|  3 | liany | m   | beijing   |
|  4 | lilu  | m   | zhuhai    |
+----+-------+-----+-----------+
4 rows in set (0.01 sec)

mysql> update t1 set address='zhuhai';
Query OK, 3 rows affected (0.05 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> select * from t1;
+----+-------+-----+---------+
| id | name  | sex | address |
+----+-------+-----+---------+
|  1 | daiiy | m   | zhuhai  |
|  2 | tom   | f   | zhuhai  |
|  3 | liany | m   | zhuhai  |
|  4 | lilu  | m   | zhuhai  |
+----+-------+-----+---------+
4 rows in set (0.00 sec)

4 开始恢复

-- 先锁表，防止数据再次被污染
mysql> lock tables t1 read ;
Query OK, 0 rows affected (0.00 sec)

-- 查看正在写哪个二进制日志
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000007 |   283391 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5 分析二进制日志

# 在mysql-bin目录下例如：/var/lib/mysql下执行
[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS master-bin.000007 | grep -B 15 'zhuhai'

# at 1892
# at 1945
#190115  6:40:12 server id 100  end_log_pos 1945 CRC32 0x6dabfb7f       Annotate_rows:
#Q> update t1 set address='zhuhai'
#190115  6:40:12 server id 100  end_log_pos 1999 CRC32 0xb36d3db3       Table_map: `test`.`t1` mapped to number 22
# at 1999
#190115  6:40:12 server id 100  end_log_pos 2149 CRC32 0x40d65cde       Update_rows: table id 22 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='guangzhou' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='shanghai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='beijing' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */

6 处理分析日志

[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS master-bin.000007 | sed -n '/# at 1945/,/COMMIT/p' > t1.txt  

[root@localhost mysql]# cat t1.txt

# at 1945
#190115  6:40:12 server id 100  end_log_pos 1945 CRC32 0x6dabfb7f       Annotate_rows:
#Q> update t1 set address='zhuhai'
#190115  6:40:12 server id 100  end_log_pos 1999 CRC32 0xb36d3db3       Table_map: `test`.`t1` mapped to number 22
# at 1999
#190115  6:40:12 server id 100  end_log_pos 2149 CRC32 0x40d65cde       Update_rows: table id 22 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='guangzhou' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='shanghai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='beijing' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
# at 2149
#190115  6:40:12 server id 100  end_log_pos 2180 CRC32 0x1cf61db8       Xid = 40
COMMIT/*!*/;

# 正则过滤出有效信息

[root@localhost mysql]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' t1.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > recover.sql
[root@localhost mysql]# cat recover.sql

UPDATE `test`.`t1`
SET
  @1=1 ,
  @2='daiiy' ,
  @3=2 ,
  @4='guangzhou' ,
WHERE
  @1=1 ;
UPDATE `test`.`t1`
SET
  @1=2 ,
  @2='tom' ,
  @3=1 ,
  @4='shanghai' ,
WHERE
  @1=2 ;
UPDATE `test`.`t1`
SET
  @1=3 ,
  @2='liany' ,
  @3=2 ,
  @4='beijing' ,
WHERE
  @1=3 ;

# 将文件中的@1,@2,@3,@4替换为t1表中id，name，sex，address字段，并删除最后字段的","号

[root@localhost mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql
[root@localhost mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql
[root@localhost mysql]# cat recover.sql

UPDATE `test`.`t1`
SET
  id=1 ,
  name='daiiy' ,
  sex=2 ,
  address='guangzhou' 
WHERE
  id=1 ;
UPDATE `test`.`t1`
SET
  id=2 ,
  name='tom' ,
  sex=1 ,
  address='shanghai' 
WHERE
  id=2 ;
UPDATE `test`.`t1`
SET
  id=3 ,
  name='liany' ,
  sex=2 ,
  address='beijing' 
WHERE
  id=3 ;

7 导入数据

mysql> source recover.sql

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+-------+-----+-----------+
| id | name  | sex | address   |
+----+-------+-----+-----------+
|  1 | daiiy | m   | guangzhou |
|  2 | tom   | f   | shanghai  |
|  3 | liany | m   | beijing   |
|  4 | lilu  | m   | zhuhai    |
+----+-------+-----+-----------+
4 rows in set (0.01 sec)


mysql> unlock tables;

DELETE忘加WHERE条件误删除恢复

1 模拟误删除数据

mysql> select * from t1;
+----+-------+-----+-----------+
| id | name  | sex | address   |
+----+-------+-----+-----------+
|  1 | daiiy | m   | guangzhou |
|  2 | tom   | f   | shanghai  |
|  3 | liany | m   | beijing   |
|  4 | lilu  | m   | zhuhai    |
+----+-------+-----+-----------+
4 rows in set (0.01 sec)

mysql> delete from t1;
Query OK, 4 rows affected (0.05 sec)

mysql> select * from t1;
Empty set (0.00 sec)

2 在binlog中查询相关操作

[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS master-bin.000007 | sed -n '/### DELETE FROM `test`.`t1`/,/COMMIT/p' > delete.txt
[root@localhost mysql]# cat delete.txt

### DELETE FROM `test`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='guangzhou' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### DELETE FROM `test`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='shanghai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### DELETE FROM `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='beijing' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### DELETE FROM `test`.`t1`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='lilu' /* STRING(80) meta=65104 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
# at 3370
#190115  7:09:26 server id 100  end_log_pos 3401 CRC32 0x086183f5       Xid = 55
COMMIT/*!*/;

3 将txt中内容转为SQL

[root@localhost mysql]# cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > t1.sql
[root@localhost mysql]# cat t1.sql

INSERT INTO `test`.`t1`
SELECT
  1 ,
  'daiiy' ,
  2 ,
  'guangzhou' ;
INSERT INTO `test`.`t1`
SELECT
  2 ,
  'tom' ,
  1 ,
  'shanghai' ;
INSERT INTO `test`.`t1`
SELECT
  3 ,
  'liany' ,
  2 ,
  'beijing' ;
INSERT INTO `test`.`t1`
SELECT
  4 ,
  'lilu' ,
  2 ,
  'zhuhai' ;

4 导入数据

mysql> source t1.sql
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+-------+-----+-----------+
| id | name  | sex | address   |
+----+-------+-----+-----------+
|  1 | daiiy | m   | guangzhou |
|  2 | tom   | f   | shanghai  |
|  3 | liany | m   | beijing   |
|  4 | lilu  | m   | zhuhai    |
+----+-------+-----+-----------+
4 rows in set (0.01 sec)