mysql 清空表 delete后怎么恢复
答案:1 悬赏:50
解决时间 2021-01-19 06:55
- 提问者网友:雪舞兮
- 2021-01-18 10:57
mysql 清空表 delete后怎么恢复
最佳答案
- 二级知识专家网友:洎扰庸人
- 2021-01-18 12:05
delete 忘加where条件误删除恢复(binglog格式必须是ROW)
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 |
+----+-------+-----+-----------+
rows in set (0.00 sec)
mysql> delete from t1;
Query OK, 4 rows affected (0.03 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql>2、在binglog中去查找相关记录
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETe FROM db01.t1/,/COMMIT/p' > delete.txt
[root@localhost mysql]# cat delete.txt
### DELETE FROM db01.t1
### WHERe
### @1=1
### @2='daiiy'
### @3=2
### @4='guangzhou'
### DELETe FROM db01.t1
### WHERe
### @1=2
### @2='tom'
### @3=1
### @4='shanghai'
### DELETe FROM db01.t1
### WHERe
### @1=3
### @2='liany'
### @3=2
### @4='beijing'
### DELETe FROM db01.t1
### WHERe
### @1=4
### @2='lilu'
### @3=2
### @4='zhuhai'
# at 2719
#140305 11:41:00 server id 1 end_log_pos 2746 Xid = 78
COMMIT;
[root@localhost mysql]#3、将记录转换成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 db01.t1
SELECT
,
'daiiy' ,
,
'guangzhou' ;
INSERT INTO db01.t1
SELECT
,
'tom' ,
,
'shanghai' ;
INSERT INTO db01.t1
SELECT
,
'liany' ,
,
'beijing' ;
INSERT INTO db01.t1
SELECT
,
'lilu' ,
,
'zhuhai' ;
[root@localhost mysql]#4、导入数据,验证数据完整性。
mysql> source t1.sql;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
ERROR 1046 (3D000): No database selected
mysql> select * from db01.t1;
+----+-------+-----+-----------+
| id | name | sex | address |
+----+-------+-----+-----------+
| 1 | daiiy | m | guangzhou |
| 2 | tom | f | shanghai |
| 3 | liany | m | beijing |
| 4 | lilu | m | zhuhai |
+----+-------+-----+-----------+
rows in set (0.00 sec)
mysql>到这里数据就完整回来了。
将binglog格式设置为row有利有弊,好处是记录了每一行的实际变化,在主从复制时也不容易出问题。但是由于记录每行的变化,会占用大量磁盘,主从复制时带宽占用会有所消耗。到底是使用row还是mixed,需要在实际工作中自己去衡量,但从整体上来说,binglog的格式设置为row,都是不二的选择
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 |
+----+-------+-----+-----------+
rows in set (0.00 sec)
mysql> delete from t1;
Query OK, 4 rows affected (0.03 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql>2、在binglog中去查找相关记录
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETe FROM db01.t1/,/COMMIT/p' > delete.txt
[root@localhost mysql]# cat delete.txt
### DELETE FROM db01.t1
### WHERe
### @1=1
### @2='daiiy'
### @3=2
### @4='guangzhou'
### DELETe FROM db01.t1
### WHERe
### @1=2
### @2='tom'
### @3=1
### @4='shanghai'
### DELETe FROM db01.t1
### WHERe
### @1=3
### @2='liany'
### @3=2
### @4='beijing'
### DELETe FROM db01.t1
### WHERe
### @1=4
### @2='lilu'
### @3=2
### @4='zhuhai'
# at 2719
#140305 11:41:00 server id 1 end_log_pos 2746 Xid = 78
COMMIT;
[root@localhost mysql]#3、将记录转换成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 db01.t1
SELECT
,
'daiiy' ,
,
'guangzhou' ;
INSERT INTO db01.t1
SELECT
,
'tom' ,
,
'shanghai' ;
INSERT INTO db01.t1
SELECT
,
'liany' ,
,
'beijing' ;
INSERT INTO db01.t1
SELECT
,
'lilu' ,
,
'zhuhai' ;
[root@localhost mysql]#4、导入数据,验证数据完整性。
mysql> source t1.sql;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
ERROR 1046 (3D000): No database selected
mysql> select * from db01.t1;
+----+-------+-----+-----------+
| id | name | sex | address |
+----+-------+-----+-----------+
| 1 | daiiy | m | guangzhou |
| 2 | tom | f | shanghai |
| 3 | liany | m | beijing |
| 4 | lilu | m | zhuhai |
+----+-------+-----+-----------+
rows in set (0.00 sec)
mysql>到这里数据就完整回来了。
将binglog格式设置为row有利有弊,好处是记录了每一行的实际变化,在主从复制时也不容易出问题。但是由于记录每行的变化,会占用大量磁盘,主从复制时带宽占用会有所消耗。到底是使用row还是mixed,需要在实际工作中自己去衡量,但从整体上来说,binglog的格式设置为row,都是不二的选择
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
点此我要举报以上问答信息!
大家都在看
推荐信息