Shulou(Shulou.com)06/01 Report--

2017-03-27 09:25 read 178comments 0

Author: Ma GE Linux Operation and maintenance-Robin

The lesson of blood will not be described in detail. Go directly to the operation steps and recovery ideas (friendly reminder: any operation of the database should be backed up in advance). The following is the recovery process after Mysql data:

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) 2. 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 log

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 table4. 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

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.