MySQL restores data through binlog

mysql log files

Any mature software will have a mature logging system. When problems occur in the software, these logs are a treasure trove for finding the source of the problem. Similarly, MySQL is no exception, and there will be a series of logs recording the running status of MySQL.

MySQL mainly has the following logs:

• Error log: records error information during mysql operation
• General query log: records the statements that MySQL is running, including each SQL statement of query, modification, update, etc.
• Slow query log: records the SQL statements that consume a lot of 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 path of the mysql configuration file, you can use the mysql command to find it.

mysql --verbose --help|grep -A 1 'Default options' #This command lists the paths that my.cnf searches in order.

binlog

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

Data recovery: When the database is accidentally deleted or something unspeakable happens, the data can be restored to a certain point in time through binlog. Master-slave replication: When a database is updated, the master database records it through binlog and notifies the slave database to update, thus ensuring the consistency of master and 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 operations such as client connection and SQL statement processing optimization, while 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 engine-independent. 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 verify the commit status of the InnoDB redo log.

Binlog log is turned on

How to enable logging:

1. Add configuration

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

2. Only set the log-bin parameter

 log-bin=/path/bin-log

When binlog is turned on, MySQL creates an .index file specified by log_bin_index and multiple binary log files. The index records all binlog files used by MySQL in sequence. The binlog log will be rebuilt with the specified name (or default value) plus an incrementing number as a suffix, e.g. bin-log.000001. The binlog log will be rebuilt when the following three situations occur:

When the file size reaches the value of the max_binlog_size parameter, execute the flush logs command to restart the MySQL service.

Binlog log format

The binlog format can be set by the value of the binlog_format parameter. The optional values ​​are statement, row, and mixed. * Statement format: records the original SQL statement 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 log viewing tool: mysqlbinlog

Because binlog is a binary file, it cannot be directly opened and viewed like other files. But MySQL provides a binlog viewing tool, mysqlbinlog, which can parse binary files. Of course, the parsing results of logs in different formats are different; 1. Statement format log, execute mysqlbinlog /path/bin-log.000001, you can directly see the original executed SQL statement 2. Row format log, the readability is not so good, but you can still make the document more readable through parameters mysqlbinlog -v /path/bin-log.000001

Two pairs of very important parameters of mysqlbinlog: 1. --start-datetime --stop-datetime parses binlog within a certain time period; 2. --start-position --stop-position parses binlog between two positions;

Using binlog to restore data

Using binlog to restore data is essentially to find all DML operations through binlog, remove the wrong SQL statements, and then go through the long march again to restore 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`)
     )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     INSERT INTO `users` (`id`, `name`, `age`)
        VALUES
            (null, 'Name 1', 5);

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

    mysqldump -uroot -p T > /path/xxx.sql; # Back up the database show master status; # Check the current position, which is 154.

3. Insert multiple records

  INSERT INTO `users` (`id`, `name`, `age`)
  VALUES
     (null, 'Name 2', 13),
     (null, 'Name 3', 14),
     (null, 'Name 4', 15),
     (null, 'Name Five', 16),
     (null, 'Name 6', 17);

4. Perform an incorrect operation and insert several more data after the incorrect operation

update users set age = 5;
 INSERT INTO `users` (`id`, `name`, `age`)
 VALUES
    (null, 'Name 7', 16),
    (null, 'Name 8', 18);

5. After discovering the wrong operation, perform data recovery. First, stop the external service of MySQL and use the backup data to restore to the last data;

6. Use the mysqlbinlog command to analyze the binary file and find that

    The error occurred at position 706, and the last normal operation ended at position 513.
    There is a normal SQL execution from 1152 to the end

7. Export the executable SQL file from the binlog log 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 incorrect 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 it is, a database crash is frustrating and upsetting. Binlog can be said to be a regret pill after database crash and data loss in various situations. This article simply conducts a data recovery experiment on the database through an offline environment. If there is anything wrong, please advise

The above is the details of MySQL data recovery through binlog. For more information about MySQL binlog data recovery, please pay attention to other related articles on 123WORDPRESS.COM!