Pay attention to the wechat public number [programmer Bai Ze], enter bai Ze’s knowledge sharing planet 🌍

preface

MySQL InnoDB storage engine: Redo log; MySQL InnoDB storage engine: redo log; MySQL InnoDB storage engine: redo log The bin log is a binary log that logs all database table data and table structure changes (no query operations are recorded). With this log, you can achieve: data recovery and master/slave replication (not hard to understand, because all operations involving changes are recorded and can be traced).

This article focuses on data recovery using bin log, and the next article will cover master-slave replication.

Preliminary knowledge

SSH Tool Recommendation

The following commands will be frequently typed in the console terminal, so we recommend an open source and free SSH client electerm: github.com/electerm/el…

Bin Log status management

Before explaining the two features that bin log can provide, learn how to manage the bin log state of your MySQL service and control it by modifying parameters. MySQL/bin log enabled MySQL/bin log enabled MySQL/bin log enabled If not, you are advised to enable bin log by modifying the MySQL configuration file and then restart the MySQL service.

In Linux system, for example, MySQL database is according to the/etc/my CNF – / etc/MySQL/my CNF – / usr/local/MySQL/etc/my CNF – ~ /. My. The order of CNF reading configuration files, If the parameters are set repeatedly, the parameters in the later configuration file will overwrite the former. If you don’t have a configuration file for your MySQL service, just create one yourself and put it in one of the above locations. Then enter the configuration code in the configuration file you found online and restart the MySQL service.

Now assume that you have enabled MySQL binary logging as follows:

mysql> show variables like '%log_bin%';
Copy the code

Bin log Data file

Looking at the query results above, you can see two path variables: Log_bin_basename and log_bin_index respectively indicate the location where data files are generated (/usr/local/mysql.data /) and file name rules (binlog.xxxxx1, binlog.xxxxx2, and so on) after binlog is enabled. And the index file binlog.index, which holds the list of binlog data files.

#View the list of MySQL data files (most of the MySQL data files are in this path, as shown below, mainly bin)logRelated data files and index files)
lilithgamesdeMacBook-Pro-42:~ lilithgames$ sudo ls -al /usr/local/mysql/data
Copy the code

#Check the binlogIndex file contents (exactly corresponding to the three data files listed above)
lilithgamesdeMacBook-Pro-42:~ lilithgames$ sudo cat /usr/local/mysql/data/binlog.index
Copy the code

Since bin log records all changes made to the database, ** does it record SQL statements in a data file or the result of the changed rows? ** There are three recording modes:

  • ROW: data file will record each line of data is modified, so that it can guarantee the restore data or master-slave replication won’t because some functions, such as now () function performs two access time is not consistent) incomplete data in the same situation, the disadvantage is that for the whole table changes will lead to a large amount of data is inserted into the data file.
  • SRATEMENT: SQL statement that records modified data. In contrast to ROW, data synchronization may be inconsistent in some cases.
  • MIXED: Use a mixture of the above two recording modes, using SRATEMENT in general and ROW in special cases.

However, the ROW schema has been optimized in the new version of MySQL. Changes to table structures are recorded in STATEMENT schema and changes to records are recorded in data files. Therefore, ROW mode is the default working mode for bin log.

mysql> show variables like 'binlog_format'; 
Copy the code

Data recovery

To prepare data

**Talk is cheap, show me the code! ** I know you can’t wait to experience bin Log data recovery, so let’s get started

For demonstration purposes, a new log file is generated by executing flush logs in the MySQL login state (to separate the following commands from the database into a new data file).

#Generate a new binary data file (ordinal increment)
mysql> flush logs;
#View all current binary data files
mysql> show binary logs;
Copy the code

Because I executed the Flush log command twice, this generated two new binary data files, which are obviously small because no new table changes have been logged.

Next we set up a test database, TEST_Database, and then create a user table and insert a few test data into it.

# to createusertableCREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) NOT NULL.PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET=utf8; Insert test user dataINSERT INTO user (id, username) VALUES (null.'AAA');
INSERT INTO user (id, username) VALUES (null.'BBB');
INSERT INTO user (id, username) VALUES (null.'CCC'); # query testSELECT * FROM user
Copy the code

Next we use the mysqlbinlog command to view the binlog data file (guess table changes are logged in binlog.000010 binary), part of which is shown here.

sudo mysqlbinlog /usr/local/mysql/data/binlog.000010
Copy the code

MySQL > insert into binlog.000010; MySQL > insert into binlog.000010; MySQL > insert into binlog.000010;

  • The first AT represents the starting position of an event pos, the middle is the binary data of the event, and the last AT represents the starting position of the next event POS (i.e. the end position of the current time POS).
  • 220303 17:59:37 Server ID 1 Indicates the time when server 1 executes the event
  • Exec_time indicates the execution time (the exact time is different for master and slave replication, as explained in the next article).

#The mysqlbinlog command can also add parameters, such as specifying to query the data between pos start and pos end, Google ~
sudo mysqlbinlog /usr/local/mysql/data/binlog.000010 
#Of course, you can query bin directly in mysql login state without using mysqlbinloglogFor data file contents, the tests are as follows:
mysql> show binlog events in 'binlog.000010' from 447 limit 10;
Copy the code

Simulation error

# error delete id is1The userDELETE FROM user where id=1Insert two more usersINSERT INTO user (id, username) VALUES (null.'DDD');
INSERT INTO user (id, username) VALUES (null.'EEE');
Copy the code

Data recovery

To be clear: the essence of data recovery with the bin log file is to re-execute the SQL** between the two POS intervals

First check the binlog.000010 data file with the mysql command

mysql> show binlog events in 'binlog.000010';
Copy the code

Select pos: 447 from user; select pos: 447 from user; select POS: 1864 from delete_rows; select pos: 1864 from user; select pos: 447 from user; 1636 (otherwise warning ⚠️)

Mysqlbinlog = mysqlbinlog = mysqlbinlog = mysqlbinlog = mysqlbinlog = mysqlbinlog;

sudo mysqlbinlog --start-position=447 --stop-position=1636 /usr/local/mysql/data/binlog.000010 > return.sql
Copy the code

Execute the return. SQL file (equivalent to executing the SQL statement of the interval again) to restore the data! And you’re done! Deleted AAA is back!! Don’t get fired!!

mysql> source ~/return.sql
Copy the code

conclusion

This article tells the simple use of bin log case for data recovery, and spent a bigger space to explain some bin log of basic knowledge, in order for the subsequent interpretation using bin log of master-slave replication lays the foundation, I hope, after reading this article, you feel the understanding of the binary log eight-part essays, on the basis of further.

I am Bai Ze, a back-end programmer/student party, and I have set up a spring and Autumn recruitment preparation/internal push/chat group, welcome to join.

P3-juejin.byteimg.com/tos-cn-i-k3…

Below are my wechat and public account, follow the public account [programmer Bai Ze], reply resume, you can get the resume template I am using.

P3-juejin.byteimg.com/tos-cn-i-k3…