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 */ |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
Spis treści
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.
Check other blog posts
See all blog posts30 December 2020
Things your hosting won’t do for you
Read moreMany clients of hosting companies are mistakenly convinced that a hosting provider will solve their problems and take care of their crucial issues, while maintaining their website hosted on their servers. (Un)fortunately, that doesn’t work that way. What are the things your hosting won’t do for you? It won’t do the dishes, design a website,…
1 December 2020
How to transfer WordPress to another server?
Read moreMy mind’s made up. You’ve had enough of the silly hosting or cosmic costs of its maintenance and finally you decide to move the site you’ve placed on WordPress. How to move WordPress to another server? What do you need to remember and how should you prepare for it? Migration of WordPress to another server…
7 October 2020
Google Workspace – the best business mail you can have
Read moreGoogle Workspace is probably the best business mail for your work. Great anti-spam filters, full control over your data, the fact you can choose a country where your data are stores, a calendar, Google Meets… these are only a fraction of what G Suite offers. Take a look at why you should consider switching to Google…