Shulou(Shulou.com)05/31 Report--

This article shows you how to restore mistakenly deleted data in the MySQL database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Find binlog.

The prerequisite for data recovery is that the binlog log of Mysql is enabled. If the binlog log is not enabled, please ignore this document. Whether binlog log is enabled or not, you can view the Mysql configuration file. The log location is usually in the / var/lib/mysql directory or the date directory where the compiler is installed. You can also log in to Mysql and use the command to view it.

# 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 and cut binlog logs under the Mysql command line. When the binlog is cut and checked 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)

two。 Find the wrong statement in binlog

The time of execution of the error statement can be found in the binlog log, and the binlog log before and after the recovery of the error statement is 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 'incorrect sql statement'

3. Restore binlog Lo

You can specify the start and end times by directly restoring the binlog log to a sql script through the mysqlbinlog command. If you generate multiple binlog logs from the last backup (it is recommended that you refresh the binlog logs at the same time) to the recovery time, export them to sql in the order from small to large, and then import them into the database.

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

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

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

4. Restore to database

When recovering 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 the above content is how to recover erroneous deleted data in MySQL database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.