Learn The Best Way To Recover Deleted MySQL Data

Accidentally deleting data from a MySQL database can be a stressful experience. Fortunately, if you have a regular backup strategy and binary logging enabled, you can effectively recover the lost data. In this blog post, we will walk through the steps to recover deleted data using the last backup and binary logs.

While we can perform this recovery process on the production server, it will impact production. Therefore, it is better to test the recovery on a new instance first. Once we validate that the recovery is accurate, we can apply it to the production environment.

What is a Backup?

A backup is a copy of data from a database or file system used to restore and recover data in case of loss, corruption, or disasters. Backups are crucial for data protection and continuity, allowing recovery from scenarios such as accidental deletion, hardware failure, software errors, and other unforeseen events.

Ensure You Have the Necessary Backups and Logs

Make sure you have the most recent backup of your database. Backups can be either logical (e.g., using mysqldump) or physical (e.g., Percona XtraBackup). The restore process will vary based on the backup type. In this case, we are using a full dump created with mysqldump, so we will use the corresponding restore process.

Restoring the Last Backup

To restore the last backup, load the backup file into the database. For example, if the backup file is named fullbackup.sql, you can restore it using the following command:

Command :

mysql -u test -p < fullbackup.sql

Logs :

[root@genex-monitoring ec2-user]# mysql -u test -p < fullbackup.sql
Enter password:
[root@genex-monitoring ec2-user]#

Check the output of the restoration process for any errors or warnings. Ensure the mysql command completed successfully.

Once the backup restoration is complete, we need to recover the data between the full backup and the incident. For example, one of the transaction logs is provided below for reference, illustrating how we will recover the deleted data using the following tools.

mysql> use test_db

Database changed

mysql> create table binlog2sql(id int primary key auto_increment, name varchar(16), status enum(‘A’,’NA’), up_date datetime default current_timestamp);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into binlog2sql (name,status) values (‘sam’,’A’),(‘ram’,’A’),(‘kom’,’A’),(‘tom’,’A’),(‘lom’,’A’);

