Wechat search huan little growth road

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

introduce

Hello, I’m Leo. In the previous article, we introduced WAL’s security mechanisms. Data security can be ensured. Through security, we analyze the writing mechanism of binlog, redolog log. Today we will analyze the implementation principle of master slave library! How does MySQL ensure that data from primary and secondary libraries is consistent?

Thinking of writing

Based on feedback from readers and friends, I add a piece of thought to each article. Readers can better absorb relevant knowledge, and judge whether it is the knowledge they need.

Get into the business

Basic process of master/slave synchronization

As shown below, this is the state diagram of the master and slave libraries.

  • State 1: the client accesses MySQLA, A is the master library, B is the slave library, AND B synchronizes A’s data.
  • State 2: the client accesses MySQLB. B is the primary library, A is the secondary library, and A synchronizes data from B.

When a master/slave switch is required, it is the process of changing from state 1 to state 2.

Data is being synchronized from A to B or from B to A. The synchronized thread has super administrator rights. Therefore, it is recommended to set the slave library to readonly mode. One pitfall to avoid master-slave synchronization is the following double-write. So setting readOnly can’t hurt.

  1. Prevents misoperation of query statements of other running classes. Cause data inconsistency problems
  2. Prevents logic bugs when switching between states 1 and 2

Let’s take a look at each step of the process, as shown below

  1. Run the change master command on standby library B to set the IP, port, username, password of primary library A, and the location from which the binlog request should start. This location contains the file name and log offset
  2. Run the start slave command on slave library B. In this case, the slave library starts two threads, namely io_thread and SQL_thread, as shown in the figure. Io_thread is responsible for establishing connections with the primary library.
  3. After verifying the user name and password, primary database A reads the binlog from the local database to the location sent by secondary database B and sends the log to secondary database B.
  4. Standby database B obtains the binlog and writes it to a local file, which is called the relay log.
  5. Sql_thread reads transfer logs, parses commands in logs, and executes them.

Sql_thread threads are covered in more detail in a future article. I won’t explain too much here!

Based on the above process, let’s dissect the underlying process bit by bit. Let’s take a look at binlog transport

Binlog format huashan Lunjian

When it comes to binlog transfers, we’ll definitely talk about the format. The common format of a binlog is statement and row. There is also a format called mixed. This format is a hybrid of the previous two formats.

Let’s take an example

mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `t_modified`(`t_modified`) ) ENGINE=InnoDB; Insert into t values (1, 1, '2018-11-13'); Insert into t values (2, 2, '2018-11-12'); Insert into t values (3, 3, '2018-11-11'); Insert into t values (4, 4, '2018-11-10'); Insert into t values (5, 5, '2018-11-09');Copy the code

Let’s start by simply executing a delete statement to see what the corresponding binlog looks like.

mysql> delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;
Copy the code

When the binlog format is in the first case. statement

Binlog records the original text of SQL statements. Mysql > show binlog events in ‘master.000001’; To view

Analyze the results on the graph.

  • SET @@session.GTID_NEXT=’ANONYMOUS ‘SET @@session.GTID_NEXT=’ANONYMOUS’
  • The second line is a BEGIN, which corresponds to the fourth line commit, indicating a transaction in the middle;
  • The third line is the actual statement. As you can see, there is also a “use ‘test” command before the actual delete command is executed. This command is not executed by us, but is added by MySQL based on the database where the current table resides. This ensures that the log will be correctly updated to table T of the test library, regardless of which library the current worker thread is in, when it is sent to the standby library for execution. The delete statement following the use ‘test ‘command is the original SQL we entered. As you can see, the binlog faithfully records the SQL commands, even the comments.
  • The last line is a COMMIT. You can see it says xID =61.

Remember what xid is? Let’s review it.

Xid is a common binlog and redo log data field. During crash recovery, the redo log is scanned in sequence

  • If you encounter a prepare and COMMIT redo log, commit it.
  • If you encounter a redo log that has only Parepare but no COMMIT, take the XID to the binlog to find the corresponding transaction.

To illustrate the difference between statement and row, let’s take a look at the execution of the delete command

As you can see, this delete generates a warning. The current binlog is set to statement format. And delete with limit, it is possible to have data inconsistency between master and slave libraries. Take the example above.

  1. If the delete statement uses index A, the first row that meets the condition will be found based on index A.
  2. But if the index T_modified is used, then the line t_modified=’2018-11-09 ‘(a=5) is deleted.

In the statement format, the original statement is recorded in the binlog. Therefore, it is possible to execute the statement in the primary database using index A. The index T_modified is used when the standby database executes this SQL statement. Therefore, MySQL considers it risky to write this way.

So, if I change the format of binlog to binlog_format= ‘row’, does this problem not exist? Let’s take a look at what’s in Binog at this point.

As you can see, BEGIN and COMMIT are the same as statement binlog. However, the row binlog replaces the SQL statement with two events: Table_map and Delete_rows.

  • Table_map event, used to indicate that the next table to be manipulated is table T of the test library;
  • Delete_rows event, used to define the deletion behavior.

If we change the format to ROW, we won’t see the details. You also need to use the mysqlBinlog tool to parse and view the contents of the binlog with the following command. As you can see from the figure above, the transaction’s binlog starts at position 8900. So you can use the start-position argument to specify that the logs at this position should be parsed.

