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.
Let’s create data with faulty DELETE command inside 🙂 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
MariaDB [test]> create table t1(id int); Query OK, 0 rows affected (0.008 sec) MariaDB [test]> insert into t1 values(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.003 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [test]> insert into t1 values(6); Query OK, 1 row affected (0.004 sec) MariaDB [test]> insert into t1 values(7); Query OK, 1 row affected (0.001 sec) MariaDB [test]> delete from test.t1; Query OK, 7 rows affected (0.002 sec) MariaDB [test]> create table t2(id int); insert into t2 values(10),(11),(12); Query OK, 0 rows affected (0.034 sec) Query OK, 3 rows affected (0.001 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> exit |
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.
1) Locating where is our DELETE command residing.
As long as we are storing binary (transactional) logs,we 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:
|
1 |
mysqlbinlog -v --base64-output=DECODE-ROWS localhost-bin.000002 > mysql-bin.000002.sql |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[root@localhost mysql]# tail -15 mysql-bin.000002.sql ### INSERT INTO `test`.`t2` ### SET ### @1=11 ### INSERT INTO `test`.`t2` ### SET ### @1=12 # Number of rows: 3 # at 1723 #180720 10:11:44 server id 1 end_log_pos 1754 CRC32 0x9238cb53 Xid = 26 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
[root@localhost mysql]# grep -nr -B 10 -A 30 Delete_rows mysql-bin.000002.sql 104-#180720 10:06:09 server id 1 end_log_pos 1212 CRC32 0x75c44f0c GTID 0-1-7 trans 105-/*!100001 SET @@session.gtid_seq_no=7*//*!*/; 106-BEGIN 107-/*!*/; 108-# at 1212 109-# at 1254 110-#180720 10:06:09 server id 1 end_log_pos 1254 CRC32 0xff132128 Annotate_rows: 111-#Q> delete from test.t1 112-#180720 10:06:09 server id 1 end_log_pos 1299 CRC32 0x64d46f5b Table_map: `test`.`t1` mapped to number 22 113-# at 1299 114:#180720 10:06:09 server id 1 end_log_pos 1367 CRC32 0x146f66d7 Delete_rows: table id 22 flags: STMT_END_F 115-### DELETE FROM `test`.`t1` 116-### WHERE 117-### @1=1 118-### DELETE FROM `test`.`t1` 119-### WHERE 120-### @1=2 121-### DELETE FROM `test`.`t1` 122-### WHERE 123-### @1=3 124-### DELETE FROM `test`.`t1` 125-### WHERE 126-### @1=4 127-### DELETE FROM `test`.`t1` 128-### WHERE 129-### @1=5 130-### DELETE FROM `test`.`t1` 131-### WHERE 132-### @1=6 133-### DELETE FROM `test`.`t1` 134-### WHERE 135-### @1=7 136-# Number of rows: 7 137-# at 1367 138-#180720 10:06:09 server id 1 end_log_pos 1398 CRC32 0x71e5e02f Xid = 24 139-COMMIT/*!*/; 140-# at 1398 141-#180720 10:11:44 server id 1 end_log_pos 1440 CRC32 0xe8d0e1f9 GTID 0-1-8 ddl 142-/*!100001 SET @@session.gtid_seq_no=8*//*!*/; 143-# at 1440 144-#180720 10:11:44 server id 1 end_log_pos 1530 CRC32 0x7b772d2a Query thread_id=10 exec_time=0 error_code=0 |
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:
2) Preparing flashback binlog fragment
Looks great, we can prepare again those lines, this time with clause –flashback, opposite operation will be generated:
|
1 |
mysqlbinlog --flashback -v --base64-output=DECODE-ROWS --start-position=1212 --stop-position=1398 localhost-bin.000002 > localhost-bin.000002_flash.sql |
There is no surprise how resulted file will be looking:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
[root@localhost mysql]# cat localhost-bin.000002_flash.sql /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; #180720 10:06:09 server id 1 end_log_pos 1254 CRC32 0xff132128 Annotate_rows: #Q> delete from test.t1 #180720 10:06:09 server id 1 end_log_pos 1299 CRC32 0x64d46f5b Table_map: `test`.`t1` mapped to number 22 # Number of rows: 7 #180720 10:06:09 server id 1 end_log_pos 1398 CRC32 0x71e5e02f Xid = 24 BEGIN/*!*/; #180720 10:06:09 server id 1 end_log_pos 1367 CRC32 0x146f66d7 Write_rows: table id 22 flags: STMT_END_F ### INSERT INTO `test`.`t1` ### SET ### @1=7 ### INSERT INTO `test`.`t1` ### SET ### @1=6 ### INSERT INTO `test`.`t1` ### SET ### @1=5 ### INSERT INTO `test`.`t1` ### SET ### @1=4 ### INSERT INTO `test`.`t1` ### SET ### @1=3 ### INSERT INTO `test`.`t1` ### SET ### @1=2 ### INSERT INTO `test`.`t1` ### SET ### @1=1 COMMIT /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; |
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:
|
1 2 |
[root@localhost mysql]# mysqlbinlog --flashback --start-position=1212 --stop-position=1398 localhost-bin.000002 > localhost-bin.000002_flash.bin [root@localhost mysql]# |
3) Propagating changes into database + verification
From mariadb client we will process this „flash“:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
MariaDB [(none)]> source localhost-bin.000002_flash.bin Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) |
And verification is really easy:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
MariaDB [(none)]> select * from test.t1; select * from test.t2; +------+ | id | +------+ | 7 | | 6 | | 5 | | 4 | | 3 | | 2 | | 1 | +------+ 7 rows in set (0.000 sec) +------+ | id | +------+ | 10 | | 11 | | 12 | +------+ 3 rows in set (0.000 sec) |
Flashback operation performed as expected. Newly created table t2 is intacted and „bad“ DELETE operation was successfully replaced by original values (INSERT INTO).
-a-