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

When the slave database is down, all read requests will be transferred to the main database, which will have no impact on the business.

2. When the main library goes down

Currently, only one master library is set in our writehost. When the master library goes down, all read and write requests through mycat will report errors, as shown below:

Read request:

Mysql > select * from T1; ERROR 1184 (HY000): reject connection

Write request:

Mysql > insert into T1 values (2); ERROR 1184 (HY000): reject connection 3 and set multiple writehost to automatically switch write requests to slave database after master database downtime

As you can see above, if only one writehost is set, when the main database is down, it will not automatically switch, and all read and write requests will be affected.

To solve this, we can set up multiple writehost to automatically switch the write request to the slave node when the master library goes down.

Our production environment is one master and one slave, and we can set both nodes to writehost, so that if writehost1 (master library) goes down, mycat will automatically transfer all write requests to writehost2 (slave library).

Original configuration:

[root @ demo-init conf] # cat schema.xml select user ()

Modified configuration:

[root @ demo-init conf] # cat schema.xml select user ()

Only two changes have been made:

A) change switchType= "- 1" to switchType= "1"

Description: switchType:

After this configuration, when the master database goes down, the write request will be automatically transferred to the slave database to ensure the normal availability of the business.

4. What to do when the original main database is restored

When the original master library is restored, it will not be included in the management of mycat (blance=3). In other words, after the original primary library is restored, mycat will not send write requests to the original primary database. It's up to conf/dnindex.properties to decide.

The following is a description of the official mycat documentation, which is excerpted here.

Normally, Mycat will use the first writeHost as the write node, and all DML SQL will be sent to this node. If Mycat enables read-write separation, the query node will execute on readHost (+ writeHost) based on read-write separation policy. When two or more writeHost are configured in a dataHost, if the first writeHost goes down, Mycat will automatically switch to the next available writeHost to execute DML SQL statement after the default 3 heartbeat checks fail. And record the index of the writeHost currently used in the conf/dnindex.properties file (the first is 0, the second is 1, and so on). Note that this file cannot be deleted or changed unless you have a deep understanding of its purpose and your purpose.

So the question is, when the original configuration of the MySQL write node outage recovery, how to rejoin the Mycat, do you want to return to the original write node? On this issue, we have also discussed with DBA for a long time, the final proposal is to keep the existing state unchanged, change the flag, restore the MySQL node as a slave node, follow the new master node, reconfigure master-slave synchronization, other nodes that used to follow this node for synchronization are also changed, reconfigure the synchronization source, and then join the Mycat after the data hands of these nodes are synchronized. At present, version 1.3 of Mycat does not have the function of monitoring the master-slave synchronization status of MySQL, so in this process, DBA can first change the password of MySQL so that Mycat cannot link to the failed server, and then recover the password after the synchronization is completed, so that Mycat will automatically re-enter the repaired Mycat.

In other words, when the master library is restored, master-slave synchronization needs to be manually reconfigured.

5. conditions for the separation of reading and writing.

Read-write separation must meet the following conditions before read operations can be sent to the slave node for execution.

A) readHost must be configured in schema.xml, and the balance configuration is not 0.

B) the SQL statement is select or show.

C) in a non-transaction Of course, it can also be forcibly sent through the comment / # mycat:db_type=slave,. /.

Let's test the performance of select in transactions and in non-transactions.

First explain my environment, 3306 is the master library, 3307 is the slave library.

1. In the non-transaction, you can see that 3307 of the slave database data is queried through mycat:

Mysql > select * from T1; / / slave data is found +-+ | id | +-+ | 3307 | +-+ 1 row in set (0.00 sec)

2. In the transaction, you can see that the data of the 3306 master database is queried through mycat:

Mysql > begin; Query OK, 0 rows affected (0.00 sec) mysql > select * from T1; / / data found in the main database +-+ | id | +-+ | 3306 | +-+ 1 row in set (0.00 sec)

3. Use annotations to force the query in the transaction to read from the library:

Mysql > begin; Query OK, 0 rows affected (0.00 sec) mysql > select * from T1; / / see select read the main library +-+ | id | +-+ | 3306 | +-+ 1 row in set (0.00 sec) mysql > / *! mycat:db_type=slave*/ select * from T1 / / after annotated, select read the slave library +-+ | id | +-+ | 3307 | +-+ 1 row in set (0.01 sec) mysql > select * from T1 in the transaction / / see select read the main library +-+ | id | +-+ | 3306 | +-+ 1 row in set (0.00 sec) 6. Officially recommended mycat read-write separation architecture

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.