Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the principle of master-slave, master-slave and read-write separation in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. The basic principle of MySQL active and standby.

In state 1, the read and write of the client directly accesses node A, while node B is the standby library of A, but synchronizes all the updates of A to be executed locally. This keeps the data of nodes B and A the same. When you need to switch, cut to state 2. At this time, the client reads and writes access to node B, and node An is the standby library of B. [related recommendation: mysql video tutorial]

In state 1, although node B is not directly accessed, it is recommended to set standby node B to read-only mode. There are several reasons:

1. Sometimes some operation query statements will be put on the standby database to check, set to read-only to prevent misoperation

two。 Prevent handover logic from having bug

3. Readonly status can be used to determine the role of a node.

If you set the standby library to read-only, how can you keep up-to-date with the main library?

The readonly setting is not valid for super rights users, while threads used to synchronize updates have super permissions

The following figure is a complete flowchart of a update statement executed on node An and then synchronized to node B.

A long connection is maintained between standby library B and main library A. There is a thread inside the main library A that is dedicated to this persistent connection to service slave B. The complete process of a transaction log synchronization is as follows:

1. Use the change master command on slave B to set the IP, port, user name, password of main library A, and where to start requesting binlog, which contains the file name and log offset

two。 Execute the start slave command on slave B, and the slave starts two threads, io_thread and sql_thread in the figure. Io_thread is responsible for establishing a connection with the main library.

3. After verifying the user name and password, the main library A begins to read the binlog locally and send it to B according to the location passed by the slave library B.

4. After getting the binlog, slave B writes to the local file, which is called transit log.

5.sql_thread reads the transit log, parses the commands in the log, and executes

Due to the introduction of multithreaded replication scheme, sql_thread evolved into multiple threads

Second, the problem of circular replication

Double M structure:

Node An and node B are the active and standby relationship between each other. In this way, there is no need to modify the active / standby relationship when switching.

There is a problem to be solved in the double M structure. The business logic updates a statement on node A, and then sends the generated binlog to node B. after node B executes this update statement, it will also generate binlog. So, if node An is also the standby library of node B, it is equivalent to taking the newly generated binlog of node B for execution again, and then the update statement will be iterated between node An and B, that is, circular replication.

MySQL records the server id of the instance where the command was first executed in binlog. Therefore, the following logic can be used to solve the problem of circular replication between two nodes:

1. It is stipulated that the server id of two libraries must be different. If they are the same, the relationship between them cannot be set as an active / standby relationship.

two。 A slave library is connected to the binlog and generates the same new binlog as the server id of the original binlog during playback.

3. After receiving the log sent from its own main library, each library first judges the server id. If it is the same as its own, it indicates that the log is generated by itself, and then discards the log directly.

The execution flow of the double M structure log is as follows:

1. For transactions updated from node A, what is recorded in binlog is the server id of A.

two。 After being passed to node B for execution once, the server id of the binlog generated by node B is also the server id of A.

3. Then send it back to node An and determine that the server id is the same as your own, and the log will no longer be processed. So, the endless cycle is broken here.

Third, active and standby delay

1. What is the master / slave delay?

The time points related to data synchronization mainly include the following three:

1. The main library An executes a transaction and writes it to binlog, which is recorded as T1.

two。 It is then passed to standby B, and the time when standby B receives this binlog is recorded as T2.

3. After library B executes this transaction, record this time as T3

The so-called master / standby delay is the difference between the execution time of the standby database and the execution time of the master database of the same transaction, that is, T3-T1.

You can execute the show slave status command on the slave database, and its return result displays seconds_behind_master, which is used to indicate how many seconds the current slave database has been delayed.

The method of calculating seconds_behind_master is as follows:

1. There is a time field in the binlog of each transaction, which records the time written on the main database.

two。 The standby database takes the value of the time field of the currently executing transaction, calculates the difference between it and the current system time, and gets the seconds_behind_master.

If the system time setting of the master / slave database machine is inconsistent, the value of the master / slave delay will not be inaccurate. When the standby library connects to the primary library, the system time of the current primary library is obtained through the SELECTUNIX_TIMESTAMP () function. If it is found that the system time of the main database is not the same as its own at this time, the standby database will automatically deduct this difference when performing seconds_behind_master calculation.

