mysql误删操作回复
在影响库的正常运行回复快速回复
环境前部署
开启二进制文件
vim /etc/my.cnf
############
server-id=1
log-bin=bin
binlog-format=ROW
###############
第一步：查看数据
mysql> select * from xiaoshou;
+----+-------+-------+----+----------------------------+
| id | name | jiage | sl | SJ |
+----+-------+-------+----+----------------------------+
| 1 | baish | 3 | 2 | 2019-08-31 09:17:26.000000 |
| 2 | baish | 3 | 2 | 2019-08-31 09:17:29.000000 |
| 3 | baish | 3 | 2 | 2019-08-31 09:17:32.000000 |
| 4 | baish | 3 | 2 | 2019-08-31 09:17:35.000000 |
| 5 | baish | 3 | 2 | 2019-08-31 09:40:44.000000 |
| 6 | baish | 3 | 2 | 2019-08-31 09:40:47.000000 |
| 7 | baish | 3 | 2 | 2019-08-31 09:40:50.000000 |
| 8 | baish | 3 | 2 | 2019-08-31 09:40:53.000000 |
| 9 | baish | 3 | 2 | 2019-08-31 09:40:56.000000 |
| 10 | baish | 3 | 2 | 2019-08-31 09:40:59.000000 |
| 11 | baish | 3 | 2 | 2019-08-31 09:41:02.000000 |
| 12 | baish | 3 | 2 | 2019-08-31 09:41:05.000000 |
| 13 | baish | 3 | 2 | 2019-08-31 09:41:08.000000 |
| 14 | baish | 3 | 2 | 2019-08-31 09:41:11.000000 |
| 15 | baish | 3 | 2 | 2019-08-31 09:41:14.000000 |
| 16 | baish | 3 | 2 | 2019-08-31 09:55:02.000000 |
| 17 | baish | 3 | 2 | 2019-08-31 09:55:12.000000 |
| 18 | baish | 3 | 2 | 2019-08-31 09:55:22.000000 |
| 19 | baish | 3 | 2 | 2019-08-31 09:55:32.000000 |
| 20 | baish | 3 | 2 | 2019-08-31 09:55:42.000000 |
| 21 | baish | 3 | 2 | 2019-08-31 09:55:52.000000 |
| 22 | baish | 3 | 2 | 2019-08-31 09:56:02.000000 |
| 23 | baish | 3 | 2 | 2019-08-31 09:56:12.000000 |
| 24 | baish | 3 | 2 | 2019-08-31 09:56:22.000000 |
| 25 | baish | 3 | 2 | 2019-08-31 09:56:32.000000 |
| 26 | baish | 3 | 2 | 2019-08-31 09:56:42.000000 |
| 27 | baish | 3 | 2 | 2019-08-31 09:56:52.000000 |
| 28 | baish | 3 | 2 | 2019-08-31 09:57:02.000000 |
| 29 | baish | 3 | 2 | 2019-08-31 09:57:12.000000 |
| 30 | baish | 3 | 2 | 2019-08-31 09:57:22.000000 |
| 31 | baish | 3 | 2 | 2019-08-31 09:57:32.000000 |
| 32 | baish | 3 | 2 | 2019-08-31 09:57:42.000000 |
+----+-------+-------+----+----------------------------+
第二步：删除表
mysql> delete from zxw.xiaoshou;
Query OK, 33 rows affected (0.01 sec)
第三步：查看数据
mysql> select * from xiaoshou;
Empty set (0.00 sec)
第四步：不小心删除表，不要慌，首先查看二进制文件
mysql> show master status\G
*************************** 1. row ***************************
File: bin.000006 这个是二进制文件目录
Position: 137813
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
第五步：导出二进制文件到/root下下面
[root@zxw66 ~]# mysqlbinlog /var/lib/mysql/bin.000006 > /root/6bin.sql
第六步：修改导出的二进制文件
[root@zxw66 ~]# vim 6bin.sql
查找到删除语句
/Delete
按一下G跳转到最后
在按一下N （N是从后往前高亮查找）
把delete的语句删除（圈起来的都删除）
# at 136889
#190831 10:08:28 server id 1 end_log_pos 137782 CRC32 0xc45e3d2f Delete_rows: table id 70 flags: STMT_END_F
BINLOG '
nNZpXRMBAAAAOQAAALkWAgAAAEYAAAAAAAEAA3p4dwAIeGlhb3Nob3UABQMPAwMRA/8ABgAlzuQD
nNZpXSABAAAAfQMAADYaAgAAAEYAAAAAAAEAAgAF/+ABAAAABWJhaXNoAwAAAAIAAABdacqmAAAA
4AIAAAAFYmFpc2gDAAAAAgAAAF1pyqkAAADgAwAAAAViYWlzaAMAAAACAAAAXWnKrAAAAOAEAAAA
BWJhaXNoAwAAAAIAAABdacqvAAAA4AUAAAAFYmFpc2gDAAAAAgAAAF1p0BwAAADgBgAAAAViYWlz
aAMAAAACAAAAXWnQHwAAAOAHAAAABWJhaXNoAwAAAAIAAABdadAiAAAA4AgAAAAFYmFpc2gDAAAA
AgAAAF1p0CUAAADgCQAAAAViYWlzaAMAAAACAAAAXWnQKAAAAOAKAAAABWJhaXNoAwAAAAIAAABd
adArAAAA4AsAAAAFYmFpc2gDAAAAAgAAAF1p0C4AAADgDAAAAAViYWlzaAMAAAACAAAAXWnQMQAA
AOANAAAABWJhaXNoAwAAAAIAAABdadA0AAAA4A4AAAAFYmFpc2gDAAAAAgAAAF1p0DcAAADgDwAA
AAViYWlzaAMAAAACAAAAXWnQOgAAAOAQAAAABWJhaXNoAwAAAAIAAABdadN2AAAA4BEAAAAFYmFp
c2gDAAAAAgAAAF1p04AAAADgEgAAAAViYWlzaAMAAAACAAAAXWnTigAAAOATAAAABWJhaXNoAwAA
AAIAAABdadOUAAAA4BQAAAAFYmFpc2gDAAAAAgAAAF1p054AAADgFQAAAAViYWlzaAMAAAACAAAA
XWnTqAAAAOAWAAAABWJhaXNoAwAAAAIAAABdadOyAAAA4BcAAAAFYmFpc2gDAAAAAgAAAF1p07wA
AADgGAAAAAViYWlzaAMAAAACAAAAXWnTxgAAAOAZAAAABWJhaXNoAwAAAAIAAABdadPQAAAA4BoA
AAAFYmFpc2gDAAAAAgAAAF1p09oAAADgGwAAAAViYWlzaAMAAAACAAAAXWnT5AAAAOAcAAAABWJh
aXNoAwAAAAIAAABdadPuAAAA4B0AAAAFYmFpc2gDAAAAAgAAAF1p0/gAAADgHgAAAAViYWlzaAMA
AAACAAAAXWnUAgAAAOAfAAAABWJhaXNoAwAAAAIAAABdadQMAAAA4CAAAAAFYmFpc2gDAAAAAgAA
AF1p1BYAAADgIQAAAAViYWlzaAMAAAACAAAAXWnUIAAAAC89XsQ=
'/*!*/;
# at 137782
第七步：回复数据
方法一：在数据外面回复
[root@zxw66 ~]# mysql -uroot -p123 zxw < 6bin.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 10022: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''/*!*/;
# at 137782
#190831 10:08:28 server id 1 end_log_pos 137813 CRC32 0x81f' at line 1
方法二：在数据里回复
mysql> source 6bin.sql
第八步：再次查看数据
mysql> select * from xiaoshou;
+----+-------+-------+----+----------------------------+
| id | name | jiage | sl | SJ |
+----+-------+-------+----+----------------------------+
| 1 | baish | 3 | 2 | 2019-08-31 09:17:26.000000 |
| 2 | baish | 3 | 2 | 2019-08-31 09:17:29.000000 |
| 3 | baish | 3 | 2 | 2019-08-31 09:17:32.000000 |
| 4 | baish | 3 | 2 | 2019-08-31 09:17:35.000000 |
| 5 | baish | 3 | 2 | 2019-08-31 09:40:44.000000 |
| 6 | baish | 3 | 2 | 2019-08-31 09:40:47.000000 |
| 7 | baish | 3 | 2 | 2019-08-31 09:40:50.000000 |
| 8 | baish | 3 | 2 | 2019-08-31 09:40:53.000000 |
| 9 | baish | 3 | 2 | 2019-08-31 09:40:56.000000 |
| 10 | baish | 3 | 2 | 2019-08-31 09:40:59.000000 |
| 11 | baish | 3 | 2 | 2019-08-31 09:41:02.000000 |
| 12 | baish | 3 | 2 | 2019-08-31 09:41:05.000000 |
| 13 | baish | 3 | 2 | 2019-08-31 09:41:08.000000 |
| 14 | baish | 3 | 2 | 2019-08-31 09:41:11.000000 |
| 15 | baish | 3 | 2 | 2019-08-31 09:41:14.000000 |
| 16 | baish | 3 | 2 | 2019-08-31 09:55:02.000000 |
| 17 | baish | 3 | 2 | 2019-08-31 09:55:12.000000 |
| 18 | baish | 3 | 2 | 2019-08-31 09:55:22.000000 |
| 19 | baish | 3 | 2 | 2019-08-31 09:55:32.000000 |
| 20 | baish | 3 | 2 | 2019-08-31 09:55:42.000000 |
| 21 | baish | 3 | 2 | 2019-08-31 09:55:52.000000 |
| 22 | baish | 3 | 2 | 2019-08-31 09:56:02.000000 |
| 23 | baish | 3 | 2 | 2019-08-31 09:56:12.000000 |
| 24 | baish | 3 | 2 | 2019-08-31 09:56:22.000000 |
| 25 | baish | 3 | 2 | 2019-08-31 09:56:32.000000 |
| 26 | baish | 3 | 2 | 2019-08-31 09:56:42.000000 |
| 27 | baish | 3 | 2 | 2019-08-31 09:56:52.000000 |
| 28 | baish | 3 | 2 | 2019-08-31 09:57:02.000000 |
| 29 | baish | 3 | 2 | 2019-08-31 09:57:12.000000 |
| 30 | baish | 3 | 2 | 2019-08-31 09:57:22.000000 |
| 31 | baish | 3 | 2 | 2019-08-31 09:57:32.000000 |
| 32 | baish | 3 | 2 | 2019-08-31 09:57:42.000000 |
| 33 | baish | 3 | 2 | 2019-08-31 09:57:52.000000 |
+----+-------+-------+----+----------------------------+
33 rows in set (0.00 sec)
