How to retrieve accidentally deleted data in mysql

Mysql method to retrieve accidentally deleted data: first find the wrong statement in the binlog; then restore the binlog log; finally restore to the database, the code is [# mysql -uroot -p -f ids < data.sql 】.

Mysql method to retrieve accidentally deleted data:

1. Find the binlog

The prerequisite for data recovery is that Mysql's binlog must be enabled. If the binlog is not enabled, please ignore this document. Whether the binlog log is enabled can check the Mysql configuration file. The log location is generally in the /var/lib/mysql directory or the date directory where it was compiled and installed. You can also log in to Mysql and use commands to view it.

# cat /etc/my.cnf
log_bin=mysql-bin
# mysql -uroot -p
Enter password:
mysql> show variables like'log_bin%';
+---------------------------------+--------------------------------------------------+
| Variable_name                   | Value                                            |
+---------------------------------+--------------------------------------------------+
| log_bin                         | ON                                               |
| log_bin_basename                | /home/programs/mysql-5.6.26/data/mysql-bin       |
| log_bin_index                   | /home/programs/mysql-5.6.26/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                              |
| log_bin_use_v1_row_events       | OFF                                              |
+---------------------------------+--------------------------------------------------+
5 rows in set (0.00 sec)
# ll /home/programs/mysql-5.6.26/data/mysql-bin*
-rw-rw---- 1 mysql mysql 343629748 Oct 13 22:09 /home/programs/mysql-5.6.26/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql        19 Sep 23 17:11 /home/programs/mysql-5.6.26/data/mysql-bin.index

If there are multiple binlog logs, you can also view the current binlog and cut the binlog log under the Mysql command line. After the binlog is cut and viewed again, you will see that the new log is written to the new binlog file.

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 343629748 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

2. Find the wrong statement in the binlog

You can find the time point when the wrong statement was executed in the binlog log, and restore the binlog logs before and after the wrong statement as sql. You can also skip this step and directly restore the entire binlog log to sql, then open the sql file and delete the error statement.

# sudo mysqlbinlog --base64-output=DECODE-ROWS -v -d ids mysql-bin.000001 | grep --ignore-case -A3 -B4 '错误的sql语句'

3. Restore the binlog log

Directly restore the binlog log as a sql script through the mysqlbinlog command, and you can specify the start and end time. If multiple binlog logs are generated from the last backup (it is recommended to refresh the binlog log during backup) to the recovery time, export them to SQL in order from small to large and then import them into the database in sequence.

# sudo mysqlbinlog --base64-output=DECODE-ROWS -v -d ids --start-datetime '2016-10-11 15:22:53' mysql-bin.000001 > /home/stack/data.sql

In the above command, use -d ids to specify the database to be restored. If you want to restore table-level data, export it to sql and then filter grep.

# more data.sql | grep --ignore-case -E 'insert|update|delete' | grep table

4. Restore to the database

When restoring data, there may be duplicate data errors. It is recommended to use the -f parameter to ignore it.

# mysql -uroot -p -f ids < data.sql

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of How to retrieve accidentally deleted data in mysql. For more information, please follow other related articles on the PHP Chinese website!