Under normal network conditions, the main source of master / slave delay is the time difference between the slave receiving the binlog and executing the transaction.

The most direct manifestation of the master / standby delay is that the backup database consumes transit logs at a slower rate than the master database produces binlog.

2. The origin of the master / standby delay

1. Under some deployment conditions, the performance of the machine where the standby library is located is worse than that of the machine where the main library is located.

two。 There is a lot of pressure to prepare the library. The main library provides write capabilities, while the standby library provides some reading capabilities. Ignoring the pressure control of the standby database, the query on the standby database consumes a lot of CPU resources, affects the synchronization speed, and causes the master / slave delay.

You can do the following:

One master, many followers. In addition to preparing the library, you can pick up a few more slave libraries and let them share the pressure of reading from the library.

Output through binlog to external systems, such as Hadoop, to enable external systems to provide statistical query capabilities

3. Big business. Because the main database must wait for the transaction to be executed before it is written to the binlog, and then passed to the standby database. So, if a statement on a master library executes for 10 minutes, the transaction is likely to cause a 10-minute delay in the slave library.

Typical large transaction scenario: delete too much data and DDL of large tables with delete statement at one time

Fourth, active and standby handover strategy 1, reliability priority strategy

Under the double M structure, the detailed process of switching from state 1 to state 2 is as follows:

1. Judge the seconds_behind_master of slave B now. If it is less than a certain value, proceed to the next step, otherwise continue to retry this step.

two。 Change the main library A to read-only state, that is, set readonly to true

3. Determine the value of seconds_behind_master in standby B until the value becomes 0

4. Change standby library B to read-write state, that is, set readonly to false

5. Cut the business request to standby B

There is unavailable time in this switching process. After step 2, both main library An and standby library B are in the readonly state, that is, the system is in an unwritable state until step 5 is completed. In this unavailable state, the more time-consuming is step 3, which may take several seconds. That's why you need to make a judgment at step 1 to make sure that the value of seconds_behind_master is small enough.

The unavailability time of the system is determined by the strategy of giving priority to data reliability.

2. Usability priority strategy

Usability priority strategy: if you forcibly adjust steps 4 and 5 of the reliability priority policy to the beginning, that is, do not wait for the synchronization of master and standby data, directly cut the connection to slave B, and make slave B read and write, then there is almost no unavailable time in the system. The price of this switching process is that there may be data inconsistencies.

Mysql > CREATE TABLE `t` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `c` int (11) unsigned DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;insert into t (c) values (1), (2), (3)

Table t defines a self-increasing primary key id. After initializing the data, there are three rows of data on both the primary and standby databases. Continue to execute the two insert statements on table t, in turn:

Insert into t (c) values (4); insert into t (c) values (5)

Suppose that there are a large number of updates to other data tables on the main database, resulting in a delay of 5 seconds between the master and the slave. After inserting a statement of canti4, the master / slave switch is initiated.

The following figure shows the availability first policy, and the switching process and data results during binlog_format=mixed

1. In step 2, the main library An executes the insert statement, inserts a row of data (4p4), and then starts to switch between the master and the standby.

two。 In step 3, due to a 5-second delay between the master and slave, slave B begins to receive commands from the client to insert cadministrator 5 before it has time to apply the transfer log of inserting cymb4.

3. In step 4, slave B inserts a row of data (4 binlog 5) and sends this data to main library A

4. In step 5, standby B executes inserting the transfer log of cend4, and inserts a row of data (5p4). On the other hand, the statement of inserting center5 executed directly in slave B is passed to main library A, and a row of new data is inserted (5jue 5).

The end result is that there are two inconsistent lines of data on main database An and standby database B.

Usability priority policy, setting binlog_format=row

So when the row format records the binlog, it records all the field values of the newly inserted row, so only one line is inconsistent in the end. Moreover, the active and standby application threads on both sides will report an error duplicate key error and stop. That is to say, in this case, neither of the rows of data of standby library B (5j4) and of main library A (5pr 5) will be executed by the other party.

3. Summary

