MySQL restores data through binlog



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

MySQL log file

Any mature software will have a mature log system. When there is a problem with the software, these logs are the treasure house to query the source of the problem. Similarly, MySQL is no exception. There will be a series of logs to record the running status of MySQL.

MySQL mainly includes the following logs:

  • Error log: Records error information during MySQL operation
  • General query log: records the running statements of MySQL, including each SQL query, modification, update, etc
  • Slow query log: Records SQL statements that take a long time to query
  • Binlog log: records data modification records, including table creation, data update, etc

These logs need to be configured in the my.cnf file. If you don’t know the MySQL configuration file path, you can use the MySQL command to find them,

MySQL -- verbose -- help|grep - a 1 'default options' # this command will list the paths found in my.cnf order.

Binlog log

Binlog is binary log, which records all database update statements, including table update and record update, that is, data manipulation language (DML). Binlog is mainly used for data recovery and configuration of master-slave replication;

  • Data recovery: when the database is deleted by mistake or something indescribable happens, the data can be recovered to a certain point in time through binlog.
  • Master-slave replication: when the database is updated, the master database records and notifies the slave database to update through binlog, so as to ensure the consistency of the master-slave database data;

MySQL is divided into service layer module and storage engine layer module according to its functions. The service layer is responsible for client connection, SQL statement processing optimization and other operations, and the storage engine layer is responsible for data storage and query; Binlog belongs to the log of the service layer module, that is, it is independent of the engine. The data changes of all data engines will be recorded in the binlog log. When the database crashes, if the InnoDB engine is used, the binlog log can also verify the commitment of the redo log of InnoDB.

Binlog log on

Log opening method:

1. Add configuration


2. Just set the log bin parameter


When binlog log is enabled, MySQL will create a log_ bin_ Index specifies the. Index file and multiple binary log files. Index records all binlog files used by MySQL in order. The binlog log will take the specified name (or default value) plus the self incrementing number as the suffix, ex: bin-log.000001. When the following three situations occur, the binlog log will be rebuilt:

File size reached max_ binlog_ The value of the size parameter
Execute the flush logs command
Restart MySQL service

Binlog log format

Through the parameter binlog_ The format parameter can be used to set the format of binlog. The optional values are statement, row and mixed

  • Statement format: records the original SQL statements executed by the database
  • Row format: records the modification of specific rows. This is the current default value
  • Mixed format: because the above two formats have their own advantages and disadvantages, the mixed format appears

Binlog viewing tool: mysqlbinlog

Because binlog is a binary file, it cannot be opened and viewed directly like other files. However, MySQL provides a binlog viewing tool, mysqlbinlog, which can parse binary files. Of course, the log parsing results of different formats are different;

  1. Statement format log, execute mysqlbinlog / path / bin log.00000 1, and you can directly see the original executed SQL statement
  2. If the log is in row format, the readability is not so good, but the document can still be made more readable through parameters mysqlbinlog – V / path / bin-log.000001

Mysqlbinlog two pairs of very important parameters

  1. –Start datetime — stop datetime parses binlog in a certain time period;
  2. –Start position — stop position resolves the binlog between two positions;

To recover data using binlog:

Using binlog to recover data is essentially to find all DML operations through binlog, remove the wrong SQL statements, and then go through the long march again to recover the data;

Offline practice:

  1. Create a data table and insert initial values

    CREATE TABLE `users` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(255) DEFAULT NULL,
              `age` int(8) DEFAULT NULL,
              PRIMARY KEY (`id`)
     INSERT INTO `users` (`id`, `name`, `age`)
            (null, 'name one', 5);
  2. Find the position of the last full backup database and binlog (PS: of course, it can also be restored over time). Here, the current status is taken as the initial value of backup,

    mysqldump -uroot -p T > /path/xxx.sql;   #  Backup database
    show master status;   #  View the current position, and the value is 154
  3. Insert multiple records

    INSERT INTO `users` (`id`, `name`, `age`)
     (null, 'name 2', 13),
     (null, 'name 3', 14),
     (null, 'name four', 15),
     (null, 'name five', 16),
     (null, 'name 6', 17);
  4. Misoperation, and several pieces of data are inserted after misoperation

    update users set age = 5;
    INSERT INTO `users` (`id`, `name`, `age`)
    (null, 'name seven', 16),
    (null, 'name 8', 18);
  5. After the misoperation is found, restore the data. First stop the external service of MySQL and use the backup data to restore the last data;

  6. Through the analysis of binary files with mysqlbinlog command, it is found that

    The misoperation occurs at position 706, and the end position of the last normal operation is 513
    There are normally executed SQL executions from 1152 to the end
  7. Export the executable SQL file from the binlog through the mysqlbinlog command, and import the data into mysql

    mysqlbinlog --start-position=154  --stop-position=513  bin-log.000001 > /path/bak.sql;
    mysql -uroot -p < /path/bak.sql;
  8. Skip the wrong UPDATE statement, and then run the subsequent normal statements again through the logic in step 7 to complete the data recovery


No matter when the database crashes, it will make people frown and upset. Binlog can be said to be a regret pill after database crash and data loss under various circumstances. This paper simply conducted a data recovery experiment on the database through the offline environment. If there is anything wrong, please give advice

Reference articles

This work adoptsCC agreement, reprint must indicate the author and the link to this article