Query OK, 5 rows affected (0.00 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql>

mysql> select * from binlog2sql;
+—-+——+——–+———————+
| id | name | status | up_date |
+—-+——+——–+———————+
| 1 | sam | A | 2024-09-24 08:59:03 |
| 2 | ram | A | 2024-09-24 08:59:03 |
| 3 | kom | A | 2024-09-24 08:59:03 |
| 4 | tom | A | 2024-09-24 08:59:03 |
| 5 | lom | A | 2024-09-24 08:59:03 |
+—-+——+——–+———————+
5 rows in set (0.00 sec)

Accidentally deleted data

We were trying to delete data from one table but accidentally deleted it from the binlog2sql table.

mysql> delete from binlog2sql where id in (4,5);
Query OK, 2 row affected (0.00 sec)

mysql> select * from binlog2sql;
+—-+——+——–+———————+
| id | name | status | up_date |
+—-+——+——–+———————+
| 1 | sam | A | 2024-09-24 08:59:03 |
| 2 | ram | A | 2024-09-24 08:59:03 |
| 3 | kom | A | 2024-09-24 08:59:03 |
+—-+——+——–+———————+
3 rows in set (0.00 sec)

What is a Binary Log?

A binary log (binlog) in MySQL records all changes made to the database, such as inserts, updates, and deletes, including both data and schema changes. Binary logs are used for replication, backup, and recovery.

mysql> show global variables like ‘log_bin’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_bin | ON |
+—————+——-+
1 row in set (0.01 sec)

How to Decode Binary Logs

To decode binary logs, you use the mysqlbinlog utility. This tool reads binary log files and converts them into a readable format, showing the SQL statements and other details such as timestamps and user information.

Command :

mysqlbinlog –base64-output=decode-rows -vv /var/lib/mysql/mysql-bin.000819 –start-position=569411343 –stop-position=585003464

at 569411343

240924 09:10:36 server id 218  end_log_pos 569411404 CRC32 0x279f1189  Table_map: test.binlog2sql mapped to number 47394

at 569411404

240924 18:10:36 server id 218  end_log_pos 569411514 CRC32 0x15b4357f  Write_rows: table id 47394 flags: STMT_END_F

INSERT INTO test.binlog2sql

SET

  @1=1 /* INT meta=0 nullable=0 is_null=0 */

  @2=’sam’ /* VARSTRING(16) meta=16 nullable=1 is_null=0 */

  @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */

  @4=’2024-09-24 08:59:03′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

INSERT INTO test.binlog2sql

SET

  @1=2 /* INT meta=0 nullable=0 is_null=0 */

  @2=’ram’ /* VARSTRING(16) meta=16 nullable=1 is_null=0 */

  @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */

  @4=’2024-09-24 08:59:03′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

INSERT INTO test.binlog2sql

SET

  @1=3 /* INT meta=0 nullable=0 is_null=0 */

  @2=’kom’ /* VARSTRING(16) meta=16 nullable=1 is_null=0 */

  @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */

  @4=’2024-09-24 08:59:03′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

INSERT INTO test.binlog2sql

SET

  @1=4 /* INT meta=0 nullable=0 is_null=0 */

  @2=’tom’ /* VARSTRING(16) meta=16 nullable=1 is_null=0 */

  @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */

  @4=’2024-09-24 08:59:03′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

INSERT INTO test.binlog2sql

SET

  @1=5 /* INT meta=0 nullable=0 is_null=0 */

  @2=’lom’ /* VARSTRING(16) meta=16 nullable=1 is_null=0 */

  @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */

  @4=’2024-09-24 08:59:03′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

BEGIN

/!/;

at 585003338

240924 09:29:36 server id 218  end_log_pos 585003399 CRC32 0xe07d9b09  Table_map: test.binlog2sql mapped to number 47394

at 585003399

240924 09:29:36 server id 218  end_log_pos 585003464 CRC32 0xd5c5fb9d  Delete_rows: table id 47394 flags: STMT_END_F

DELETE FROM test.binlog2sql

WHERE

  @1=4 /* INT meta=0 nullable=0 is_null=0 */

  @2=’tom’ /* VARSTRING(16) meta=16 nullable=1 is_null=0 */

  @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */

  @4=’2024-09-24 08:59:03′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

DELETE FROM test.binlog2sql

WHERE

  @1=5 /* INT meta=0 nullable=0 is_null=0 */

  @2=’lom’ /* VARSTRING(16) meta=16 nullable=1 is_null=0 */

  @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */

  @4=’2024-09-24 08:59:03′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

at 585003464

240924 09:29:36 server id 218  end_log_pos 585003495 CRC32 0x5ed67c37  Xid = 8087192154

COMMIT/!/;

Between the last full backup and the incident, binary logs can be used to recover any changes. We will use the binlog2sql tool to extract and recover data from the MySQL binary logs.

Introducing binlog2sql

binlog2sql is an open-source Python tool that parses MySQL binary logs and generates SQL statements. It is useful for reversing unwanted changes or auditing database activities.

Installation

To install binlog2sql, follow these steps:

1. Ensure that your MySQL configuration (my.cnf) includes the following settings:

[mysqld]
server-id = <random ID>
log-bin=on
binlog_format=row
binlog_row_image=full

2. Clone the binlog2sql repository:

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

Using binlog2sql

Run binlog2sql

Navigate to the binlog2sql directory and execute the script with appropriate options:

[root@ip-10-10-1-218 binlog2sql]# ./binlog2sql.py -utestuser -pTest@1234@@567 –start-file mysql-bin.000819  –start-position 585003338 –stop-position 585003495  | cut -f1 -d’#’

DELETE FROM `test`.`binlog2sql` WHERE `status`=’A’ AND `up_date`=’2024-09-24 08:59:03′ AND `id`=4 AND `name`=’tom’ LIMIT 1;

DELETE FROM `test`.`binlog2sql` WHERE `status`=’A’ AND `up_date`=’2024-09-24 08:59:03′ AND `id`=5 AND `name`=’lom’ LIMIT 1;

Flashback

The ability to rollback database operations by generating SQL statements that “undo” changes recorded in MySQL binary logs (binlogs). The binlog2sql tool can extract these binlogs and convert them into SQL statements, allowing you to roll back specific transactions or changes.

[root@ip-10-10-1-218 binlog2sql]# ./binlog2sql.py -utestuser -pTest@1234@@567 –start-file mysql-bin.000819  –start-position 585003338 –stop-position 585003495 –flashback | cut -f1 -d’#’ > insert_logs.sql

INSERT INTO `test`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES (‘A’, ‘2024-09-24 08:59:03’, 5, ‘lom’);

INSERT INTO `test`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES (‘A’, ‘2024-09-24 08:59:03’, 4, ‘tom’);

Applying the SQL Statements

We need to manually execute the above insert queries. However, if the recovery data is large, we can save it as a file and restore it in the database.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

mysql> source insert_logs.sql

INSERT INTO test.binlog2sql(status, up_date, id, name) VALUES (‘A’, ‘2024-09-24 08:59:03’, 5, ‘lom’)

Query OK, 1 row affected (0.01 sec)

---

INSERT INTO test.binlog2sql(status, up_date, id, name) VALUES (‘A’, ‘2024-09-24 08:59:03’, 4, ‘tom’)

Query OK, 1 row affected (0.00 sec)

Verify the Recovery

Check your database to ensure that the deleted data has been successfully restored. Query the database and verify that the recovered data is intact.

mysql> select * from test.binlog2sql;
+—-+——+——–+———————+
| id | name | status | up_date |
+—-+——+——–+———————+
| 1 | sam | A | 2024-09-24 08:59:03 |
| 2 | ram | A | 2024-09-24 08:59:03 |
| 3 | kom | A | 2024-09-24 08:59:03 |
| 4 | tom | A | 2024-09-24 08:59:03 |
| 5 | lom | A | 2024-09-24 08:59:03 |
+—-+——+——–+———————+
5 rows in set (0.00 sec)

Prevent Future Issues

To prevent similar issues in the future, consider implementing the following best practices:

• Regular Backups: Schedule regular backups and verify their integrity.
• Binary Logging: Ensure binary logging is always enabled.
• Access Controls: Implement strict access controls to prevent accidental deletions.
• Monitoring and Alerts: Set up monitoring and alerts to detect and respond to issues promptly.

Conclusion

Recovering accidentally deleted data in MySQL is possible if you have a recent backup and binary logging enabled. By following the steps outlined in this blog post, you can restore your data to its previous state and minimize downtime. Always ensure you have a robust backup and recovery strategy in place to protect your data against accidental deletions and other potential issues.