1. When using binlog in row format, data inconsistencies are more likely to be found. When using binlog in mixed or statement format, it may take a long time to find the problem of data inconsistency

two。 The availability priority policy of active / standby handover will lead to data inconsistency. Therefore, in most cases, the reliability priority strategy is recommended.

5. Parallel replication strategy of MySQL

For the parallel replication capability of the master and standby, you should pay attention to the two black arrows in the picture above. One writes to the main database on behalf of the client, and the other represents the sql_thread execution transfer log on the standby database.

Before the MySQL5.6 version, MySQL only supported single-thread replication, so there was a serious delay between master and standby when the concurrency of the main library was high and the TPS was high.

The multithreaded replication mechanism splits the sql_thread with only one thread into multiple threads, all of which conform to the following model:

Coordinator is the original sql_thread, but now it no longer updates data directly, but is only responsible for reading transit logs and distributing transactions. The one that really updates the log becomes the worker thread. The number of worker threads is determined by the parameter slave_parallel_workers.

Coordinator needs to meet the following two basic requirements when distributing:

Cannot cause an update overwrite. This requires that two transactions on the same line be updated and must be distributed to the same worker

The same transaction cannot be taken apart and must be placed in the same worker

1. Parallel replication strategy of MySQL5.6 version

The MySQL5.6 version supports parallel replication, but the supported granularity is parallel by library. In the hash table used to determine the distribution policy, key is the database name

The parallel effect of this strategy depends on the stress model. If there are multiple DB on the main library and the pressure of each DB is balanced, the effect of using this strategy will be very good.

Two advantages of this strategy:

The hash value is constructed quickly, only the library name is needed.

The format of binlog is not required, because binlog in statement format can easily get the library name.

You can create different DB, distribute the same heat table evenly into these different DB, and force this strategy.

2. Parallel replication strategy of MariaDB.

Redo log group commit optimization, which is what MariaDB's parallel replication strategy takes advantage of:

Transactions that can be committed in the same group must not modify the same line

Transactions that can be executed in parallel on the main library must also be executed in parallel on the standby database.

In terms of implementation, MariaDB does this:

1. Transactions committed together in one group have the same commit_id, and the next group is commit_id+1

2.commit_id is written directly into binlog.

3. When transferred to standby applications, transactions of the same commit_id are distributed to multiple worker for execution

4. After all the execution of this group is completed, coordinator will pick up the next batch.

The following figure assumes the execution of three sets of transactions in the main library. When trx1, trx2, and trx3 commit, trx4, trx5, and trx6 are executed. In this way, when the first set of transactions is committed, the next set of transactions will soon enter the commit state

According to MariaDB's parallel replication strategy, the execution effect on the slave database is as follows:

When executing on the slave database, the second set of transactions can not be executed until the first set of transactions is fully executed, so that the throughput of the system is not enough.

In addition, this plan is easy to be held back by big business. Assuming that trx2 is a very large transaction, the next group can not start execution until the execution of trx1 and trx3 is completed when preparing the library application. Only one worker thread is working, which is a waste of resources.

3. Parallel replication strategy of MySQL5.7 version

The parallel replication policy is controlled by the parameter slave-parallel-type in the MySQL5.7 version:

Configured as DATABASE, indicating the use of the MySQL5.6 version of the per-library parallel strategy

Configured as LOGICAL_CLOCK, which represents a policy similar to MariaDB. MySQL made the optimization on this basis.

Can all transactions that are executed at the same time be in parallel?

No, because there may be transactions in the lock waiting state due to lock conflicts. If these transactions are assigned to different worker on the slave database, there will be inconsistencies between the standby database and the main database.

The core of the MariaDB strategy is that all transactions in the commit state can be in parallel. The transaction in the commit state indicates that it has passed the lock conflict test.

In fact, as long as the redo log prepare phase can be reached, it means that the transaction has passed the lock conflict test.

Therefore, the idea of the MySQL5.7 parallel replication strategy is:

1. Transactions that are in prepare state at the same time can be executed in parallel when preparing the database.

two。 Transactions in prepare state and transactions in commit state can also be executed in parallel when the standby database is executed.

The binlog group has two parameters when it is submitted:

