This section describes how to use binlog logs to restore database data.

Mysql log file

Any mature software will have a mature log system, and when a problem occurs in the software, these logs are a treasure trove of sources of the problem. Similarly, mysql is no exception. There are also a series of logs to record the running status of mysql.

Mysql has the following types of logs:

  • Error log: records error information about the mysql running
  • General query log: records the running statements of mysql, including each SQL query, modification, and update
  • Slow query log: records THE SQL statements that take time to query
  • Binlog: Records data modification records, including table creation and data update

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

Mysql -- verbose -- help | grep - A 1 'Default options' #. This command will list out my CNF sequential search path.Copy the code

Binlog log

The binary log is a binary log file that records all database updates, including table updates and record updates. The binary log is mainly used for data recovery and configuration of master/slave replication.

Data recovery: When the database is deleted or something undescribable occurs, the binlog can be used to restore the data at a certain point in time. Master/slave replication: When a database is updated, the master database records and notifies the slave database of the update through binlog to ensure data consistency between the master and slave databases.

Mysql is divided into service layer module and storage engine layer module according to functions. The service layer is responsible for client connection and SQL statement processing optimization, and the storage engine layer is responsible for data storage and query. Binlog Is a service layer module log, that is, engine independent. All data changes of the data engine are recorded in binlog. Binlog can also verify the commit status of InnoDB’s redo log in the event of a database crash if InnoDB is using the InnoDB engine.

Binlog Log is enabled

Log enabling mode:

1. Add the configuration

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

2. Set only log-bin

 log-bin=/path/bin-log
Copy the code

When binlog is enabled, mysql creates a.index file specified by log_bin_index and multiple binary log files. The index file records all binlog files used by mysql in sequence. The binlog file is suffixed with the specified name (or default value) and an incremented number ex: bin-log.000001. The binlog file is rebuilt when:

The file size reaches max_binlog_size. Run flush logs to restart the mysql service

Binlog Log format

The value of the binlog_format parameter can be used to set the format of the binlog. Possible values are Statement, row, or mixed * statement format: Records the original SQL statements executed by the database. * ROW format: Record changes to specific lines, which is currently the default * mixed format: Because the above two formats have advantages and disadvantages, mixed format appears

Binlog Log viewing tool: mysqlbinlog

Because binlog is a binary file, it cannot be directly opened and viewed like other files. However, mysql provides a binlog viewer called mysqlBinlog, which can parse binary files. Of course, different log formats have different results; Mysql > mysqlbinlog /path/bin-log.000001 SQL statement (mysqlbinlog /path/bin-log.000001) Mysqlbinlog -v /path/bin-log.000001 can still be used to make the document more readable

–start-datetime –stop-datetime Parse the binlog for a specified period of time; –start-position –stop-position Parses the binlog between two positions;

Restore data using binlog

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 to restore data.

Offline field

1. Create the table and insert the 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, 'id ', 5);Copy the code

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

mysqldump -uroot -p T > /path/xxx.sql; Show master status; The current position is 154Copy the code

3. Insert multiple records

INSERT INTO ` users ` (` id `, ` name `, ` age `) VALUES (null, 'name 2, 13), (null,' name the three ', 14), (null, 'name four, 15), (null,' name five ', 16), (null, 'name 6 ', 17);Copy the code

4. Perform a misoperation and insert several pieces of data after the misoperation

Update users set age = 5; INSERT INTO ` users ` (` id `, ` name `, ` age `) VALUES (null, 'name seven, 16), (null,' name 8, 18);Copy the code

5. After detecting misoperations, restore data. Stop external mysql services and use the backup data to restore the previous data.

6. Run the mysqlbinlog command to analyze binary files

The error occurred at position 706, and the last normal operation ended at 513. There was a normal SQL execution at 1152 to endCopy the code

7. Run the mysqlbinlog command to export executable SQL files from binlog logs and import the data to mysql

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

8. Skip the incorrect update statement and run the following normal statements using the logic in Step 7 to complete data restoration

summary

Any time a database crash occurs, it can be frustrating and upsetting. Binlog can be said to be a regret medicine after the database crash and data loss in various cases. This article through the offline environment, a simple data recovery experiment on the database, if there is any wrong, please also advise