How to use binlog rollback for erroneous deletion of delete in mysql
This article mainly introduces how to delete delete error in mysql using binlog rollback, the article is very detailed, has a certain reference value, interested friends must read it!
Mysql > select * from tet3
+-+ +
| | id | dd |
+-+ +
| | 1 | XX |
| | 2 | YY |
| | 3 | aaa |
| | 4 | 5002301999X |
| | 5 | 0000000X |
| | 6 | oi80 |
| | 7 | 887 |
| | 8 | 887 |
| | 10 | jju |
+-+ +
9 rows in set (0.03 sec)
Mysql > delete from tet3 where id > 3
Query OK, 6 rows affected (0.03 sec)
Mysql > select * from tet3
+-+ +
| | id | dd |
+-+ +
| | 1 | XX |
| | 2 | YY |
| | 3 | aaa |
+-+ +
3 rows in set (0.00 sec)
[root@localhost data] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-v-v db-bin.000016 | sed-n'/ # DELETE FROM `test`.tet3` /, / COMMIT/p' > / root/delete.txt
[root@localhost data] # more / root/delete.txt
# DELETE FROM `test`.`tet3`
# WHERE
# @ 1room4 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2 VARSTRING 5002301999X' / * meta=20 nullable=1 is_null=0 * /
# DELETE FROM `test`.`tet3`
# WHERE
# @ 1room5 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2 VARSTRING 0000000X' / * meta=20 nullable=1 is_null=0 (20) /
# DELETE FROM `test`.`tet3`
# WHERE
# @ 1room6 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2roomoi80' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /
# DELETE FROM `test`.`tet3`
# WHERE
# @ 1room7 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2 / VARSTRING (20) meta=20 nullable=1 is_null=0 * /
# DELETE FROM `test`.`tet3`
# WHERE
# @ 1room8 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2 / VARSTRING (20) meta=20 nullable=1 is_null=0 * /
# DELETE FROM `test`.`tet3`
# WHERE
# @ 1room10 / * INT meta=0 nullable=0 is_null=0 * /
# @ 2 VARSTRING (20) meta=20 nullable=1 is_null=0 * /
# at 3640
# 150426 23:17:36 server id 199 end_log_pos 3671 CRC32 0xb946f7f5 Xid = 150426
Com _ MIT _ blank /
[root@localhost ~] # cat delete.txt | sed-n'/ # / p' | sed's FROM/INSERT INTO/g;s/WHERE/SELECT/g;' delete FROM/INSERT INTO/g;s/WHERE/SELECT/g;' # / / g _ politics /\ /\ *. /, / g _ _ sed _
[root@localhost ~] #
[root@localhost ~] #
[root@localhost ~] # more insert.sql
INSERT INTO `test`.`tet3`
SELECT
4
'5002301999X'
INSERT INTO `test`.`tet3`
SELECT
5
'0000000X'
INSERT INTO `test`.`tet3`
SELECT
6
'oi80'
INSERT INTO `test`.`tet3`
SELECT
7
'887'
INSERT INTO `test`.`tet3`
SELECT
8
'887'
INSERT INTO `test`.`tet3`
SELECT
10
'jju'
That's all we need to roll back the sql. Just execute it.
The command explains:
Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v-v db-bin.000016 | sed-n'/ # DELETE FROM `test`.`tet3` /, / COMMIT/p' > / root/delete.txt
Mysqlbinlog-no-defaults-base64-output=decode-rows-v-v db-bin.000016
This belongs to the mysqlbinlog command parameter.
-- no-defaults prevents the mysqlbinlog tool from reading parameters from any configuration file (ensuring password security)
-- base64-output=decode-rows shows the sql changes brought about by row mode
-v-v is viewed as a binary log file
Sed-n'/ # DELETE FROM `test`.tet3` /, / COMMIT/p'
Print content from'# DELETE FROm `test`.`tet3`'to the end of 'COMMIT'.
Cat delete.txt | sed-n'/ # / p' | sed's picket accounts # / / gmasters\ /\ *. * /, / glemencyDelete FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed-r's / (@ 2.*), /\ 1Universe g' | sed's hand @ [1-9] = / / g'> insert.sql
Sed-n'/ # / p'
Print the line at the beginning of'#
Sed's Universe FROM/INSERT INTO/g;s/WHERE/SELECT/g;' # / / gscarf /\ /\ *. * / / gttersAccord delete FROM/INSERT INTO/g;s/WHERE/SELECT/g;'
Separate interpretation: sCompact accounts # / / g leading s/\ /\ *. * / / g; this part is to remove'# 'and / *.. * /
The s/DELETE FROM/INSERT INTO/g; part, right? change delete from to insert into.
This part is changed from where to select
| | sed-r's / (@ 2.*), /\ 1Placement g' |
-r is a regular expression, meaning to add a semicolon at the end of the line beginning with @ 2.
Sed's pick @ [1-9] = / / g'
This is easy. That is, the removal of @ 1 Mutual 9. Of course, there are only @ 1 and @ 2 in this example.
The above is all the contents of the article "how to use binlog rollback to delete delete mistakenly in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