The binlog_group_commit_sync_delay parameter indicates the delicacy of the delay before calling fsync

The binlog_group_commit_sync_no_delay_count parameter indicates how many times the base class calls fsync

These two parameters are used to deliberately lengthen the time of binlog from write to fsync, so as to reduce the number of binlog writes. In MySQL5.7 's parallel replication strategy, they can be used to create more transactions that are also in the prepare phase. This increases the degree of parallelism of standby replication. In other words, these two parameters can not only deliberately make the main database commit more slowly, but also make the standby database execute faster.

4. Parallel replication strategy of MySQL5.7.22.

MySQL5.7.22 adds a new parallel replication strategy, which is based on WRITESET parallel replication, and adds a parameter binlog-transaction-dependency-tracking to control whether this new policy is enabled. There are three optional values for this parameter:

COMMIT_ORDER, a strategy to determine whether it can be parallelized by entering prepare and commit at the same time

WRITESET, which means that for each row that the transaction involves updating, the hash value of that row is calculated to form the collection writeset. If two transactions do not operate on the same row, that is, their writeset does not intersect, they can run in parallel.

WRITESET_SESSION is an additional constraint on the basis of WRITESET, that is, two transactions executed successively by the same thread on the main database should be executed in the same order when preparing the database.

For unique identification, the hash value is calculated by library name + table name + index name + value. If there are other unique indexes on a table in addition to the primary key index, then for each unique index, the writeset corresponding to the insert statement will be increased by one more hash value.

1.writeset is written directly into binlog after the main library is generated, so there is no need to parse binlog content when the standby library is executed.

two。 You don't need to scan the binlog of the entire transaction to decide which worker to distribute to, saving memory.

3. Since the distribution strategy of the repository does not depend on binlog content, it is possible to index binlog in statement format.

For scenarios where there are no primary key and foreign key constraints on the table, the WRITESET strategy cannot be parallel and will be temporarily reduced to a single-threaded model.

6. if there is a problem with the main library, what should I do from the slave library?

The following figure is a basic one-master and multi-slave structure.

In the figure, the dotted arrow indicates the relationship between master and standby, that is, An and A 'are the master and standby for each other, and slave libraries B, C and D point to master library A. The setting of one master and multiple slaves is generally used for the separation of reads and writings. The master library is responsible for all writes and partial reads, while other read requests are shared by the slave library.

After the switching of one master and multi-slave structure is completed, A' will become the new master library, and slave libraries B, C and D will also be transferred to A'.

1. Site-based active / standby handover

When we set node B as the slave library of node A', we need to execute a change master command:

CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password MASTER_LOG_FILE=$master_log_name MASTER_LOG_POS=$master_log_pos

MASTER_HOST, MASTER_PORT, MASTER_USER and MASTER_PASSWORD represent the IP, port, user name and password of the main library A', respectively.

The last two parameters, MASTER_LOG_FILE and MASTER_LOG_POS, indicate that you want to continue synchronization from the log in the master_log_pos location of the master_log_name file of the main library. And this location is the so-called synchronization point, that is, the file name and log offset corresponding to the main library.

It is difficult to find the synchronization site accurately, only one approximate position can be taken. One way to desynchronize sites is as follows:

1. Wait for the new main database A' to synchronize all the transfer logs.

two。 Execute the show master status command on A' to get the latest File and Position on the current A'

3. Take the time T of the failure of the original main library A

4. Parse the File of A' with mysqlbinlog tool to get the site of T time, which can be used as $master_log_pos.

This value is not accurate, there is such a case, suppose that at T this moment, main library A has executed an insert statement to insert a row of data R, and has passed binlog to A 'and B, and then the host of main library A will be powered off immediately after the transmission. So, at this point, the state of the system is as follows:

1. On slave library B, the line binlog,R already exists due to synchronization

two。 The R line already exists on the new main library A', and the log is written after the master_log_pos location.

3. When you execute the change master command on slave library B and point to the master_log_pos location of the File file of A', the binlog inserted into the row of R data will be synchronized to slave library B for execution, resulting in a primary key conflict and then stopping tongue

In general, there are two common ways to skip these errors when switching tasks

