Odzyskiwanie skasowanych danych z MySQL za pomocą binlogów

Co zrobić kiedy uruchomiliśmy przypadkowo złe zapytanie na bazie MySQL? Czy da się odzyskać skasowane dane z MySQLa? Jeżeli korzystamy z binlogów, które wykorzystujemy np. w replikacji MySQL, możemy coś poradzić.

Do odzyskania danych z MySQLa wykorzystamy binlogi działające w trybie ROW. Jeżeli skasowaliśmy jakiś rekord, a nie mieliśmy ich włączonych, niemożliwe będzie odzyskanie utraconych danych. W pliku /etc/my.cnf, powinniśmy mieć ustawione następujące parametry:

binlog-format = ROW
log-bin = /var/log/mysql/bin-log

Stworzymy testową bazę danych i uzupełnimy ją rekordami.

CREATE DATABASE `undelete`;
USE `undelete`;

CREATE TABLE `names` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`name` varchar(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `names` (name) VALUES ("Kamil"), ("Michal"), ("Adam"), ("Gerard"), ("Bartek");

Stworzyliśmy bazę danych undelete z tabelą names, w której znajduje się pięć rekordów. Dla pewności sprawdźmy jej zawartość:

mysql> select * from names;
+----+---------------------+--------+
| id | date                | name   |
+----+---------------------+--------+
|  1 | 2015-02-14 09:12:42 | Kamil  |
|  2 | 2015-02-14 09:12:42 | Michal |
|  3 | 2015-02-14 09:12:42 | Adam   |
|  4 | 2015-02-14 09:12:42 | Gerard |
|  5 | 2015-02-14 09:12:42 | Bartek |
+----+---------------------+--------+
5 rows in set (0.00 sec)

Skasujemy przypadkowe rekordy z bazy i postaramy się je odzyskać.

mysql> DELETE FROM `names` WHERE id >3;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from names;
+----+---------------------+--------+
| id | date                | name   |
+----+---------------------+--------+
|  1 | 2015-02-14 09:12:42 | Kamil  |
|  2 | 2015-02-14 09:12:42 | Michal |
|  3 | 2015-02-14 09:12:42 | Adam   |
+----+---------------------+--------+
3 rows in set (0.00 sec)

Jak widać powyżej, udało nam się skasować dwa rekordy. Informacja ta powinna zostać zapisana w binlogu, z którego odzyskamy dane. W naszym przypadku jest to plik bin-log.000001.

mysql> show binlog events in 'bin-log.000001';
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| bin-log.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.22-log, Binlog ver: 4 |
| bin-log.000001 | 120 | Query       |         1 |         196 | BEGIN                                 |
| bin-log.000001 | 196 | Table_map   |         1 |         253 | table_id: 73 (undelete.names)         |
| bin-log.000001 | 253 | Delete_rows |         1 |         320 | table_id: 73 flags: STMT_END_F        |
| bin-log.000001 | 320 | Xid         |         1 |         351 | COMMIT /* xid=78 */                   |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+

Odzyskiwanie za pomocą mysqlbinlog

Operację odzyskiwania danych, możemy przeprowadzić ręcznie za pomocą polecenia mysqlbinlog. Pozwala ono na podejrzenie danych, jakie zostały zapisane w logach binarnych.

mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=196 /var/log/mysql/bin-log.000001
/*!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 /*!*/;
# at 196
#150215  9:36:32 server id 1  end_log_pos 253 CRC32 0x6bedb0e7 	Table_map: `undelete`.`names` mapped to number 73
# at 253
#150215  9:36:32 server id 1  end_log_pos 320 CRC32 0x1bc278f9 	Delete_rows: table id 73 flags: STMT_END_F
### DELETE FROM `undelete`.`names`
### WHERE
###   @1=4
###   @2=1423992931
###   @3='Gerard'
### DELETE FROM `undelete`.`names`
### WHERE
###   @1=5
###   @2=1423992931
###   @3='Bartek'
# at 320
#150215  9:36:32 server id 1  end_log_pos 351 CRC32 0xee833dd0 	Xid = 78
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Jeżeli danych nie ma dużo, możemy przenieść je do bazy ręcznie. W przypadku dużej ilości, możemy pokusić się o skorzystanie ze skryptu jakim jest MyUndelete.

Odzyskiwanie za pomocą MyUndelete

Skrypt pobieramy z GitHuba za pomocą polecenia git clone https://github.com/lefred/MyUndelete.git. Musimy też uzupełnić plik vim ~/.my.cnf, z danymi do logowania do bazy danych.

W naszym przypadku kasowanie danych miało miejsce pomiędzy pozycjami w binlogu 120 a 351. Wydajemy zatem odpowiednie polecenie w nadziei, że dane się odzyskają:

./MyUndelete.py -b /var/log/mysql/bin-log.000001 -s 120 -e 351

*** WARNING *** USE WITH CARE ****

Binlog file is  /var/log/mysql/bin-log.000001
Start Position file is  120
End Postision file is  351
Event type (' ') is a delete v2
Ready to revert the statement ? [y/n]
y
Done... I hope it worked ;)

Możemy teraz sprawdzić wyniki w bazie. Jak widać poniżej, dane zostały przywrócone.

mysql> use undelete;
Database changed
mysql> select * from names;
+----+---------------------+--------+
| id | date                | name   |
+----+---------------------+--------+
|  1 | 2015-02-15 09:35:31 | Kamil  |
|  2 | 2015-02-15 09:35:31 | Michal |
|  3 | 2015-02-15 09:35:31 | Adam   |
|  4 | 2015-02-15 09:35:31 | Gerard |
|  5 | 2015-02-15 09:35:31 | Bartek |
+----+---------------------+--------+
5 rows in set (0.00 sec)

Skrypt ten pozwala również na cofnięcie danych w przypadku wykonania na bazie błędnych poleceń INSERT oraz UPDATE.