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

Read-write separation is an inevitable point to consider when we configure the database cluster, because it can effectively reduce the load of the master database, and in the case of read redundant writes, the vast majority of read requests can be distributed to different slave libraries. Even if the load of the slave library is not enough, the overall query efficiency can be improved by adding the slave library. This article mainly explains how to configure MyCat for read-write separation.

1. Environment building

In order to build a read-write separation database architecture, we need to prepare two databases, one as the master library and the other as the slave database. The approach used in this article is a master-slave architecture built through docker, so it can be run directly on the local machine. The specific configuration is as follows:

Role hostname host ip host port master library mysql-master127.0.0.13306 slave library mysql-slave127.0.0.13307

Readers can read my own article on how docker builds MySQL master-slave database (Docker builds MySQL master-slave cluster). Readers can also build master-slave databases through virtual machines, so I won't repeat them here.

2. MyCat configuration

With regard to the configuration of MyCat, it is mainly necessary to specify both the master library and the slave library on a database node. Here is an example:

Select user ()

With regard to the above configuration, there are mainly the following points to be explained:

The configuration order of xml the order of schema, dataNode and dataHost cannot be changed.

In dataHost, both the master library and slave library are configured through writeHost. MyCat can use show master status to detect which is the master library and which is the slave library.

There are two parameters on dataHost: balance and switchType.

-1 means that when the master is down, the master and slave will not switch automatically.

1 means that when the master is down, the master and slave switch automatically.

2 indicates that when master is down, whether to switch automatically is decided based on the master-slave replication status of MySQL; which of Zhengzhou infertility hospital is more cost-effective: http://yyk.39.net/zz3/zonghe/1d427.html

3 means that when the master is down, the mode based on MySQL galary cluster is automatically switched.

0 means that the read-write separation mechanism is not enabled, and all read and write operations are sent to the writeHost

1 indicates the start of read-write separation mechanism, and all read operations are sent to slave and standby-master. The so-called standby-master refers to the backup master node of the current master.

2 means that all reads are randomly distributed on master and slave

3 means that all reads are distributed to slave, while master and standby-master do not bear read operations

Here balance can specify four values, each of which means as follows:

Here switchType can also specify four values, each of which has the following meaning: https://www.jianshu.com/p/3b9c64fdcec9

During read-write separation, if the master node goes down, the slave node can still provide read operations normally, and an exception will be thrown for write operations sent by the client.

The above configuration is the first way to separate read and write, and another configuration method is to write the slave node inside the master node, thus indicating the dependency. The configuration is as follows:

Select user ()

For this configuration, there are related articles that say that if the master goes down, the slave machine configured in this way cannot provide read service, but according to the author's test, it can still provide read service normally, so there is no significant difference between the two methods.

3. Summary

This paper mainly explains the two ways of building MyCat master-slave mode, and explains in detail the points that should be paid attention to in its configuration.

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.