Recovery schemes and preventive measures for mysql data being deleted by mistake

Preface

Misremoval of data is a serious database management problem, but through reasonable backup strategies and the use of appropriate recovery tools, the risk of data loss can be effectively reduced.

Several common data recovery methods include the use of backups, binary logs, tablespace files, and third-party tools

1. Use backup to restore

Backup is the most common method of data recovery. By backing up the database regularly, you can quickly restore to the most recent backup state when data is lost.

Stop MySQL service
```
sudo systemctl stop mysql
```
Restore backup
```
mysql -u root -p < /path/to/
```
Restart MySQL service
```
sudo systemctl start mysql
```

advantage:

Easy to do: Just execute a few simple commands to complete the recovery.
High reliability: As long as the backup file is complete and not corrupted, it can be fully restored to the state it was at the time of backup.

shortcoming:

Data loss: You can only restore to the last backup point, and the subsequent data cannot be restored.
Dependency backup strategy: There is a regular backup plan, otherwise there may be no backup files available.

2. Use Binary Log

The binary log records all changes made to the database. With these logs, you can roll back to a specific point in time or replay certain operations.

Query binlog on status

First of all, make sure that binlog is enabled, otherwise the data will definitely not be restored.
```
SHOW VARIABLES LIKE 'log_bin';
```
```
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)
```
If the value of log_bin is ON, it means that binlog has been turned on; if the value is OFF, it means that binlog is not turned on
Query binlog mode
```
SHOW VARIABLES LIKE 'binlog_format';
```
A result set will be returned with the current binlog format. Possible values are:
- ROW: Indicates the use of row-based replication, which is the recommended setting because it provides better data consistency.
- STATEMENT: Indicates the use of statement-based replication, in which some data may be lost because it only records the executed SQL statements.
- MIXED: Indicates mixed-based replication, in which MySQL will automatically switch line mode and statement mode as needed.

Query the binlog file currently used

mysql> show master status\G
*************************** 1. row ***************************
 File: mysql-bin.000217
 Position: 668127868
 Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 29dc2bf9-f657-11ee-b369-08c0eb829a3c:1-291852745,
744ca9cd-5f86-11ef-98d6-0c42a131d16f:1-5374311
1 row in set (0.00 sec)

Find binary log files: Find binary log files containing delete operations

Parsing binlog files into readable sql files through mysqlbinlog tool
```
mysqlbinlog --start-datetime="YYYY-MM-DD HH:MM:SS" --stop-datetime="YYYY-MM-DD HH:MM:SS" /path/to/binlog | mysql -u root -p
```
Where: –start-datetime and --stop-datetime are used to specify the time range to accurately locate the state before the error deletion operation.
Replay data

The parsed file is an SQL script file. You can execute SQL scripts in the usual way.
```
mysql -uroot -proot < 
```
advantage:
- Accurate recovery: You can recover according to specific time points to reduce data loss.
- High flexibility: suitable for a variety of complex recovery scenarios.
shortcoming:
- High complexity: You need to understand the structure and usage of binary logs.
- Depend on log integrity: If the binary log file is incomplete or corrupt, it may not be restored successfully.

Recover deleted tables through MySQL's binary log (binlog).

# View binlog enabledSHOW VARIABLES LIKE 'log_bin';
If the return value is ON，It is enabled。

# Find binlog fileSHOW BINARY LOGS;
# Use mysqlbinlog tool to read binlog filesmysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-01 23:59:59" binlog.000001

# Find the operation of deleting table# Use grep to filter out the DROP TABLE statement:mysqlbinlog binlog.000001 | grep 'DROP TABLE'

# Replay and delete operation before#After confirming the status before deleting the table, extract the CREATE TABLE statement before deleting, and then manually recreate the table.
# Recover data# If you find the insert data operation in binlog, you can restore the data through the corresponding SQL statement.
#Precautions#When doing this, make sure to stop writing to the database to avoid data inconsistencies.#It is best to back up the current database status before operation, just in case.

3. Use InnoDB tablespace recovery

For the InnoDB storage engine, recovery can be performed by copying the tablespace file (.ibd file). This method is suitable for recovery at the physical file level.

Stop MySQL service
```
sudo systemctl stop mysql
```
Copy ibd file: Copy the ibd file that was accidentally deleted from the backup or the old version
Modify the table structure: Modify the table structure as needed to make it consistent with the current database.
Start MySQL service
```
sudo systemctl start mysql
```
Import tablespace
```
ALTER TABLE your_table_name IMPORT TABLESPACE;
```
advantage:
- Quick Recovery: works on large data sets because there is no need to reload data from the entire table.
- Physical level recovery: tablespace files can be recovered directly from the file system.
shortcoming:
- Higher risk: If the table structure is inconsistent, it may cause data corruption.
- Dependence file system: It requires access to the underlying file system, and the operation is relatively complicated.

4. Use third-party tools

Some third-party tools can help recover accidentally deleted data

Download and install the tool
Run the tool: Follow the instructions for using the tool

advantage:
- Powerful: More recovery options and advanced features are provided.
- User-friendly: There is usually better user interface and documentation support.
shortcoming:
- Cost issue: Some tools may be paid.
- Learning curve: It requires a certain amount of learning and configuration time.

Preventive measures

In order to avoid the trouble caused by accidental deletion of data, the following precautions are recommended:

Regular backup
Set up an automatic backup plan to ensure that the latest backup is available.
Permission control
Limit the permissions of database users to avoid unnecessary misoperations.
Audit log
Enable audit logs to record all DDL and DML operations.
Test environment
Verify in the test environment before performing any operations in the production environment.

Through the above methods, the accidentally deleted data can be effectively restored and the losses can be reduced.

Summarize

This is the article about the recovery plan and preventive measures of mysql data being deleted by mistake. For more related content of mysql data being deleted by mistake, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!