mysqlbinlog -vv data/master.000001 --start-position=8900;

  • Server ID 1, which indicates that the transaction is executed on the library server_id=1.
  • Each event has a CRC32 value because I set the binlog_checksum parameter to CRC32.
  • The Table_map event, as seen in Figure 5, shows the table to be opened next, mapped to the number 226. Now our SQL statement only operates on one table. What if we want to operate on multiple tables? Each table has a corresponding Table_map event that maps to a single number that distinguishes operations on different tables.
  • In mysqlbinlog, we use the -vv parameter to parse out the contents, so we can see the values of each field in the result (for example, @1=4, @2=4).
  • Binlog_row_image is set to FULL by default, so Delete_event contains all field values for the deleted row. If binlog_ROW_image is set to MINIMAL, only necessary information is recorded, in this case, only id=4.
  • The final Xid event is used to indicate that the transaction was committed correctly.

As you can see, when binlog_format uses row format, the primary key ID of the actual deleted row is recorded in the binlog, so that when the binlog is sent to the standby database, it will delete the row whose ID is 4.

What is miexed and what does it do for binlog

To solve this problem, we need to explain the advantages and disadvantages of row and statement binlogs.

Statement records general information, almost our execution information, and we cannot see what the specific logic is. So if you synchronize to a slave library, it’s easy to find data inconsistencies, hence the ROW format.

The row row format addresses the shortcomings of statement. You can look up the details of the execution, but the downside is that too much detail leads to too much memory usage. Such as deleting tens of thousands of data. A binlog in row format records each numeric record. This will not only occupy too much space, but also occupy disk I/O, affecting the efficiency of MySQL

Miexed was created to solve the problem of inconsistent statements, as well as the memory footprint of the row format. The main implementation is that he’s going to determine if this binlog is going to cause data inconsistencies. If so, use the ROW format. If no, logs in statement format are kept.

Therefore, if you set the binlog format of your online MySQL to statement, it is basically an unreasonable setting. You should at least set the format of binlog to mixed.

In our example, if we set it to mixed, it will be recorded as row; If a statement is executed without limit 1, it is recorded as a statement.

Next, let’s look at data recovery from the perspective of delete, INSERT, and UPDATE SQL statements.

If I execute a DELETE statement, the row binlog will also store the entire row of the deleted row. If a delete statement is executed and the wrong data is deleted, you can convert the delete statement from the binlog statement into an INSERT statement and restore the deleted data.

What if you did the wrong insert statement? That’s more direct. The row format records all field information in the insert statement’s binlog, which can be used to pinpoint the row that was just inserted. In this case, you simply change the INSERT statement into a DELETE statement and delete the row that was inserted by mistake.

If the update statement is executed, the binlog will record the entire row before the modification and the entire row after the modification. So, if you mistakenly execute an update statement, just swap the two lines before and after the event and execute it in the database to restore the update.

In fact, data manipulation errors caused by DELETE, INSERT, or UPDATE statements require a return to the state before the operation. MariaDB’s Flashback tool rolls data back and forth based on the principles described above.

Case problem

Mysql > insert into t values(10,10, now()); mysql> insert into t values(10,10, now());

If we set the binlog format to mixed, do you think MySQL will record it as row or statement?

According to the output, the statement format is displayed. If you send it to the master database for synchronization, the time there must be inaccurate, resulting in inconsistent data from the master database ah.

So let’s take the xID and use the mysqlBinlog tool

SET TIMESTAMP=1546103491 it uses the SET TIMESTAMP command to specify the return time of the following now() function.

Therefore, the value of the row inserted by the INSERT statement is the same whether the binlog is executed by the standby library one minute later or used to restore the library’s backup three days later. In other words, with the SET TIMESTAMP command, MySQL ensures the consistency of primary and secondary data.

Error: Do not use the mysqlbinlog tool to parse data and then copy the statement from it. Such an operation is risky. So be sure to send the entire structure to MySQL for execution.

Circular replication problem with master/slave synchronization

In our real world development scenario, the master library is not always the master library, and the slave library is not always the slave library. For safety. It’s often designed this way.

And that brings up another problem. The service logic updates A statement on node A and then sends the generated binlog to node B. Node B also generates A binlog after executing the update statement. I recommend that you set log_slave_updates to ON, indicating that the standby database generates a binlog after executing the relay log.

So, if node A is also the standby library of node B, it executes the new binlog generated by node B again, and then the update statement is repeated between node A and node B, which is cyclic replication. So how do we solve this?

Solution:

  1. Specify that the server ids of two libraries must be different. If they are the same, the relationship between them cannot be set as master/slave.
  2. A standby database receives a binlog and, during playback, generates a new binlog with the same server ID as the original one.
  3. After receiving the log from the primary library, each library determines the server ID. If the log is the same as its own, it indicates that the log is generated by itself and directly discards the log.

Following this logic, if we set up the double M structure, the log execution flow would look like this:

  1. For transactions updated from node A, the binlog contains the server ID of node A.
  2. After the binlog is sent to node B, the server ID generated by node B is also the server ID of node A.
  3. Node A determines that the server ID is the same as its own and does not process the log. So, the loop is broken here.

conclusion

In this article, we introduce how MySQL ensures data consistency between master and slave libraries, the implementation process, the advantages and disadvantages of the three formats of binlog, the application configuration of the master and slave libraries of MySQL in online scenarios, the problem of cyclic replication of the switchover between master and slave libraries and the solution.

The more you know, the more you don’t know. May the future years, do not forget the original aspiration, study hard! Have a life to live up to!

Any problems can be discussed together. Like + comment + follow is the best support for the blogger!