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

This article is to share with you about MySQL5.7 multi-source replication and Nginx middleware is how, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, say no more, follow the editor to have a look.

Before, I wrote something to verify multi-source replication, and the second half wrote something about Nginx.

Background: hurry

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. Nginx is placed at the front end to shield DB layer details from the application layer. At the same time, Nginx completes load balancing / read-write separation and "pseudo-HA".

Nginx configuration used

Simply try the function. Specify host when connecting, and use TCP to connect to the Nginx above 61. You can see that you have successfully logged in to MySQL.

Look on the two main libraries and find that this link has been sent to 67

Now that it is connected, after passing a Nginx that is purely used for request forwarding, there should be no problem with the ordinary operation. We should skip the experiment.

There is no essential difference between upstream connected to write and upstream connected to read. The experiment is skipped.

If you use Nginx as middleware to achieve read-write separation, you only need to connect different ports in URL. Skip the experiment.

Multiple connections are connected to the Nginx's write library (the main library) at the same time, and you can see that the connections are distributed to different servers.

Question: what is the status of the client if there is a session connected to a main library, and then that main library goes wrong and dies?

Answer the doubt: the mysql process of the kill main library, simulating the downmachine

Two clients are prompted to reconnect, and the other two are fine.

The status of the surviving master database. You can see that all the requests have been transferred to the surviving master database.

Conclusion: for the client, although the main library that keeps the connection is dead, it looks like the connection timeout has been disconnected from the front end, and the main library with port 13579 will not be felt.

Read the library in the same way and skip verification.

Additional disconnection phenomenon found: the timeout set in Nginx will also have a certain impact. Connected to the status of the figure above, these clients have not been operated. After the timeout, the following error log will be output on the MySQL side

If you re-operate several clients, you will see that all the connections are actually disconnected.

Solution: after the connection is established, after the idle time exceeds the shorter one in the settings of Nginx (proxy_timeout) and MySQL, it will disconnect itself.

Conclusion: like the reverse proxy of Nginx+Tomcat, attention should be paid to the setting of timeout.

Question: after a certain library (take the main library that hung up above as an example) is dead, after the time of fail_timeout, the main library is restored, will it be automatically added back to the list?

Answer: in order to facilitate testing, change the time of fail_timeout, then shut down the main library 67, restart Nginx, start 67 again, and wait for a period of time

The interval is a little longer, but after reopening the connection, you can see that the connection has re-entered, and the setting of fail_timeout is still ok. After this period of time, Nginx will check the condition of the back-end server and re-add the started server.

Conclusion: as a middleware, Nginx can automatically remove the hanging machine / automatically add it back to the recovered machine.

PS: if it is started and the data is being recovered, it is better to remove the problematic library from upstream.

Question: in the configuration of upstream, it is written to forward the connection through hash, so can we use other methods such as weight to forward the connection like the reverse proxy of Nginx+Tomcat?

Answer the puzzle: give it a try; in order to see the effect, simply change the configuration of Nginx

After connecting four clients, take a look at the connection between the two main libraries

You can see that the connections have been forwarded to the 65 main library.

Conclusion: the configuration of weights can be effective, so we can use MySQL instances with different configurations to build this similar architecture according to the actual requirements.

Question: since using Nginx as the middleware, you can "automatically remove the suspended machine / automatically add the recovered machine", and the front end shields the details of the back-end DB architecture, so you won't find that a certain DB is not available. Why should it be described as "pseudo-HA"?

Answer the doubt: from a personal point of view, indeed, accessing the back-end MySQL through the middleware of Nginx does have the look of HA; if a MySQL instance is down, it will not cause the entire DB system to fail to run; failed MySQL instances can be automatically joined after recovery.

However, there are two obvious shortcomings in using Nginx as middleware: the port of Nginx is exposed to the application layer as the only exit, and the HA of Nginx itself needs to be protected in other ways (unlike VIP, even if admin or worker is down, database access will not be affected)

Another more important disadvantage is that after the two master libraries are all dead, Nginx itself cannot elect a new slave database as a new master to rebuild the new master-slave architecture.

These two shortcomings, especially the second deficiency, if it is a very strict HA environment and requirements, this second deficiency is very fatal. If you want to make up for this, you may be able to develop a set of / modify open source tools.

Follow-up question: with such an obvious / serious deficiency, is the multi-source replication + Nginx middleware easy to use?

Answer: from a personal point of view, it's not bad.

For the single-exit problem of 1.Nginx, you can improve availability by launching multiple instances (separately) pointing to the same set of back-end databases (keepalived can also)

two。 The problem that a new Master cannot be elected is that the number of Master can be increased horizontally in the multi-source replication function of 5.7.It depends entirely on more instances to improve availability.

In doing so, the data consistency between N masters may be affected, but you only need to delete these master libraries from the upstream of the reader library.

3. Completely improve the availability by increasing the number of instances, individual instances (regardless of master or slave) fail, basically will not affect the business, the only thing shown is that "some transactions are abnormally interrupted" and need to be retried by the application layer.

Instead of mha, the master library is down and it takes a while to rebuild the master-slave structure.

The above is what the MySQL5.7 multi-source replication and Nginx middleware is like. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.