Neverending story. Commited changes need to be rollback. Some vendors are not resolving this situation at all (stupidity of user is not their problem, indeed), some are providing solutions (Oracle). One of the latest news from MariaDB (10.2) was adding this feature into mysqlbinlog utility.<\/p>\n
<\/p>\n
Let’s create data with faulty DELETE command inside \ud83d\ude42 :<\/p>\n
[crayon lang=“shell“]<\/p>\n
MariaDB [test]> create table t1(id int);
\nQuery OK, 0 rows affected (0.008 sec)<\/p>\n
MariaDB [test]> insert into t1 values(1),(2),(3),(4),(5);
\nQuery OK, 5 rows affected (0.003 sec)
\nRecords: 5 Duplicates: 0 Warnings: 0<\/p>\n
MariaDB [test]> insert into t1 values(6);
\nQuery OK, 1 row affected (0.004 sec)<\/p>\n
MariaDB [test]> insert into t1 values(7);
\nQuery OK, 1 row affected (0.001 sec)<\/p>\n
MariaDB [test]> delete from test.t1;
\nQuery OK, 7 rows affected (0.002 sec)<\/p>\n
MariaDB [test]> create table t2(id int); insert into t2 values(10),(11),(12);
\nQuery OK, 0 rows affected (0.034 sec)<\/p>\n
Query OK, 3 rows affected (0.001 sec)
\nRecords: 3 Duplicates: 0 Warnings: 0<\/p>\n
MariaDB [test]> exit<\/p>\n
[\/crayon]<\/p>\n
Situation is pretty clear – in table t1 there are 1-7 numbers, inserted in 3 transactions (AUTOCOMMIT is on) and after accidentaly deleting this table (beware for TRUNCATE TABLE, it is not written into binary log at all!) I created table t2 with just 3 values.<\/p>\n
1) Locating where is our DELETE command residing.<\/strong><\/p>\n As long as we are storing binary (transactional) logs,we\u00a0 are able to flashback any transaction. Going through hundreds of gigabytes is a bit boring -> it is good to know when this transaction happened (~hours precision). After extracting binary log into human-readable format we will grep DELETE command. Enough of theory:<\/p>\n [crayon lang=“shell“]<\/p>\n mysqlbinlog -v –base64-output=DECODE-ROWS localhost-bin.000002 > mysql-bin.000002.sql<\/p>\n [\/crayon]<\/p>\n On fresh MariDB installation it’s not a big deal to detect proper binary log file (in case you are having tons of these files, just extract suspicious ones). Target file looks like this:<\/p>\n [crayon lang=“shell“]<\/p>\n [root@localhost mysql]# tail -15 mysql-bin.000002.sql [\/crayon]<\/p>\n We can grab string „DELETE“ or just annotated „Delete_rows“ into grep command to find out what range of lines will be in our interest:<\/p>\n [crayon lang=“shell“]<\/p>\n [root@localhost mysql]# grep -nr -B 10 -A 30 Delete_rows mysql-bin.000002.sql [\/crayon]<\/p>\n Bingo! In case of huge line numbers, grep it better and extract into standalone file. Please have in mind that transaction begins right after BEGIN word and ending right before COMMIT word. In our case 1212 and 1398. Extracted paragraph is performed by:<\/p>\n <\/p>\n 2) Preparing flashback binlog fragment<\/strong><\/p>\n Looks great, we can prepare again those lines, this time with clause –flashback, opposite operation will be generated:<\/p>\n [crayon lang=“shell“]<\/p>\n mysqlbinlog –flashback -v –base64-output=DECODE-ROWS –start-position=1212 –stop-position=1398 localhost-bin.000002 > localhost-bin.000002_flash.sql<\/p>\n [\/crayon]<\/p>\n There is no surprise how resulted file will be looking:<\/p>\n [crayon lang=“shell“]<\/p>\n [root@localhost mysql]# cat localhost-bin.000002_flash.sql [\/crayon]<\/p>\n See those changes? It reverted original operation (DELETE) and you are having few INSERT INTO lines there. We need to convert this fragment into binary log format again:<\/p>\n [crayon lang=“shell“]<\/p>\n [root@localhost mysql]# mysqlbinlog –flashback –start-position=1212 –stop-position=1398 localhost-bin.000002 > localhost-bin.000002_flash.bin [\/crayon]<\/p>\n <\/p>\n 3) Propagating changes into database + verification<\/strong><\/p>\n From mariadb client we will process this „flash“:<\/p>\n [crayon lang=“shell“]<\/p>\n MariaDB [(none)]> source localhost-bin.000002_flash.bin Query OK, 0 rows affected (0.000 sec)<\/p>\n Query OK, 0 rows affected (0.000 sec)<\/p>\n Query OK, 0 rows affected (0.000 sec)<\/p>\n Query OK, 0 rows affected (0.000 sec)<\/p>\n Query OK, 0 rows affected (0.000 sec)<\/p>\n Query OK, 0 rows affected (0.004 sec)<\/p>\n Query OK, 0 rows affected (0.000 sec)<\/p>\n Query OK, 0 rows affected (0.000 sec)<\/p>\n Query OK, 0 rows affected (0.000 sec)<\/p>\n [\/crayon]<\/p>\n And verification is really easy:<\/p>\n [crayon lang=“shell“]<\/p>\n MariaDB [(none)]> select * from test.t1; select * from test.t2; +——+ [\/crayon]<\/p>\n Flashback operation performed as expected. Newly created table t2 is intacted and „bad“ DELETE operation was successfully replaced by original values (INSERT INTO).<\/p>\n -a-<\/p>\n","protected":false},"excerpt":{"rendered":" Neverending story. Commited changes need to be rollback. Some vendors are not resolving this situation at all (stupidity of user is not their problem, indeed), some are providing solutions (Oracle). One of the latest news from MariaDB (10.2) was adding … Continue reading
\n### INSERT INTO `test`.`t2`
\n### SET
\n### @1=11
\n### INSERT INTO `test`.`t2`
\n### SET
\n### @1=12
\n# Number of rows: 3
\n# at 1723
\n#180720 10:11:44 server id 1 end_log_pos 1754 CRC32 0x9238cb53 Xid = 26
\nCOMMIT\/*!*\/;
\nDELIMITER ;
\n# End of log file
\nROLLBACK \/* added by mysqlbinlog *\/;
\n\/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*\/;
\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*\/;<\/p>\n
\n104-#180720 10:06:09 server id 1 end_log_pos 1212 CRC32 0x75c44f0c GTID 0-1-7 trans
\n105-\/*!100001 SET @@session.gtid_seq_no=7*\/\/*!*\/;
\n106-BEGIN
\n107-\/*!*\/;
\n108-# at 1212
\n109-# at 1254
\n110-#180720 10:06:09 server id 1 end_log_pos 1254 CRC32 0xff132128 Annotate_rows:
\n111-#Q> delete from test.t1
\n112-#180720 10:06:09 server id 1 end_log_pos 1299 CRC32 0x64d46f5b Table_map: `test`.`t1` mapped to number 22
\n113-# at 1299
\n114:#180720 10:06:09 server id 1 end_log_pos 1367 CRC32 0x146f66d7 Delete_rows: table id 22 flags: STMT_END_F
\n115-### DELETE FROM `test`.`t1`
\n116-### WHERE
\n117-### @1=1
\n118-### DELETE FROM `test`.`t1`
\n119-### WHERE
\n120-### @1=2
\n121-### DELETE FROM `test`.`t1`
\n122-### WHERE
\n123-### @1=3
\n124-### DELETE FROM `test`.`t1`
\n125-### WHERE
\n126-### @1=4
\n127-### DELETE FROM `test`.`t1`
\n128-### WHERE
\n129-### @1=5
\n130-### DELETE FROM `test`.`t1`
\n131-### WHERE
\n132-### @1=6
\n133-### DELETE FROM `test`.`t1`
\n134-### WHERE
\n135-### @1=7
\n136-# Number of rows: 7
\n137-# at 1367
\n138-#180720 10:06:09 server id 1 end_log_pos 1398 CRC32 0x71e5e02f Xid = 24
\n139-COMMIT\/*!*\/;
\n140-# at 1398
\n141-#180720 10:11:44 server id 1 end_log_pos 1440 CRC32 0xe8d0e1f9 GTID 0-1-8 ddl
\n142-\/*!100001 SET @@session.gtid_seq_no=8*\/\/*!*\/;
\n143-# at 1440
\n144-#180720 10:11:44 server id 1 end_log_pos 1530 CRC32 0x7b772d2a Query thread_id=10 exec_time=0 error_code=0<\/p>\n
\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*\/;
\n\/*!40019 SET @@session.max_insert_delayed_threads=0*\/;
\n\/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*\/;
\nDELIMITER \/*!*\/;
\n#180720 10:06:09 server id 1 end_log_pos 1254 CRC32 0xff132128 Annotate_rows:
\n#Q> delete from test.t1
\n#180720 10:06:09 server id 1 end_log_pos 1299 CRC32 0x64d46f5b Table_map: `test`.`t1` mapped to number 22
\n# Number of rows: 7
\n#180720 10:06:09 server id 1 end_log_pos 1398 CRC32 0x71e5e02f Xid = 24
\nBEGIN\/*!*\/;
\n#180720 10:06:09 server id 1 end_log_pos 1367 CRC32 0x146f66d7 Write_rows: table id 22 flags: STMT_END_F
\n### INSERT INTO `test`.`t1`
\n### SET
\n### @1=7
\n### INSERT INTO `test`.`t1`
\n### SET
\n### @1=6
\n### INSERT INTO `test`.`t1`
\n### SET
\n### @1=5
\n### INSERT INTO `test`.`t1`
\n### SET
\n### @1=4
\n### INSERT INTO `test`.`t1`
\n### SET
\n### @1=3
\n### INSERT INTO `test`.`t1`
\n### SET
\n### @1=2
\n### INSERT INTO `test`.`t1`
\n### SET
\n### @1=1
\nCOMMIT
\n\/*!*\/;
\nDELIMITER ;
\n# End of log file
\nROLLBACK \/* added by mysqlbinlog *\/;
\n\/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*\/;
\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*\/;<\/p>\n
\n[root@localhost mysql]#<\/p>\n
\nQuery OK, 0 rows affected (0.000 sec)<\/p>\n
\n+——+
\n| id |
\n+——+
\n| 7 |
\n| 6 |
\n| 5 |
\n| 4 |
\n| 3 |
\n| 2 |
\n| 1 |
\n+——+
\n7 rows in set (0.000 sec)<\/p>\n
\n| id |
\n+——+
\n| 10 |
\n| 11 |
\n| 12 |
\n+——+
\n3 rows in set (0.000 sec)<\/p>\n