Recovery of deleted data from MySQL using binlogs

What to do when we accidentally run a bad query based on MySQL? Is it possible to recover deleted data from MySQL? If we use binlogs, which we use for example in MySQL replication, we can do something about it.

To recover data from MySQL we will use bingoes running in ROW mode. If we have deleted a record and we didn’t have it enabled, it will be impossible to recover the lost data. In /etc/my.cnf, the following parameters should be set:

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

We will create a test database and complete it with records.

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");

We have created an undelete database with a names table, which contains five records. To be sure, let’s check its contents:

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)

We will delete random records from the database and try to recover them.

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)

As you can see above, we managed to delete two records. This information should be saved in the bingo from which we will retrieve the data. In our case it is a bin-log.000001 file.

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 */                   |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+

Recovery with mysqlbinlog

Data recovery operation can be performed manually using the mysqlbinlog command. It allows us to suspect the data stored in binary logs.

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*/;

If there is not much data, we can move them to the database manually. In case of a large amount, we can try to use a script such as MyUndelete.

Recovery with MyUndelete

We download the script from GitHub using the git clone https://github.com/lefred/MyUndelete.git. We also need to complete the file vim ~/.my.cnf, with data for logging into the database.

In our case, the deletion of data took place between positions in binog 120 and 351. Therefore, we issue an appropriate command in the hope that the data will be recovered:

./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 ;)

We can now check the results in the database. As you can see below, the data has been restored.

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)

This script also allows to undo data in case of execution based on erroneous INSERT and UPDATE commands.