One is to actively skip a transaction.

Set global sql_slave_skip_counter=1;start slave

Another way is to skip the specified error directly by setting the slave_skip_errors parameter. This background is that we know very well that skipping these errors directly during the master / slave switching process is lossless, so we can set the slave_skip_errors parameter. After the synchronization relationship between the master and standby has been established and stably executed for a period of time, you still need to set this parameter to empty, so as to avoid the master-slave data inconsistency and skip it.

2 、 GTID

MySQL5.6 introduces GTID, which is a global transaction ID that is generated when a transaction commits and is the unique identity of the transaction. Its format is:

GTID=source_id:transaction_id

Source_id is automatically generated when an instance is started for the first time and is a globally unique value.

Transaction_id is an integer with an initial value of 1, which is assigned to the transaction each time the transaction is committed, plus 1

To start GTID mode, you only need to start a MySQL instance with the parameters gtid_mode=on and enforce_gtid_consistency=on.

In GTID mode, each transaction corresponds to a GTID. There are two ways to generate this GTID, and which one is used depends on the value of the session variable gtid_next

1. If gtid_next=automatic, the default value is used. At this point, MySQL assigns GTID to the transaction. When recording a binlog, record a line of SET@@SESSION.GTID_NEXT='GTID' first. Add this GTID to the GTID collection of this example

two。 If gtid_next is the value of a specified GTID, such as through set gtid_next='current_gtid', then there are two possibilities:

If current_gtid already exists in the GTID collection of the instance, the next transaction will be ignored by the system directly.

If the current_gtid does not exist in the GTID collection of the instance, assign the current_gtid to the next transaction to be executed, that is, the system does not need to generate a new GTID for the transaction, so the transaction_id does not need to add 1

A current_gtid can only be used by one transaction. After this transaction is committed, if you want to execute the next transaction, execute the set command to set gtid_next to another gtid or automatic

In this way, each MySQL instance maintains a GTID collection that corresponds to all transactions performed by the instance

3. Active / standby handover based on GTID

In GTID mode, the syntax for slave library B to be set as the new master library A' is as follows:

CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password master_auto_position=1

Master_auto_position=1 indicates that the active / standby relationship uses the GTID protocol.

The GTID collection of instance A'is marked as set_a, and the GTID collection of instance B is marked as set_b. We execute the start slave command on example B, and the logic for fetching binlog is as follows:

1. Instance B specifies the master database, which establishes a connection based on the master / slave protocol.

two。 Instance B sends set_b to the main database A'

3. Example A' calculates the difference between set_a and set_b, that is, the set of all GTID that exists in set_a but does not exist in set_b, and determines whether A' locally contains all the binlog transactions required by the difference.

If it is not included, it means that A' has deleted the binlog required by instance B and returned an error directly.

If you confirm that it is all included, A' finds the first transaction that is not in set_b from its own binlog file and sends it to B.

4. Then, starting from this transaction, read the file back and send binlog to B in order to execute.

4. GTID and online DDL

If the performance problem is caused by missing index, it can be solved by adding index online. However, in order to avoid the impact of the new index on the performance of the main database, you can first add the index in the standby database, and then switch over. Under the double M structure, the DDL statements executed by the standby database will also be passed to the main database. In order to avoid the impact on the main database after being returned, you need to shut down the binlog through set sql_log_bin=off, but the operation may result in inconsistent data and logs.

Two library instances X and Y that are master / standby relationships with each other, and the current main library is X, and GTID mode is turned on. At this time, the master / slave switching process can be as follows:

Execute stop slave on instance X

Execute the DDL statement on instance Y. There is no need to shut down binlog here.

After the execution is completed, find out the GTID corresponding to the DDL statement, and mark it as source_id_of_Y:transaction_id.

Execute the following statement sequence on instance X:

Set GTID_NEXT= "source_id_of_Y:transaction_id"; begin;commit;set gtid_next=automatic;start slave

The purpose of this is not only to have an binlog record for the update of instance Y, but also to ensure that the update is not performed on instance X.

7. Separation of MySQL read and write

The basic structure of read-write separation is shown below:

