Failover

By default, connection failover handling is left to
the user. The application is responsible for checking return values
of the database functions it calls and reacting to possible errors.
If, for example, the plugin recognizes a query as a read-only query
to be sent to the slave servers, and the slave server selected by
the plugin is not available, the plugin will raise an error after
not executing the statement.

Default: manual
failover

It is up to the application to handle the error
and, if required, re-issue the query to trigger the selection of
another slave server for statement execution. The plugin will make
no attempts to failover automatically, because the plugin cannot
ensure that an automatic failover will not change the state of the
connection. For example, the application may have issued a query
which depends on SQL user variables which are bound to a specific
connection. Such a query might return incorrect results if the
plugin would switch the connection implicitly as part of automatic
failover. To ensure correct results, the application must take care
of the failover, and rebuild the required connection state.
Therefore, by default, no automatic failover is performed by the
plugin.

A user that does not change the connection state
after opening a connection may activate automatic failover. Please
note, that automatic failover logic is limited to connection
attempts. Automatic failover is not used for already established
connections. There is no way to instruct the plugin to attempt
failover on a connection that has been connected to MySQL already
in the past.

Automatic failover

The failover policy is configured in the plugins
configuration file, by using the failover configuration directive.

Automatic and silent failover can be enabled
through the failover configuration directive. Automatic
failover can either be configured to try exactly one master after a
slave failure or, alternatively, loop over slaves and masters
before returning an error to the user. The number of connection
attempts can be limited and failed hosts can be excluded from
future load balancing attempts. Limiting the number of retries and
remembering failed hosts are considered experimental features,
albeit being reasonable stable. Syntax and semantics may change in
future versions.

Please note, since version 1.5.0 automatic failover
is disabled for the duration of a transaction if transaction
stickiness is enabled and transaction boundaries have been
detected. The plugin will not switch connections for the duration
of a transaction. It will also not perform automatic and silent
failover. Instead an error will be thrown. It is then left to the
user to handle the failure of the transaction. Please check, the
trx_stickiness documentation how to do
this.

A basic manual failover example is provided within
the error
handling section.

Standby servers

Using weighted load balancing, introduced in
PECL/mysqlnd 1.4.0, it is possible to configure standby servers
that are sparsely used during normal operations. A standby server
that is primarily used as a worst-case standby failover target can
be assigned a very low weight/priority in relation to all other
servers. As long as all servers are up and running the majority of
the workload is assigned to the servers which have hight weight
values. Few requests will be directed to the standby system which
has a very low weight value.

Upon failure of the servers with a high priority,
you can still failover to the standby, which has been given a low
load balancing priority by assigning a low weight to it. Failover
can be some manually or automatically. If done automatically, you
may want to combine it with the remember_failed option.

At this point, it is not possible to instruct the
load balancer to direct no requests at all to a standby. This may
not be much of a limitation given that the highest weight you can
assign to a server is 65535. Given two slaves, of which one shall
act as a standby and has been assigned a weight of 1, the standby
will have to handle far less than one percent of the overall
workload.

Failover and primary
copy

Please note, if using a primary copy cluster, such
as MySQL Replication, it is difficult to do connection failover in
case of a master failure. At any time there is only one master in
the cluster for a given dataset. The master is a single point of
failure. If the master fails, clients have no target to fail over
write requests. In case of a master outage the database
administrator must take care of the situation and update the client
configurations, if need be.