Introduction to the method of MySQL data recovery through binlog (code example)

This article brings you an introduction to the method of MySQL data recovery through binlog (code example). It has certain reference value. Friends in need can refer to it. I hope it will be useful to you. Helps.

By understanding the relevant configuration of the binlog log, you can simply master the data recovery operation of the database through binlog;

mysql log file

Any Mature software will have a mature log system. When a problem occurs with the software, these logs are a treasure trove for querying the source of the problem. Similarly, mysql is no exception. There will also be a series of logs recording the running status of mysql.

mysql mainly has the following types of logs:

• Error log: records error information during the operation of mysql

• General Query log: record the statements that mysql is running, including each sql of query, modification, update, etc.

• Slow query log: record the SQL statements that are time-consuming to query

• Binlog log: record data modification records, including table creation, data updates, etc.

These logs need to be configured in the my.cnf file, if you do not know mysql You can use the mysql command to find the configuration file path.

mysql --verbose --help|grep -A 1 'Default options’ #该命令会罗列出my.cnf 顺序查找的路径。

binlog log

binlog is the binary log, a binary log file that records all database update statements, including tables Updates and record updates, that is, data manipulation language (DML), binlog are mainly used for data recovery and configuration of master-slave replication;

• Data recovery: when the database is accidentally deleted or occurs When something indescribable occurs, the data to a certain point in time can be restored through binlog.

• Master-slave replication: When the database is updated, the master database records through binlog and notifies the slave database to update, thereby ensuring that the master-slave database data is consistent;

Mysql is divided into service layer modules and storage engine layer modules according to functions. The service layer is responsible for client connections, SQL statement processing optimization and other operations. The storage engine layer is responsible for data storage and query; binlog belongs to the service layer module. The log is engine independent, and all data changes of the data engine will be recorded in the binlog log. When the database crashes, if the InnoDB engine is used, the binlog log can also check the commit status of the InnoDB redo log.

binlog log activation

Log activation method:

1. Add configuration

log_bin=ON
log_bin_basename=/path/bin-log
log_bin_index=/path/bin-log.index

2. Just set the log-bin parameter

 log-bin=/path/bin-log

When the binlog log is turned on, mysql will create an .index file specified by log_bin_index and multiple binary log files. All binlog files used by mysql are recorded in order in the index. The binlog log will be suffixed with the specified name (or default value) plus an incremented number, ex: bin-log.000001. When the following three situations occur, the binlog log will be rebuilt:

The file size reaches the value of the max_binlog_size parameter
Execute the flush logs command
Restart the mysql service

binlog log format

By the value of the parameter binlog_format parameter, you can Set the binlog format. Optional values ​​include statement, row, mixed

  * statement 格式：记录数据库执行的原始 SQL 语句
  * row 格式：记录具体的行的修改，这个为目前默认值
  * mixed 格式：因为上边两种格式各有优缺点，所以就出现了 mixed 格式

binlog log viewing tool: mysqlbinlog

Because binlog is a binary file and cannot be treated like other files. , open it directly to view. However, mysql provides the binlog viewing tool mysqlbinlog, which can parse binary files. Of course, the results of log parsing in different formats are different;

1. statement 格式日志，执行 mysqlbinlog  /path/bin-log.000001，可以直接看到原始执行的 SQL 语句
2. row格式日志，则可读性没有那么好，但仍可通过参数使文档更加可读 mysqlbinlog -v  /path/bin-log.000001

mysqlbinlog two pairs of very important parameters

  1. --start-datetime  --stop-datetime 解析某一个时间段内的binlog；
  2. --start-position  --stop-position   解析在两个position之间的binlog；

Use binlog to restore data:

Using binlog to recover data is essentially to find all DML operations through binlog, remove erroneous SQL statements, and then go through the Long March Road again to recover the data;

Offline practical operation:

Create a data table and insert the initial value

    CREATE TABLE `users` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(255) DEFAULT NULL,
              `age` int(8) DEFAULT NULL,
              PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     INSERT INTO `users` (`id`, `name`, `age`)
        VALUES
            (null, '姓名一', 5);

Find the database and binlog position of the last full backup (ps: of course, it can also be restored through time). Here, the current status is used as the initial value of the backup.

    mysqldump -uroot -p T > /path/xxx.sql;   # 备份数据库
    show master status;   # 查看当前的position位置，此时值为154

Insert multiple records

  INSERT INTO `users` (`id`, `name`, `age`)
  VALUES
     (null, '姓名二', 13),
     (null, '姓名三', 14),
     (null, '姓名四', 15),
     (null, '姓名五', 16),
     (null, '姓名六', 17);

Made a misoperation, and inserted several pieces of data after the misoperation

update users set age = 5；
 INSERT INTO `users` (`id`, `name`, `age`)
 VALUES
    (null, '姓名七', 16),
    (null, '姓名八', 18);

Discovered After misoperation, perform data recovery. First, stop the external service of mysql and use the backup data to restore to the last data;

Use the mysqlbinlog command to analyze the binary file and find out

    误操作发生在position为706位置，且上次正常操作的结束位置在513
    在1152到结尾位置有正常执行的SQL执行

Through mysqlbinlog The command exports the executable SQL file from the binlog log and imports the data into mysql

 mysqlbinlog --start-position=154  --stop-position=513  bin-log.000001 > /path/bak.sql;
 mysql -uroot -p < /path/bak.sql;

Skip the wrong update statement, and then re-run the subsequent normal statements through the logic of step 7 to complete the data recovery work

Summary

No matter what time, a database crash will make people frown and upset. Binlog can be said to be a regretful medicine after database crash and data loss under various circumstances. This article simply conducts a data recovery experiment on the database through an offline environment. If there is something wrong, please give me some advice

This article ends here. For more knowledge about MySQL, you can pay attention to the MySQL Tutorial column on the php Chinese website! ! !

The above is the detailed content of Introduction to the method of MySQL data recovery through binlog (code example). For more information, please follow other related articles on the PHP Chinese website!