cdh误删mysql怎么回复_mysql误删操作回复

在影响库的正常运行回复快速回复

环境前部署

开启二进制文件

vim /etc/my.cnf

############

server-id=1

log-bin=bin

binlog-format=ROW

###############

第一步:查看数据

mysql> select * fromxiaoshou;+----+-------+-------+----+----------------------------+

| 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:137813Binlog_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的语句删除(圈起来的都删除)

# at136889#190831 10:08:28 server id 1 end_log_pos 137782 CRC32 0xc45e3d2f Delete_rows: table id 70flags: 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 lineinterfacecan be insecure.

ERROR1064 (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 ''/*!*/;

# at137782#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)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值