Explanation: How to recover the data mistakenly deleted from MySQL database

2017-03-27 09:25 Read 178 Reviews 0

Marco Linux operation and maintenance-robin

The lesson of blood, the incident is not detailed. Direct on-line operation and recovery of ideas (friendly tip: Any database operation should be done in advance backup ), the following is the recovery process after MySQL data:

1. Find Binlog

directory or compiled date the directory. You can also log in to MySQL with the command view.

# cat /etc/my.cnflog_bin=mysql-bin# mysql -uroot -penter 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 you have multiple Binlog logs, you can also view the current Binlog, cut Binlog logs on the MySQL command line. After the cut is complete binlog see the new log written to the new Binlog file again.

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 Binlog

You can find the point in the log where the error statement was executed, and restore the Binlog log before and after the error statement to SQL, respectively. You can also skip this step, 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 ' wrong SQL statement '

3. Recovering Binlog Logs

mysqlbinlogYou can specify the start and end times by directly recovering the Binlog log as a SQL script by command. If you generate multiple Binlog logs from the last backup (the recommended backup, while refreshing the Binlog log) up to the recovery time, export them to SQL in order from small to large and import them into the database separately.

# sudo mysqlbinlog--base64-output=decode-rows-v-D IDs--start-datetime ' 2016-10-11 15:22:53 ' mysql-bin.000001 >/hom E/stack/data.sql

　

The above command -d ids specifies to recover the database, if you want to restore table-level data, export to SQL and then filter grep.

# More Data.sql | grep--ignore-case-e ' Insert|update|delete ' | grep table

4. Recovering to a database

When recovering data, there may be duplicate data error, it is recommended to -f ignore the parameters.

# mysql-uroot-p-F IDs < Data.sql

Explanation: How to recover the data mistakenly deleted from MySQL database