The main purpose of read-write separation is to share the pressure on the main database. The structure in the figure above is that the client takes the initiative to do load balancing. In this mode, the connection information of the database is generally placed on the connection layer of the client. The client selects the back-end database for query

Another architecture is that there is an intermediate proxy layer proxy between MySQL and the client. The client only connects to the proxy, and the proxy decides the distribution route of the request according to the request type and context.

1. The client is directly connected, so there is one less layer of proxy forwarding, so the query performance is slightly better, and the overall architecture is simple, and it is more convenient to troubleshoot problems. However, in this solution, due to the need to understand the details of the back-end deployment, the client will be aware of the operations such as master / slave switching and library migration, and the database connection information needs to be adjusted. Generally, such an architecture will be accompanied by a component responsible for managing the back end, such as Zookeeper, so that the business side can only focus on the development of business logic.

two。 The architecture with proxy is more client-friendly. The client does not need to pay attention to the back-end details, connection maintenance, back-end information maintenance and other work are done by proxy. But in this way, the requirements for the back-end maintenance team will be higher, and proxy also needs to have a highly available architecture.

The phenomenon that an expired state of the system will be read from the library is called expired read.

1. Force the scheme of the main library.

To force the main database scheme is to classify query requests. In general, it can be divided into two categories:

1. For requests that must get the latest results, force them to be sent to the main database

two。 For requests that can read old data, send it to the slave database

The biggest problem with this solution is that sometimes there may be requirements that none of the queries can be overdue, such as some financial businesses. In this case, it is necessary to give up the separation of read and write, and all the read and write pressure is in the main library, which is tantamount to giving up expansibility.

2. Sleep scheme

After the master library is updated, sleep it before reading the slave library. The specific solution is similar to executing a select sleep (1) command. The assumption of this scheme is that in most cases, the master / slave delay is less than 1 second, and there is a high probability of getting the latest data by doing a sleep.

Take the goods released by buyers as an example, after the goods are released, the content entered by the client will be directly displayed on the page as the latest goods by Ajax, instead of actually going to the database to make a query. In this way, the seller can use this display to confirm that the product has been released successfully. By the time the seller refreshes the page to view the goods, it has already passed a period of time, and the goal of sleep has been achieved, and then the problem of overdue reading has been solved.

But the plan is not accurate:

1. If the query request can get the correct result from the library in 0.5 seconds, it will also wait 1 second.

two。 If the delay is more than 1 second, there will still be overdue reads.

3. Judge the active and standby scheme without delay.

The value of the seconds_behind_master parameter in the show slave status result, which can be used to measure the delay time between master and slave.

1. The first way to ensure that there is no delay between master and slave is to determine whether seconds_behind_master is equal to 0 before each query request is executed by the slave library. If it is not equal to 0, you must wait until this parameter becomes 0 before the query request can be executed

Some screenshots of the show slave status results are as follows:

two。 The second method is to compare the sites to ensure that there is no delay between the active and standby sites:

Master_Log_File and Read_Master_Log_Pos represent the latest sites of the main library read.

Relay_Master_Log_File and Exec_Master_Log_Pos represent the latest sites executed by the repository.

If the values of Master_Log_File and Read_Master_Log_Pos and Relay_Master_Log_File and Exec_Master_Log_Pos are exactly the same, the received log has been completed synchronously.

3. Third, compare GTID sets to ensure that there is no delay between master and slave:

Auto_Position=1 said that the GTID protocol was used in this pile of master / standby relationships.

Retrieved_Gitid_Set is the GTID collection of all logs received by the repository.

Executed_Gitid_Set is a collection of all GTID that have been executed in the repository.

If the two sets are the same, it also means that the logs received by the standby database have been completed synchronously.

4. The status of the binlog of a transaction between the primary and secondary libraries:

1) the execution of the main library is completed, write to binlog, and feedback to the client

2) binlog is sent from the main library to the standby database, which is received by the standby database

3) complete the execution of binlog in the repository.

The above logic for judging that there is no delay between master and slave is that all the logs received by the slave database have been executed. However, from the analysis of the status of binlog between master and slave, some logs are in the state that the client has received the submission confirmation, while the slave database has not received the log.

