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

This is the lifetime series of filling pits _ (: lifetime "∠) _

The joint post of Nginx's TCP reverse proxy: http://blog.itpub.net/29510932/viewspace-1842929/

-text-

Background: late stage of lazy cancer, tidy up and send it up.

Environment: MySQL-5.7.9 x 4 Ngingmuri 1.9.7 x 1, five virtual machines

General idea:

Four MySQL instances form a dual-master and double-slave multi-source replication structure. The Nginx is placed at the front end to shield the details of the DB layer from the application layer.

Configuration brief:

The dual-master configuration of MySQL is no different from the ordinary dual-master configuration, and GTID is opened in this construction.

To enable multi-source replication from the library, you need to set-- master-info-repository=TABLE-- relay-log-info-repository=TABLE enable channel for multi-source replication from the library. Just pay attention to the syntax.

Nginx's TCP forwarding function can be found in another blog. The simple configuration of this experiment is shown in screenshot.

Verify:

First, look at the replication and set up a test table.

Insert a few pieces of data randomly and take a look at the status of the slave library

You can see the GTID information of the two master libraries in the status of the slave library.

Question: why does a channel pointing to 67 have GTID information for two main libraries?

Answer the puzzle: take a look at the relay-log message of 67

We can see that relay-log contains the transaction information of the two main libraries at the same time. The reason is that the log-slave-updates is enabled by the two main libraries at the same time, so the transactions of the two main libraries are included in relay-log.

Follow-up question: then the relay-log of channel_67 contains transactions of two main libraries, is it true that the channel of 67 main libraries filters out the logs of 65 main libraries when repeating transactions?

Answer the puzzle: after shutting down the SQL_THREAD of channel_67, execute the statements on the two main libraries, and then take a look at the status of the slave library.

After it was found that the SQL_THREAD of channel_67 was stopped, 67% of the transactions were still updated. By contrast, it was updated by SQL_THREAD of channel_65.

Then stop the SQL_THREAD of 65 and 67 at the same time, and see the effect.

We can basically draw a conclusion: channel's SQL_THREAD does not filter out non-master logs, but faithfully reproduces every transaction recorded in relay-log.

Follow-up question: since both channel perform all the transactions of relay-log, why didn't you report an error?

Answer / speculate: when SQL_THREAD repeats the relay-log, it checks the transactions that have been executed, and skips if it is repeated.

Question: will there be any problems / differences in the synchronization of slave libraries if log-slave-updates is turned off on the dual-master MySQL?

Solution: hands-on testing, close the slave-log-update and then observe the relay-log of the slave library

You can see that there is no transaction information for the main library 65 in relay-log, so take a look at slave status again.

It can be found that each channel no longer receives logs from another main library, but the GTID information of executed transactions is still synchronized.

Conclusion: there is no problem, and each channel handles the transaction information of its own main database separately. For the sake of clear and clear data flow, it is better to turn off slave-log-update in the dual-master configuration.

Extended question: suppose the SQL_THREAD of channel_67 stops for a period of time, so that the insert statement of 67 is not repeated (assuming the insertion value is 18), while the insert of 65 is all repeated (the insertion values are 19 and 21)

Is there an error in the AUTO_INCREMENT counter on the slave library?

After preparing the environment, it is in a state of missing 18, and the effect is as follows

Missing transactions are included in relay-log 's information

Judging from the results, everything ok

The experiment is still in progress, and the Nginx part is left to the bottom part, which owes. _ (: "∠") _.

To be continued--

PS: in version 5.6.x, log-slave-updates must be enabled to enable GTID. By consulting the data, it is inferred that it is required for auto_position, so this option needs to be enabled, but it is no longer a necessary condition in 5.7.9.

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.