MySQL 数据库连接Replication协议设置主从链接和从库只读

电玩女神 2021-11-27 01:48 199阅读 0赞

[MySQL Connector/J 8.0 Developer Guide][MySQL Connector_J 8.0 Developer Guide]  /  [Multi-Host Connections][]  /  Configuring Server Failover

## 9.1 Configuring Server Failover ##

MySQL Connector/J supports server failover. A failover happens when connection-related errors occur for an underlying, active connection. The connection errors are, by default, propagated to the client, which has to handle them by, for example, recreating the working objects (`Statement`, `ResultSet`, etc.) and restarting the processes. Sometimes, the driver might eventually fall back to the original host automatically before the client application continues to run, in which case the host switch is transparent and the client application will not even notice it.

A connection using failover support works just like a standard connection: the client does not experience any disruptions in the failover process. This means the client can rely on the same connection instance even if two successive statements might be executed on two different physical hosts. However, this does not mean the client does not have to deal with the exception that triggered the server switch.

The failover is configured at the initial setup stage of the server connection by the connection URL (see explanations for its format [here][]):

jdbc:mysql://[primary host][:port],[secondary host 1][:port][,[secondary host 2][:port]]...[/[database]]»
    [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

The host list in the connection URL comprises of two types of hosts, the primary and the secondary. When starting a new connection, the driver always tries to connect to the primary host first and, if required, fails over to the secondary hosts on the list sequentially when communication problems are experienced. Even if the initial connection to the primary host fails and the driver gets connected to a secondary host, the primary host never loses its special status: for example, it can be configured with an access mode distinct from those of the secondary hosts, and it can be put on a higher priority when a host is to be picked during a failover process.

The failover support is configured by the following connection properties (their functions are explained in the paragraphs below):

*  `failOverReadOnly`
 *  `secondsBeforeRetryMaster`
 *  `queriesBeforeRetryMaster`
 *  `retriesAllDown`
 *  `autoReconnect`
 *  `autoReconnectForPools`

### Configuring Connection Access Mode ###

As with any standard connection, the initial connection to the primary host is in read/write mode. However, if the driver fails to establish the initial connection to the primary host and it automatically switches to the next host on the list, the access mode now depends on the value of the property `failOverReadOnly`, which is “true” by default. The same happens if the driver is initially connected to the primary host and, because of some connection failure, it fails over to a secondary host. Every time the connection falls back to the primary host, its access mode will be read/write, irrespective of whether or not the primary host has been connected to before. The connection access mode can be changed any time at runtime by calling the method` Connection.setReadOnly(boolean)`, which partially overrides the property `failOverReadOnly`. When `failOverReadOnly=false` and the access mode is explicitly set to either true or false, it becomes the mode for every connection after a host switch, no matter what host type are being connected to; but, if `failOverReadOnly=true`, changing the access mode to read/write is only possible if the driver is connecting to the primary host; however, even if the access mode cannot be changed for the current connection, the driver remembers the client's last intention and, when falling back to the primary host, that is the mode that will be used. For an illustration, see the following successions of events with a two-host connection.

*  Sequence A, with `failOverReadOnly=true`:
    
    1.  Connects to primary host in read/write mode
    2.  Sets `Connection.setReadOnly(true)`; primary host now in read-only mode
    3.  Failover event; connects to secondary host in read-only mode
    4.  Sets `Connection.setReadOnly(false)`; secondary host remains in read-only mode
    5.  Falls back to primary host; connection now in read/write mode
    
     
 *  Sequence B, with `failOverReadOnly=false`
    
    1.  Connects to primary host in read/write mode
    2.  Sets `Connection.setReadOnly(true)`; primary host now in read-only mode
    3.  Failover event; connects to secondary host in read-only mode
    4.  Set `Connection.setReadOnly(false)`; connection to secondary host switches to read/write mode
    5.  Falls back to primary host; connection now in read/write mode

The difference between the two scenarios is in step 4: the access mode for the secondary host in sequence A does not change at that step, but the driver remembers and uses the set mode when falling back to the primary host, which would be read-only otherwise; but in sequence B, the access mode for the secondary host changes immediately.

### Configuring Fallback to Primary Host ###

As already mentioned, the primary host is special in the failover arrangement when it comes to the host's access mode. Additionally, the driver tries to fall back to the primary host as soon as possible by default, even if no communication exception occurs. Two properties, `secondsBeforeRetryMaster` and `queriesBeforeRetryMaster`, determine when the driver is ready to retry a reconnection to the primary host (the `Master` in the property names stands for the primary host of our connection URL, which is not necessarily a master host in a replication setup):

*  `secondsBeforeRetryMaster` determines how much time the driver waits before trying to fall back to the primary host
 *  `queriesBeforeRetryMaster` determines the number of queries that are executed before the driver tries to fall back to the primary host. Note that for the driver, each call to a `Statement.execute*()` method increments the query execution counter; therefore, when calls are made to `Statement.executeBatch()` or if`allowMultiQueries` or `rewriteBatchStatements` are enabled, the driver may not have an accurate count of the actual number of queries executed on the server. Also, the driver calls the `Statement.execute*()` methods internally in several occasions. All these mean you can only use `queriesBeforeRetryMaster` only as a coarse specification for when to fall back to the primary host.

In general, an attempt to fallback to the primary host is made when at least one of the conditions specified by the two properties is met, and the attempt always takes place at transaction boundaries. However, if auto-commit is turned off, the check happens only when the method `Connection.commit()` or `Connection.rollback()` is called. The automatic fallback to the primary host can be turned off by setting simultaneously `secondsBeforeRetryMaster` and `queriesBeforeRetryMaster` to “0”. Setting only one of the properties to “0” only disables one part of the check.

### Configuring Reconnection Attempts ###

When establishing a new connection or when a failover event occurs, the driver tries to connect successively to the next candidate on the host list. When the end of the list has been reached, it restarts all over again from the beginning of the list; however, the primary host is skipped over, if (a) NOT all the secondary hosts have already been tested at least once, AND (b) the fallback conditions defined by `secondsBeforeRetryMaster` and `queriesBeforeRetryMaster` are not yet fulfilled. Each run-through of the whole host list, (which is not necessarily completed at the end of the host list) counts as a single connection attempt. The driver tries as many connection attempts as specified by the value of the property `retriesAllDown`.

### Seamless Reconnection ###

Although not recommended, you can make the driver perform failovers without invalidating the active `Statement` or `ResultSet` instances by setting either the parameter`autoReconnect` or `autoReconnectForPools` to `true`. This allows the client to continue using the same object instances after a failover event, without taking any exceptional measures. This, however, may lead to unexpected results: for example, if the driver is connected to the primary host with read/write access mode and it fails-over to a secondary host in real-only mode, further attempts to issue data-changing queries will result in errors, and the client will not be aware of that. This limitation is particularly relevant when using data streaming: after the failover, the `ResultSet` looks to be alright, but the underlying connection may have changed already, and no backing cursor is available anymore.

\-------------------------------------------------------------------

来源：MySQL官方文档

链接：[https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-config-failover.html][https_dev.mysql.com_doc_connector-j_8.0_en_connector-j-config-failover.html]

[MySQL Connector_J 8.0 Developer Guide]: https://dev.mysql.com/doc/connector-j/8.0/en/
[Multi-Host Connections]: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-multi-host-connections.html
[here]: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html
[https_dev.mysql.com_doc_connector-j_8.0_en_connector-j-config-failover.html]: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-config-failover.html