At this point, three transactions, trx1, trx2, and trx3, are executed on the main library, where:

Trx1 and trx2 have been transferred to the slave library, and the execution has been completed

The trx3 is completed in the master library and has been replied to the client, but has not yet been transferred to the slave library

If the query request is executed on slave library B at this time, according to the above logic, the slave library thinks that there is no synchronization delay, but the trx3 is still not found.

4. Cooperate with semi-sync

To solve the above problem, we need to introduce semi-synchronous replication. Semi-sync made the following design:

1. When the transaction commits, the master sends the binlog to the slave

two。 After receiving the binlog from the library, send an ack back to the main library to indicate that it has received

3. After the main library receives this ack, it can return the confirmation of transaction completion to the client.

If semi-sync is enabled, it means that all transactions that have sent acknowledgements to the client ensure that the slave database has received the log

The scheme of semi-sync+ locus judgment is valid only for one master and one standby scenario. In an one-master-multi-slave scenario, the master database only needs to wait for an ack of the slave library and then returns confirmation to the client. At this point, when a query request is executed on the slave library, there are two situations:

1. If the query falls on this slave library that responds to ack, you can ensure that the latest data is read.

two。 However, if the query falls on other slave libraries, they may not have received the latest log, which will lead to the problem of overdue reading.

There is another potential problem with the scheme of judging synchronization sites, that is, if the sites or GTID sets of the main library update quickly during the peak period of business updates, then the equivalence judgment of the above two sites will not be established all the time, and it is very likely that the query request can not be responded to from the database for a long time.

The figure above, from state 1 to state 4, has been in the state of delaying a transaction. However, in fact, the client initiates the select statement after sending the trx1 update, and we just need to make sure that the trx1 has been executed before we can execute the select statement. That is, if the query request is executed in status 3, the expected result will be obtained.

There are two problems when semi-sync cooperates with master / slave solution without delay:

1. In the case of one master and multiple slaves, there is a phenomenon of overdue reading when executing query requests in some slave libraries.

two。 In the case of persistent delays, there may be the problem of excessive waiting

5. Equal main library site scheme select master_pos_wait (file, pos [, timeout])

The logic of this command is as follows:

1. It is executed from the library

two。 The parameters file and pos refer to the file name and location on the main library

3.timeout is optional. Setting it to a positive integer N means that the function can wait up to N seconds.

The normal result of this command is a positive integer M, which indicates how many transactions have been executed from the beginning of the command to the binlog location represented by file and pos.

1. If an exception occurs in the slave synchronization thread during execution, NULL is returned.

two。 If you wait more than N seconds, return-1

3. If you find that this location has already been executed at the beginning of execution, 0 is returned.

For the logic in the figure above that trx1 is executed first, and then a query request is executed, you can use this logic to ensure that the correct data can be found:

After the 1.trx1 transaction update is completed, execute show master status immediately to get the File and Position executed by the current main library.

two。 Select a slave library to execute the query statement

3. Execute select master_pos_wait (file, pos, 1) on the slave library

4. If the return value is a positive integer > = 0, the query statement is executed in this slave library

5. Otherwise, execute the query statement to the main library

The process is as follows:

6. GTID Scheme select wait_for_executed_gtid_set (gtid_set, 1)

The logic of this command is as follows:

1. Wait until the transaction executed by this library contains the incoming gtid_set and returns 0

two。 Timeout returns 1

In the scheme of main library sites, after executing the transaction, you have to take the initiative to go to the main library to execute show master status. The MySQL5.7.6 version allows the GTID of the transaction to be returned to the client after the update transaction is executed, so that the GTID scheme can reduce one query.

The process of waiting for GTID is as follows:

After the 1.trx1 transaction update is completed, get the GTID of the transaction directly from the return packet, which is marked as gtid1.

two。 Select a slave library to execute the query statement

3. Execute select wait_for_executed_gtid_set (gtid1, 1) on the slave library

4. If the return value is 0, the query statement is executed in this slave library

5. Otherwise, execute the query statement to the main library

This is the end of the introduction of the principle of master / slave, master / slave, and the separation of reading and writing in MySQL. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.