The plugin is using its own configuration file. The configuration file
holds information on the MySQL replication master server,
the MySQL replication slave servers, the server pick (load balancing) policy,
the failover strategy and the use of lazy connections.
Applications reference sections by their name. Applications use section names
as the host (server) parameter to the various connect methods of the
mysqli,
mysql and
PDO_MYSQL extensions. Upon connect
the mysqlnd plugin compares the hostname
with all section names from the plugin configuration file. If hostname and
section name match, the plugin will load the sections settings.
Example #1 Using section names example
[myapp]
master[] = localhost
slave[] = 192.168.2.27
slave[] = 192.168.2.28:3306
[localhost]
master[] = localhost:/tmp/mysql/mysql.sock
slave[] = 192.168.3.24:3305
slave[] = 192.168.3.65:3309
<?php
/* All of the following connections will be load balanced */
$mysqli = new mysqli("myapp", "username", "password", "database");
$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');
$mysql = mysql_connect("myapp", "username", "password");
$mysqli = new mysqli("localhost", "username", "password", "database");
?>
Section names are strings. It is valid to use a section name such as
192.168.2.1, 127.0.0.1 or
localhost. If, for example, an application
connects to localhost and a plugin
configuration section [localhost] exists, the
semantics of the connect operation are changed. The application will
no longer only use the MySQL server running on the host
localhost but the plugin will start to load balance
MySQL queries following the rules from the [localhost]
configuration section. This way you can load balance queries from
an application without changing the applications source code.
Here is a short explanation of the configuration directives that can be used.
-
master[]
string
-
URI of a MySQL replication master server. The URI follows the syntax
hostname[:port|unix_domain_socket].
The plugin supports using only one master server.
Setting a master server is mandatory. The plugin will report a
warning upon connect if the user has failed to provide a master
server for a configuration section.
The warning may read
(mysqlnd_ms) Cannot find master section in config.
Furthermore the plugin may set an error code for the connection handle such as
HY000/2000 (CR_UNKNOWN_ERROR). The corresponding error
message depends on your language settings.
-
slave[]
string
-
URI of one or more MySQL replication slave servers. The URI follows the syntax
hostname[:port|unix_domain_socket].
The plugin supports using one or more slave servers.
Setting a slave server is mandatory. The plugin will report a
warning upon connect if the user has failed to provide at least one slave
server for a configuration section. The warning may read
(mysqlnd_ms) Cannot find slaves section in config.
Furthermore the plugin may set an error code for the connection handle such as
HY000/2000 (CR_UNKNOWN_ERROR). The corresponding error
message depends on your language settings.
-
pick[]
string
-
Load balancing (server picking) policy. Supported policies:
random, random_once (default),
roundrobin, user.
If no load balancing policy is set, the plugin will default to
random_once. The random_once
policy picks a random slave server when running the first read-only
statement. The slave server will be used for all read-only
statements until the PHP script execution ends.
The random policy will pick a random server whenever
a read-only statement is to be executed.
If using
roundrobin the plugin iterates over the list of
configured slave servers to pick a server for statement execution.
If the plugin reaches the end of the list, it wraps around to the beginning
of the list and picks the first configured slave server.
Setting more than one load balancing policy for a configuration
section makes only sense in conjunction with user
and mysqlnd_ms_set_user_pick_server. If the
user defined callback fails to pick a server, the plugin falls
back to the second configured load balancing policy.
-
failover
string
-
Failover policy. Supported policies:
disabled (default), master.
If no failover policy is set, the plugin will not do any
automatic failover (failover=disabled). Whenever
the plugin fails to connect a server it will emit a warning and
set the connections error code and message. Thereafter it is up to
the application to handle the error and, for example, resent the
last statement to trigger the selection of another server.
If using failover=master the plugin will implicitly
failover to a slave, if available. Please check the
concepts documentation to learn about potential
pitfalls and risks of using failover=master.
-
lazy_connections
bool
-
Controls the use of lazy connections. Lazy connections
are connections which are not opened before the client sends the first
connection.
It is strongly recommended to use lazy connections.
Lazy connections help to keep the number of open connections low.
If you disable lazy connections and, for example, configure one MySQL
replication master server and two MySQL replication slaves, the
plugin will open three connections upon the first call to a
connect function although the application might use the master
connection only.
Lazy connections bare a risk if you make heavy use of actions
which change the state of a connection. The plugin does not dispatch
all state changing actions to all connections from the connection pool.
The few dispatched actions are applied to already opened connections
only. Lazy connections opened in the future are not affected.
If, for example, the connection character set is changed using a
PHP MySQL API call, the plugin will change the character set of all
currently opened connection. It will not remember the character set
change to apply it on lazy connections opened in the future. As a
result the internal connection pool would hold connections using
different character sets. This is not desired. Remember that character
sets are taken into account for escaping.
-
master_on_write
bool
-
If set, the plugin will use the master server only after the
first statement has been executed on the master. Applications
can still send statements to the slaves using SQL hints to
overrule the automatic decision.
The setting may help with replication lag. If an application runs
an INSERT the plugin will, by default, use the
master to execute all following statements, including
SELECT statements. This helps to avoid problems
with reads from slaves which have not replicated the
INSERT yet.
-
trx_stickiness
string
-
Transaction stickiness policy. Supported policies:
disabled (default), master.
Experimental feature.
The setting requires 5.4.0 or newer. If used with PHP older than 5.4.0,
the plugin will emit a warning like
(mysqlnd_ms) trx_stickiness strategy is not supported before PHP 5.3.99.
If no transaction stickiness policy is set or,
if setting trx_stickiness=disabled,
the plugin is not transaction aware. Thus, the plugin may load balance
connections and switch connections in the middle of a transaction.
The plugin is not transaction safe. SQL hints must be used
avoid connection switches during a transaction.
As of PHP 5.4.0 the mysqlnd library allows the plugin to monitor
the autocommit mode set by calls to the
libraries trx_autocommit() function.
If setting trx_stickiness=master and
autocommit gets disabled by a PHP MySQL extension
invoking the mysqlnd library internal
function call trx_autocommit(), the plugin is made
aware of the begin of a transaction. Then, the plugin stops load balancing
and directs all statements to the master server until
autocommit is enabled. Thus, no SQL hints are required.
An example of a PHP MySQL API function calling the mysqlnd
library internal function call trx_autocommit() is
mysqli_autocommit.
Although setting trx_stickiness=master, the plugin
cannot be made aware of autocommit mode changes caused
by SQL statements such as SET AUTOCOMMIT=0.