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

The following mainly brings you to use binlog to restore MySQL data process parsing. I hope that using binlog to restore MySQL data process parsing can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

By understanding the relevant configuration of binlog logs, we can easily master the data recovery operation of the database through binlog.

Mysql log file

Any mature software will have a set of mature log system, when there is something wrong with the software, these logs are the treasure house to query the source of the problem. Similarly, mysql is no exception, and there is a series of logs recording the running status of mysql.

Mysql mainly has the following types of logs:

Error log: recording error messages while mysql is running

General query log: records the statements that mysql is running, including each sql for queries, modifications, updates, etc.

Slow query logs: SQL statements that record queries that are time consuming

Binlog log: records data modifications, including table creation, data updates, etc.

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

Mysql-- verbose-- help | grep-A 1 'Default options' # this command lists the paths to be searched sequentially by my.cnf.

Binlog log

Binlog is binary log, a binary log file that records all database update statements, including table updates and record updates, 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 mistakenly deleted or something indescribable occurs, 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 through binlog and notifies the slave database to update, thus ensuring the consistency of the master-slave database data.

Mysql is divided into service layer module and storage engine layer module according to its function. The service layer is responsible for client connection, SQL statement processing optimization and other operations, while the storage engine layer is responsible for data storage and query. Binlog belongs to the log of the service layer module, that is, engine independence, and all data changes of the data engine record binlog logs. When a database crash occurs, the binlog log can also verify the commit of InnoDB's redo log if the InnoDB engine is used.

Binlog log is enabled

How to enable the log:

1. Add configuration

Log_bin=ONlog_bin_basename=/path/bin-loglog_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 creates a .index file specified by log_bin_index and multiple binary log files, and all the binlog files used by mysql are recorded sequentially in index. The binlog log is suffixed with the specified name (or default) plus a self-increasing number, ex:bin-log.000001, and the binlog log is rebuilt when the following three situations occur:

The file size reaches the value of the max_binlog_size parameter

Execute the flush logs command

Restart the mysql service

Binlog log format

You can set the format of binlog through the value of parameter binlog_format. Available values include statement, row, and mixed.

* statement format: record the original SQL statement executed by the database * row format: record specific row changes, which 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 opened and viewed directly like other files. However, mysql provides the binlog viewer mysqlbinlog, which can parse binaries. Of course, the results of log parsing in different formats are different.

1. Statement format log, execute mysqlbinlog / path/bin-log.000001, you can directly see the original SQL statement executed. 2. Row format log, the readability is not so good, but you can still use parameters to make the document more readable mysqlbinlog-v / path/bin-log.000001

Two pairs of very important parameters for mysqlbinlog

1.-- start-datetime-- stop-datetime parses binlog; 2 in a certain period of time.-- start-position-- stop-position parses binlog between two position

Use binlog to recover data:

Using binlog to recover data is essentially to find all the DML operations through binlog, get rid of the wrong SQL statements, and then go through the long March again, and the data can be recovered.

Offline practice:

Create a datasheet 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, 'name one', 5)

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

Mysqldump-uroot-PT > / path/xxx.sql; # backup database show master status; # to view the current position location, this time value is 154,

Insert multiple records

INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, 'name two', 13), (null, 'name three', 14), (null, 'name four', 15), (null, 'name five', 16), (null, 'name six', 17)

Make a misoperation, and insert a few pieces of data after the misoperation

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

After the misoperation is found, the data recovery is carried out. First, the external service of mysql is stopped, and the backup data is used to restore the last data.

The binary file is analyzed by mysqlbinlog command, and it is found that

The misoperation occurred at position 706, and the last normal operation ended at 513.There was a normally executed SQL at 1152 to the end.

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

Skip the wrong update statement, and then rerun the subsequent normal statement through the logic of step 7 to complete the data recovery

Summary

Whenever a database crash occurs, it will cause people to frown and get upset. Binlog can be said to be a medicine for regret after database crashes and data loss in various cases. This paper makes a simple data recovery experiment on the database through the offline environment. If there is anything wrong, please give us some advice.

For the above about the use of binlog to restore MySQL data process parsing, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.