The following documentation applies to PECL/mysqlnd_ms >= 1.1.0-beta. It is not valid for prior versions. For documentation covering earlier versions, see the configuration documentation for mysqlnd_ms 1.0.x and below.
Note: Changelog: Feature was added in PECL/mysqlnd_ms 1.1.0-beta
The below description applies to PECL/mysqlnd_ms >= 1.1.0-beta. It is not valid for prior versions.
The plugin uses its own configuration file. The configuration file holds information about 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.
The plugin loads its configuration file at the beginning of a web request. It is then cached in memory and used for the duration of the web request. This way, there is no need to restart PHP after deploying the configuration file. Configuration file changes will become active almost instantly.
The PHP configuration directive
mysqlnd_ms.config_file
is used to set the plugins configuration file. Please note, that
the PHP configuration directive may not be evaluated for every web request.
Therefore, changing the plugins configuration file name or location may
require a PHP restart. However, no restart is required to read changes if
an already existing plugin configuration file is updated.
Using and parsing JSON is efficient, and using JSON makes it easier to express hierarchical data structures than the standard php.ini format.
Example #1 Converting a PHP array (hash) into JSON format
Or alternatively, a developer may be more familiar with the PHP array syntax, and prefer it. This example demonstrates how a developer might convert a PHP array to JSON.
<?php
$config = array(
"myapp" => array(
"master" => array(
"master_0" => array(
"host" => "localhost",
"socket" => "/tmp/mysql.sock",
),
),
"slave" => array(),
),
);
file_put_contents("mysqlnd_ms.ini", json_encode($config, JSON_PRETTY_PRINT));
printf("mysqlnd_ms.ini file created...\n");
printf("Dumping file contents...\n");
printf("%s\n", str_repeat("-", 80));
echo file_get_contents("mysqlnd_ms.ini");
printf("\n%s\n", str_repeat("-", 80));
?>
以上例程会输出：
mysqlnd_ms.ini file created...
Dumping file contents...
--------------------------------------------------------------------------------
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": [
]
}
}
--------------------------------------------------------------------------------
A plugin configuration file consists of one or more sections. Sections are represented by the top-level object properties of the object encoded in the JSON file. Sections could also be called configuration names.
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 of the section names from the plugin configuration file. If the hostname and section name match, then the plugin will load the settings for that section.
Example #2 Using section names example
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27"
},
"slave_1": {
"host": "192.168.2.27",
"port": 3306
}
}
},
"localhost": {
"master": [
{
"host": "localhost",
"socket": "\/path\/to\/mysql.sock"
}
],
"slave": [
{
"host": "192.168.3.24",
"port": "3305"
},
{
"host": "192.168.3.65",
"port": "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.
Please keep in mind, that such a configuration may not contribute
to overall readability of your applications source code. Using section names
that can be mixed up with host names should be seen as a last resort.
Each configuration section contains, at a minimum, a list of master servers
and a list of slave servers. The master list is configured with the keyword
master, while the slave list is configured with the
slave keyword. Failing to provide a slave list will result
in a fatal E_ERROR level error, although a slave list
may be empty. It is possible to allow no slaves. However, this is only recommended
with synchronous clusters, please see also
supported clusters.
The main part of the documentation focusses on the use
of asynchronous MySQL replication clusters.
The master and slave server lists can be optionally indexed by symbolic names for the servers they describe. Alternatively, an array of descriptions for slave and master servers may be used.
Example #3 List of anonymous slaves
"slave": [
{
"host": "192.168.3.24",
"port": "3305"
},
{
"host": "192.168.3.65",
"port": "3309"
}
]
An anonymous server list is encoded by the JSON array type.
Optionally, symbolic names may be used for indexing the slave or master servers
of a server list, and done so using the JSON object type.
Example #4 Master list using symbolic names
"master": {
"master_0": {
"host": "localhost"
}
}
It is recommended to index the server lists with symbolic server names. The alias names will be shown in error messages.
The order of servers is preserved and taken into account by mysqlnd_ms.
If, for example, you configure round robin load balancing strategy, the
first SELECT statement will be executed on the
slave that appears first in the slave server list.
A configured server can be described with the host,
port, socket, db,
user, password and connect_flags.
It is mandatory to set the database server host using the host
keyword. All other settings are optional.
Example #5 Keywords to configure a server
{
"myapp": {
"master": {
"master_0": {
"host": "db_server_host",
"port": "db_server_port",
"socket": "db_server_socket",
"db": "database_resp_schema",
"user": "user",
"password": "password",
"connect_flags": 0
}
},
"slave": {
"slave_0": {
"host": "db_server_host",
"port": "db_server_port",
"socket": "db_server_socket"
}
}
}
}
If a setting is omitted, the plugin will use the value provided by the user API call used to open a connection. Please, see the using section names example above.
The configuration file format has been changed in version 1.1.0-beta to allow for
chained filters. Filters are responsible for filtering the configured list of
servers to identify a server for execution of a given statement.
Filters are configured with the filter keyword. Filters
are executed by mysqlnd_ms in the order of their appearance.
Defining filters is optional. A configuration section in the plugins
configuration file does not need to have a filters entry.
Filters replace the
pick[]
setting from prior versions. The new random and
roundrobin provide the same functionality.
Example #6 New roundrobin filter, old functionality
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
},
"slave_1": {
"host": "192.168.78.137",
"port": "3306"
}
},
"filters": {
"roundrobin": [
]
}
}
}
The function
mysqlnd_ms_set_user_pick_server()
has been removed. Setting a callback is now done with the user
filter. Some filters accept parameters. The user filter
requires and accepts a mandatory callback parameter
to set the callback previously set through the function mysqlnd_ms_set_user_pick_server().
Example #7 The user filter replaces mysqlnd_ms_set_user_pick_server()
"filters": {
"user": {
"callback": "pick_server"
}
}
The validity of the configuration file is checked both when reading the configuration file and later when establishing a connection. The configuration file is read during PHP request startup. At this early stage a PHP extension may not display error messages properly. In the worst case, no error is shown and a connection attempt fails without an adequate error message. This problem has been cured in version 1.5.0.
Example #8 Common error message in case of configuration file issues (upto version 1.5.0)
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
?>
以上例程会输出：
Warning: mysqli::mysqli(): (mysqlnd_ms) (mysqlnd_ms) Failed to parse config file [s1.json]. Please, verify the JSON in Command line code Warning: mysqli::mysqli(): (HY000/2002): php_network_getaddresses: getaddrinfo failed: Name or service not known in Command line code on line 1 Warning: mysqli::query(): Couldn't fetch mysqli in Command line code on line 1 Fatal error: Call to a member function fetch_assoc() on a non-object in Command line code on line 1
Since version 1.5.0 startup errors are additionally buffered and emitted when
a connection attempt is made. Use the configuration directive
mysqlnd_ms.force_config_usage
to set the error type used to display buffered errors. By default an error
of type E_WARNING will be emitted.
Example #9 Improved configuration file validation since 1.5.0
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
?>
以上例程会输出：
Warning: mysqli::mysqli(): (mysqlnd_ms) (mysqlnd_ms) Failed to parse config file [s1.json]. Please, verify the JSON in Command line code on line 1
It can be useful to set mysqlnd_ms.force_config_usage = 1
when debugging potential configuration file errors. This will not only turn the
type of buffered startup errors into E_RECOVERABLE_ERROR but also
help detecting misspelled section names.
Example #10 Possibly more precise error due to mysqlnd_ms.force_config_usage=1
mysqlnd_ms.force_config_usage=1
<?php
$mysqli = new mysqli("invalid_section", "username", "password", "database");
?>
以上例程会输出：
Warning: mysqli::mysqli(): (mysqlnd_ms) Exclusive usage of configuration enforced but did not find the correct INI file section (invalid_section) in Command line code on line 1 line 1
Here is a short explanation of the configuration directives that can be used.
master
array or object
List of MySQL replication master servers. The list of either
of the JSON type array to declare an anonymous list
of servers or of the JSON type object. Please,
see above
for examples.
Setting at least one master server is mandatory. The plugin will issue an
error of type E_ERROR if the user has failed to
provide a master server list for a configuration section.
The fatal error may read
(mysqlnd_ms) Section [master] doesn't exist for host
[name_of_a_config_section] in %s on line %d.
A server is described with
the host, port,
socket, db,
user, password and
connect_flags. It is mandatory to
provide at a value for host. If any of the
other values is not given, it will be taken from the user
API connect call, please, see also:
using section names example.
Table of server configuration keywords.
| Keyword | Description | Version |
|---|---|---|
host
|
Database server host. This is a mandatory setting.
Failing to provide, will cause an error of type |
Since 1.1.0. |
port
|
Database server TCP/IP port. |
Since 1.1.0. |
socket
|
Database server Unix domain socket. |
Since 1.1.0. |
db
|
Database (schemata). |
Since 1.1.0. |
user
|
MySQL database user. |
Since 1.1.0. |
password
|
MySQL database user password. |
Since 1.1.0. |
connect_flags
|
Connection flags. |
Since 1.1.0. |
The plugin supports using only one master server. An experimental setting exists to enable multi-master support. The details are not documented. The setting is meant for development only.
slave
array or object
List of one or more MySQL replication slave servers. The syntax is
identical to setting master servers, please, see
master
above for details.
The plugin supports using one or more slave servers.
Setting a list of slave servers is mandatory. The plugin will report
an error of the type E_ERROR if slave
is not given for a configuration section. The fatal error message may read
(mysqlnd_ms) Section [slave] doesn't exist for host [%s] in %s on line %d.
Note, that it is valid to use an empty slave server list.
The error has been introduced to prevent accidently setting no slaves by
forgetting about the slave setting.
A master-only setup is still possible using an empty slave server list.
If an empty slave list is configured and an attempt is made to
execute a statement on a slave the plugin may emit a warning like
mysqlnd_ms) Couldn't find the appropriate slave connection.
0 slaves to choose from. upon statement execution.
It is possible that another warning follows such as
(mysqlnd_ms) No connection selected by the last filter.
global_transaction_id_injection
array or object
Global transaction identifier configuration related to both the use of the server built-in global transaction ID feature and the client-side emulation.
| Keyword | Description | Version |
|---|---|---|
fetch_last_gtid
|
SQL statement for accessing the latest global transaction identifier. The SQL statement is run if the plugin needs to know the most recent global transaction identifier. This can be the case, for example, when checking MySQL Replication slave status. Also used with mysqlnd_ms_get_last_gtid(). |
Since 1.2.0. |
check_for_gtid
|
SQL statement for checking if a replica has replicated
all transactions up to and including ones searched for. The
SQL statement is run when searching for replicas which can offer
a higher level of consistency than eventual consistency.
The statement must contain a placeholder |
Since 1.2.0. |
report_errors
|
Whether to emit an error of type warning if an issue occurs while executing any of the configured SQL statements. |
Since 1.2.0. |
on_commit
|
Client-side global transaction ID emulation only. SQL statement to run when a transaction finished to update the global transaction identifier sequence number on the master. Please, see the quickstart for examples. |
Since 1.2.0. |
wait_for_gtid_timeout
|
Instructs the plugin to wait up to The setting can be used both with the plugins client-side emulation and the server-side global transaction identifier feature of MySQL 5.6. Waiting for a slave to replicate a certain GTID needed for session consistency also means throttling the client. By throttling the client the write load on the master is reduced indirectly. A primary copy based replication system, such as MySQL Replication, is given more time to reach a consistent state. This can be desired, for example, to increase the number of data copies for high availability considerations or to prevent the master from being overloaded. |
Since 1.4.0. |
fabric
object
MySQL Fabric related settings. If the plugin is used together with MySQL Fabric, then the plugins configuration file no longer contains lists of MySQL servers. Instead, the plugin will ask MySQL Fabric which list of servers to use to perform a certain task.
A minimum plugin configuration for use with MySQL Fabric contains a list of one or more MySQL Fabric hosts that the plugin can query. If more than one MySQL Fabric host is configured, the plugin will use a roundrobin strategy to choose among them. Other strategies are currently not available.
Example #11 Minimum pluging configuration for use with MySQL Fabric
{
"myapp": {
"fabric": {
"hosts": [
{
"host" : "127.0.0.1",
"port" : 8080
}
]
}
}
}
Each MySQL Fabric host is described using a JSON object with the following members.
| Keyword | Description | Version |
|---|---|---|
host
|
Host name of the MySQL Fabric host. |
Since 1.6.0. |
port
|
The TCP/IP port on which the MySQL Fabric host listens for remote procedure calls sent by clients such as the plugin. |
Since 1.6.0. |
The plugin is using PHP streams to communicate with MySQL Fabric through XML RPC over HTTP. By default no timeouts are set for the network communication. Thus, the plugin defaults to PHP stream default timeouts. Those defaults are out of control of the plugin itself.
An optional timeout value can be set to overrule the PHP streams default timeout setting. Setting the timeout in the plugins configuration file has the same effect as setting a timeout for a PHP user space HTTP connection established through PHP streams.
The plugins Fabric timeout value unit is seconds. The allowed value range is from 0 to 65535. The setting exists since version 1.6.
Example #12 Optional timeout for communication with Fabric
{
"myapp": {
"fabric": {
"hosts": [
{
"host" : "127.0.0.1",
"port" : 8080
}
],
"timeout": 2
}
}
}
Transaction stickiness and MySQL Fabric logic can collide. The stickiness option disables switching between servers for the duration of a transaction. When using Fabric and sharding the user may (erroneously) start a local transaction on one share and then attempt to switch to a different shard using either mysqlnd_ms_fabric_select_shard() or mysqlnd_ms_fabric_select_global(). In this case, the plugin will not reject the request to switch servers in the middle of a transaction but allow the user to switch to another server regardless of the transaction stickiness setting used. It is clearly a user error to write such code.
If transaction stickiness is enabled and you would like to get an error of type
warning when calling
mysqlnd_ms_fabric_select_shard() or
mysqlnd_ms_fabric_select_global(),
set the boolean flag trx_warn_server_list_changes.
Example #13 Warnings about the violation of transaction boundaries
{
"myapp": {
"fabric": {
"hosts": [
{
"host" : "127.0.0.1",
"port" : 8080
}
],
"trx_warn_serverlist_changes": 1
},
"trx_stickiness": "on"
}
}
<?php
$link = new mysqli("myapp", "root", "", "test");
/*
For the demo the call may fail.
Failed or not we get into the state
needed for the example.
*/
@mysqlnd_ms_fabric_select_global($link, 1);
$link->begin_transaction();
@$link->query("DROP TABLE IF EXISTS test");
/*
Switching servers/shards is a mistake due to open
local transaction!
*/
mysqlnd_ms_select_global($link, 1);
?>
以上例程会输出：
PHP Warning: mysqlnd_ms_fabric_select_global(): (mysqlnd_ms) Fabric server exchange in the middle of a transaction in %s on line %d
Please, consider the feature experimental. Changes to syntax and semantics may happen.
filters
object
List of filters. A filter is responsible to filter the list of available
servers for executing a given statement. Filters can be chained.
The random and roundrobin filter
replace the
pick[]
directive used in prior version to select a load balancing policy.
The user filter replaces the
mysqlnd_ms_set_user_pick_server() function.
Filters may accept parameters to refine their actions.
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. No load balancing
policy is set and thus, defaulting takes place,
if neither the random nor the
roundrobin are part of a configuration section.
If a filter chain is configured so that a filter which output no
more than once server is used as input for a filter which should be given
more than one server as input, the plugin may emit a warning upon
opening a connection. The warning may read: (mysqlnd_ms) Error while creating
filter '%s' . Non-multi filter '%s' already created.
Stopping in %s on line %d. Furthermore, an error of
the error code 2000, the sql state HY000
and an error message similar to the warning may be set on the connection
handle.
Example #14 Invalid filter sequence
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"filters": [
"roundrobin",
"random"
]
}
}
<?php
$link = new mysqli("myapp", "root", "", "test");
printf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error());
$link->query("SELECT 1 FROM DUAL");
?>
以上例程会输出：
PHP Warning: mysqli::mysqli(): (HY000/2000): (mysqlnd_ms) Error while creating filter 'random' . Non-multi filter 'roundrobin' already created. Stopping in filter_warning.php on line 1 [2000] (mysqlnd_ms) Error while creating filter 'random' . Non-multi filter 'roundrobin' already created. Stopping PHP Warning: mysqli::query(): Couldn't fetch mysqli in filter_warning.php on line 3
random
object
The random filter features the random and random once
load balancing policies, set through the
pick[]
directive in older versions.
The random policy will pick a random server whenever a read-only statement is to be executed. The random once strategy picks a random slave server once and continues using the slave for the rest of the PHP web request. Random once is a default, if load balancing is not configured through a filter.
If the random filter is not given any arguments, it
stands for random load balancing policy.
Example #15 Random load balancing with random filter
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
},
"slave_1": {
"host": "192.168.78.137",
"port": "3306"
}
},
"filters": [
"random"
]
}
}
Optionally, the sticky argument can be passed to the
filter. If the parameter sticky is set to the string
1, the filter follows the random once
load balancing strategy.
Example #16 Random once load balancing with random filter
{
"filters": {
"random": {
"sticky": "1"
}
}
}
Both the random and roundrobin
filters support setting a priority, a weight for a server, since
PECL/mysqlnd_ms 1.4.0. If the weight argument is
passed to the filter, it must assign a weight for all servers. Servers
must be given an alias name in the slave respectively
master server lists. The alias must be used
to reference servers for assigning a priority with weight.
Example #17 Referencing error
[E_RECOVERABLE_ERROR] mysqli_real_connect(): (mysqlnd_ms) Unknown server 'slave3' in 'random' filter configuration. Stopping in %s on line %d
Using a wrong alias name with weight may result in
an error similar to the shown above.
If weight is omitted, the default weight of
all servers is one.
Example #18 Assigning a weight for load balancing
{
"myapp": {
"master": {
"master1":{
"host":"localhost",
"socket":"\/var\/run\/mysql\/mysql.sock"
}
},
"slave": {
"slave1": {
"host":"192.168.2.28",
"port":3306
},
"slave2": {
"host":"192.168.2.29",
"port":3306
},
"slave3": {
"host":"192.0.43.10",
"port":3306
},
},
"filters": {
"random": {
"weights": {
"slave1":8,
"slave2":4,
"slave3":1,
"master1":1
}
}
}
}
}
At the average a server assigned a weight of two will be selected twice
as often as a server assigned a weight of one. Different weights can be
assigned to reflect differently sized machines, to prefer co-located slaves
which have a low network latency or, to configure a standby failover server.
In the latter case, you may want to assign the standby server a very low
weight in relation to the other servers. For example, given the
configuration above slave3 will get only some eight
percent of the requests in the average. As long as slave1
and slave2 are running, it will be used sparsely,
similar to a standby failover server. Upon failure of slave1
and slave2, the usage of slave3
increases. Please, check the notes on failover before using
weight this way.
Valid weight values range from 1 to 65535.
Unknown arguments are ignored by the filter. No warning or error is given.
The filter expects one or more servers as input. Outputs one server.
A filter sequence such as
random, roundrobin may
cause a warning and an error message to be set on the connection
handle when executing a statement.
List of filter arguments.
| Keyword | Description | Version |
|---|---|---|
sticky
|
Enables or disabled random once load balancing policy. See above. |
Since 1.2.0. |
weight
|
Assigns a load balancing weight/priority to a server. Please, see above for a description. |
Since 1.4.0. |
roundrobin
object
If using the roundrobin filter, 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.
Example #19 roundrobin filter
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"filters": [
"roundrobin"
]
}
}
Expects one or more servers as input. Outputs one server.
A filter sequence such as
roundrobin, random may
cause a warning and an error message to be set on the connection
handle when executing a statement.
List of filter arguments.
| Keyword | Description | Version |
|---|---|---|
weight
|
Assigns a load balancing weight/priority to a server. Please, find a description above. |
Since 1.4.0. |
user
object
The user replaces
mysqlnd_ms_set_user_pick_server() function,
which was removed in 1.1.0-beta. The filter sets a callback for user-defined
read/write splitting and server selection.
The plugins built-in read/write query split mechanism decisions can be
overwritten in two ways. The easiest way is to prepend a query string
with the SQL hints MYSQLND_MS_MASTER_SWITCH,
MYSQLND_MS_SLAVE_SWITCH or
MYSQLND_MS_LAST_USED_SWITCH. Using SQL hints one can
control, for example, whether a query shall be send to the MySQL replication
master server or one of the slave servers. By help of SQL hints it is
not possible to pick a certain slave server for query execution.
Full control on server selection can be gained using a callback function. Use of a callback is recommended to expert users only because the callback has to cover all cases otherwise handled by the plugin.
The plugin will invoke the callback function for selecting a server from the lists of configured master and slave servers. The callback function inspects the query to run and picks a server for query execution by returning the hosts URI, as found in the master and slave list.
If the lazy connections are enabled and the callback chooses a slave server for which no connection has been established so far and establishing the connection to the slave fails, the plugin will return an error upon the next action on the failed connection, for example, when running a query. It is the responsibility of the application developer to handle the error. For example, the application can re-run the query to trigger a new server selection and callback invocation. If so, the callback must make sure to select a different slave, or check slave availability, before returning to the plugin to prevent an endless loop.
Example #20 Setting a callback
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"filters": {
"user": {
"callback": "pick_server"
}
}
}
}
The callback is supposed to return a host to run the query on.
The host URI is to be taken from the master and slave connection lists
passed to the callback function. If callback returns a value neither
found in the master nor in the slave connection lists the plugin
will emit an error of the type E_RECOVERABLE_ERROR
The error may read like
(mysqlnd_ms) User filter callback has returned an unknown server.
The server 'server that is not in master or slave list' can neither be found
in the master list nor in the slave list.
If the application catches the error to ignore it, follow up errors
may be set on the connection handle, for example,
(mysqlnd_ms) No connection selected by the last filter with
the error code 2000 and the sqlstate HY000.
Furthermore a warning may be emitted.
Referencing a non-existing function as a callback will result
in any error of the type E_RECOVERABLE_ERROR whenever
the plugin tries to callback function. The error message may reads like:
(mysqlnd_ms) Specified callback (pick_server) is
not a valid callback. If the application catches the error to
ignore it, follow up errors may be set on the connection handle, for example,
(mysqlnd_ms) Specified callback (pick_server) is
not a valid callback with the error code 2000
and the sqlstate HY000. Furthermore a warning
may be emitted.
The following parameters are passed from the plugin to the callback.
| Parameter | Description | Version |
|---|---|---|
connected_host
|
URI of the currently connected database server. |
Since 1.1.0. |
query
|
Query string of the statement for which a server needs to be picked. |
Since 1.1.0. |
masters
|
List of master servers to choose from. Note, that the list of master servers may not be identical to the list of configured master servers if the filter is not the first in the filter chain. Previously run filters may have reduced the master list already. |
Since 1.1.0. |
slaves
|
List of slave servers to choose from. Note, that the list of master servers may not be identical to the list of configured master servers if the filter is not the first in the filter chain. Previously run filters may have reduced the master list already. |
Since 1.1.0. |
last_used_connection
|
URI of the server of the connection used to execute the previous statement on. |
Since 1.1.0. |
in_transaction
|
Boolean flag indicating whether the statement is
part of an open transaction. If autocommit mode is turned
off, this will be set to
Transaction detection is based on monitoring the
mysqlnd library call |
Since 1.1.0. |
Example #21 Using a callback
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27",
"port": "3306"
},
"slave_1": {
"host": "192.168.78.136",
"port": "3306"
}
},
"filters": {
"user": {
"callback": "pick_server"
}
}
}
}
<?php
function pick_server($connected, $query, $masters, $slaves, $last_used_connection, $in_transaction)
{
static $slave_idx = 0;
static $num_slaves = NULL;
if (is_null($num_slaves))
$num_slaves = count($slaves);
/* default: fallback to the plugins build-in logic */
$ret = NULL;
printf("User has connected to '%s'...\n", $connected);
printf("... deciding where to run '%s'\n", $query);
$where = mysqlnd_ms_query_is_select($query);
switch ($where)
{
case MYSQLND_MS_QUERY_USE_MASTER:
printf("... using master\n");
$ret = $masters[0];
break;
case MYSQLND_MS_QUERY_USE_SLAVE:
/* SELECT or SQL hint for using slave */
if (stristr($query, "FROM table_on_slave_a_only"))
{
/* a table which is only on the first configured slave */
printf("... access to table available only on slave A detected\n");
$ret = $slaves[0];
}
else
{
/* round robin */
printf("... some read-only query for a slave\n");
$ret = $slaves[$slave_idx++ % $num_slaves];
}
break;
case MYSQLND_MS_QUERY_LAST_USED:
printf("... using last used server\n");
$ret = $last_used_connection;
break;
}
printf("... ret = '%s'\n", $ret);
return $ret;
}
$mysqli = new mysqli("myapp", "root", "", "test");
if (!($res = $mysqli->query("SELECT 1 FROM DUAL")))
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
else
$res->close();
if (!($res = $mysqli->query("SELECT 2 FROM DUAL")))
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
else
$res->close();
if (!($res = $mysqli->query("SELECT * FROM table_on_slave_a_only")))
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
else
$res->close();
$mysqli->close();
?>
以上例程会输出：
User has connected to 'myapp'... ... deciding where to run 'SELECT 1 FROM DUAL' ... some read-only query for a slave ... ret = 'tcp://192.168.2.27:3306' User has connected to 'myapp'... ... deciding where to run 'SELECT 2 FROM DUAL' ... some read-only query for a slave ... ret = 'tcp://192.168.78.136:3306' User has connected to 'myapp'... ... deciding where to run 'SELECT * FROM table_on_slave_a_only' ... access to table available only on slave A detected ... ret = 'tcp://192.168.2.27:3306'
user_multi
object
The user_multi differs from the user
only in one aspect. Otherwise, their syntax is identical.
The user filter must pick
and return exactly one node for statement execution. A filter chain
usually ends with a filter that emits only one node. The filter chain
shall reduce the list of candidates for statement execution down to
one. This, only one node left, is the case after the user
filter has been run.
The user_multi filter is a multi filter. It returns a
list of slave and a list of master servers. This list needs further filtering
to identify exactly one node for statement execution. A multi filter is typically
placed at the top of the filter chain. The quality_of_service
filter is another example of a multi filter.
The return value of the callback set for user_multi must
be an array with two elements. The first element holds a list of selected master
servers. The second element contains a list of selected slave servers. The
lists shall contain the keys of the slave and master servers as found in
the slave and master lists passed to the callback. The below example returns
random master and slave lists extracted from the functions input.
Example #22 Returning random masters and slaves
<?php
function pick_server($connected, $query, $masters, $slaves, $last_used_connection, $in_transaction)
{
$picked_masters = array()
foreach ($masters as $key => $value) {
if (mt_rand(0, 2) > 1)
$picked_masters[] = $key;
}
$picked_slaves = array()
foreach ($slaves as $key => $value) {
if (mt_rand(0, 2) > 1)
$picked_slaves[] = $key;
}
return array($picked_masters, $picked_slaves);
}
?>
The plugin will issue an
error of type E_RECOVERABLE if the callback fails to return
a server list. The error may read (mysqlnd_ms) User multi
filter callback has not returned a list of servers to use.
The callback must return an array in %s on line %d. In case the
server list is not empty but has invalid servers key/ids in it, an error
of type E_RECOVERABLE will the thrown with an
error message like (mysqlnd_ms) User multi filter callback
has returned an invalid list of servers to use.
Server id is negative in %s on line %d, or similar.
Whether an error is emitted in case of an empty slave or master list
depends on the configuration. If an empty master list is returned
for a write operation, it is likely that the plugin will emit a
warning that may read (mysqlnd_ms) Couldn't find the appropriate
master connection. 0 masters to choose from. Something is wrong in %s on line %d.
Typically a follow up error of type E_ERROR will happen.
In case of a read operation and an empty slave list the behavior depends
on the fail over configuration. If fail over to master is enabled, no
error should appear. If fail over to master is deactivated the plugin will
emit a warning that may read (mysqlnd_ms) Couldn't find the appropriate
slave connection. 0 slaves to choose from. Something is wrong in %s on line %d.
node_groups
object
The node_groups filter lets you group cluster nodes
and query selected groups, for example, to support data partitioning.
Data partitioning can be required for manual sharding, primary copy based
clusters running multiple masters, or to avoid hot spots in update everywhere
clusters that have no built-in partitioning. The filter is a multi filter
which returns zero, one or multiple of its input servers. Thus, it
must be followed by other filters to reduce the number of candidates
down to one for statement execution.
| Keyword | Description | Version |
|---|---|---|
user defined node group name
|
One or more node groups must be defined. A node group can have an
arbitrary user defined name. The name is used in combination with
a SQL hint to restrict query execution to the nodes listed for the
node group. To run a query on any of the servers of a node group,
the query must begin with the SQL hint
Each node group entry must contain a list of
The list of master and slave servers must reference corresponding
entries in the
global master
respectively slave
server list. Referencing an unknown server in either of the both server
lists may cause an
Example #23 Manual partitioning {
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.28",
"port": 3306
},
"slave_1": {
"host": "127.0.0.1",
"port": 3311
}
},
"filters": {
"node_groups": {
"Partition_A" : {
"master": ["master_0"],
"slave": ["slave_0"]
}
},
"roundrobin": []
}
}
}
Please note, if a filter chain
generates an empty slave list and the PHP configuration directive
|
Since 1.5.0. |
quality_of_service
object
The quality_of_service identifies cluster nodes
capable of delivering a certain quality of service. It is a multi filter
which returns zero, one or multiple of its input servers. Thus, it
must be followed by other filters to reduce the number of candidates
down to one for statement execution.
The quality_of_service filter has been introduced in 1.2.0-alpha.
In the 1.2 series the filters focus is on the consistency aspect of
service quality. Different types of clusters offer different
default data consistencies. For example, an asynchronous MySQL
replication slave offers eventual consistency. The slave may not
be able to deliver requested data because it has not replicated the write,
it may serve stale database because its lagging behind or it may serve
current information. Often, this is acceptable. In some cases
higher consistency levels are needed for the application to work correct.
In those cases, the quality_of_service can filter out cluster nodes
which cannot deliver the necessary quality of service.
The quality_of_service filter can be replaced or created
at runtime. A successful call to
mysqlnd_ms_set_qos()
removes all existing qos filter entries from the
filter list and installs a new one at the very beginning. All settings
that can be made through
mysqlnd_ms_set_qos()
can also be in the plugins configuration file. However, use of the function
is by far the most common use case. Instead of setting session consistency and
strong consistency service levels in the plugins configuration file it is
recommended to define only masters and no slaves. Both service levels will
force the use of masters only. Using an empty slave list shortens the
configuration file, thus improving readability. The only service level for which
there is a case of defining in the plugins configuration file is the combination
of eventual consistency and maximum slave lag.
| Keyword | Description | Version |
|---|---|---|
eventual_consistency
|
Request eventual consistency. Allows the use of all master and slave servers. Data returned may or may not be current.
Eventual consistency accepts an optional
Please note, if a filter chain
generates an empty slave list and the PHP configuration directive
Example #24 Global limit on slave lag {
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27",
"port": "3306"
},
"slave_1": {
"host": "192.168.78.136",
"port": "3306"
}
},
"filters": {
"quality_of_service": {
"eventual_consistency": {
"age":123
}
}
}
}
}
|
Since 1.2.0. |
session_consistency
|
Request session consistency (read your writes). Allows use of all masters
and all slaves which are in sync with the master.
If no further parameters are given slaves are filtered out
as there is no reliable way to test if a slave has caught up
to the master or is lagging behind. Please note, if a filter chain
generates an empty slave list and the PHP configuration directive
Session consistency temporarily requested using
mysqlnd_ms_set_qos() is a valuable alternative
to using |
Since 1.1.0. |
strong_consistency
|
Request strong consistency. Only masters will be used. |
Since 1.2.0. |
failover
Up to and including 1.3.x: string.
Since 1.4.0: object.
Failover policy. Supported policies:
disabled (default), master,
loop_before_master (Since 1.4.0).
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.
Please note, the automatic failover logic is applied when opening connections only. Once a connection has been opened no automatic attempts are made to reopen it in case of an error. If, for example, the server a connection is connected to is shut down and the user attempts to run a statement on the connection, no automatic failover will be tried. Instead, an error will be reported.
If using failover=master the plugin will implicitly
failover to a master, if available. Please check the
concepts documentation to learn about potential
pitfalls and risks of using failover=master.
Example #25 Optional master failover when failing to connect to slave (PECL/mysqlnd_ms < 1.4.0)
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"failover": "master"
}
}
Since PECL/mysqlnd_ms 1.4.0 the failover configuration keyword refers to an object.
Example #26 New syntax since 1.4.0
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"failover": {"strategy": "master" }
}
}
| Keyword | Description | Version |
|---|---|---|
strategy
|
Failover policy. Possible values:
A value of
Setting
If using |
Since 1.4.0. |
remember_failed
|
Remember failures for the duration of a web request. Default:
If set to |
Since 1.4.0. The feature is only available together
with the random and roundrobin
load balancing filter. Use of the setting is recommended.
|
max_retries
|
Maximum number of connection attempts before skipping host.
Default:
The setting is used to prevent hosts from being dropped of the
host list upon the first failure. If set to |
Since 1.4.0. The feature is only available together
with the random and roundrobin
load balancing filter.
|
Setting failover to any other value but
disabled, master or
loop_before_master
will not emit any warning or error.
lazy_connections
bool
Controls the use of lazy connections. Lazy connections are connections which are not opened before the client sends the first connection. Lazy connections are a default.
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. Only some settings are "remembered" and applied when lazy connections are opened.
Example #27 Disabling lazy connection
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"lazy_connections": 0
}
}
Please, see also server_charset to overcome potential
problems with string escaping and servers using different default
charsets.
server_charset
string
The setting has been introduced in 1.4.0. It is recommended to set it if using lazy connections.
The server_charset setting serves two purposes. It
acts as a fallback charset to be used for string escaping done before
a connection has been established and it helps to avoid escaping pitfalls
in heterogeneous environments which servers using different default charsets.
String escaping takes a connections charset into account. String escaping is not possible before a connection has been opened and the connections charset is known. The use of lazy connections delays the actual opening of connections until a statement is send.
An application using lazy connections may attempt to escape a string
before sending a statement. In fact, this should be a common case as
the statement string may contain the string that is to be escaped.
However, due to the lazy connection feature no connection has been opened
yet and escaping fails. The plugin may report an error of the type
E_WARNING and a message like (mysqlnd_ms)
string escaping doesn't work without established connection.
Possible solution is to add server_charset to your configuration
to inform you of the pitfall.
Setting server_charset makes the plugin use
the given charset for string escaping done on lazy connection handles
before establishing a network connection to MySQL. Furthermore, the
plugin will enforce the use of the charset when the connection is
established.
Enforcing the use of the configured charset used for escaping is done to prevent tapping into the pitfall of using a different charset for escaping than used later for the connection. This has the additional benefit of removing the need to align the charset configuration of all servers used. No matter what the default charset on any of the servers is, the plugin will set the configured one as a default.
The plugin does not stop the user from changing the charset at any time
using the set_charset() call or corresponding SQL statements.
Please, note that the use of SQL is not recommended as it cannot be monitored
by the plugin. The user can, for example, change the charset on a
lazy connection handle after escaping a string and before the actual connection
is opened. The charset set by the user will be used for any subsequent escaping
before the connection is established. The connection will be established
using the configured charset, no matter what the server charset is or
what the user has set before. Once a connection has been opened,
set_charset is of no meaning anymore.
Example #28 String escaping on a lazy connection handle
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"lazy_connections": 1,
"server_charset" : "utf8"
}
}
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
$mysqli->real_escape("this will be escaped using the server_charset setting - utf8");
$mysqli->set_charset("latin1");
$mysqli->real_escape("this will be escaped using latin1");
/* server_charset implicitly set - utf8 connection */
$mysqli->query("SELECT 'This connection will be set to server_charset upon establishing' AS _msg FROM DUAL");
/* latin1 used from now on */
$mysqli->set_charset("latin1");
?>
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.
Example #29 Master on write for consistent reads
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"master_on_write": 1
}
}
Please, note the quality_of_service filter introduced
in version 1.2.0-alpha. It gives finer control, for example, for achieving read-your-writes
and, it offers additional functionality introducing
service levels.
All transaction stickiness settings,
including trx_stickiness=on, are overruled by master_on_write=1.
trx_stickiness
string
Transaction stickiness policy. Supported policies:
disabled (default), master.
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 set_autocommit() function.
If setting set_stickiness=master and
autocommit gets disabled by a PHP MySQL extension
invoking the mysqlnd library internal
function call set_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 set_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 or BEGIN.
As of PHP 5.5.0, the mysqlnd library features additional C API calls to
control transactions. The level of control matches the one offered by SQL
statements. The mysqli API has been modified to use
these calls. Since version 1.5.0, PECL/mysqlnd_ms can monitor not only
mysqli_autocommit(), but also mysqli_begin(),
mysqli_commit() and mysqli_rollback() to
detect transaction boundaries and stop load balancing for the duration of
a transaction.
Example #30 Using master to execute transactions
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"trx_stickiness": "master"
}
}
Since version 1.5.0 automatic and silent failover is disabled for the
duration of a transaction. If the boundaries of a transaction have been
properly detected, transaction stickiness is enabled and a server fails,
the plugin will not attempt to fail over to the next server, if any, regardless
of the failover policy configured. The user must handle the error
manually. Depending on the configuration, the plugin may emit
an error of type E_WARNING reading like
(mysqlnd_ms) Automatic failover is not permitted in the middle of a transaction.
This error may then be overwritten by follow up errors such as
(mysqlnd_ms) No connection selected by the last filter.
Those errors will be generated by the failing query function.
Example #31 No automatic failover, error handling pitfall
<?php
/* assumption: automatic failover configured */
$mysqli = new mysqli("myapp", "username", "password", "database");
/* sets plugin internal state in_trx = 1 */
$mysqli->autocommit(false);
/* assumption: server fails */
if (!($res = $mysqli->query("SELECT 'Assume this query fails' AS _msg FROM DUAL"))) {
/* handle failure of transaction, plugin internal state is still in_trx = 1 */
printf("[%d] %s", $mysqli->errno, $mysqli->error);
/*
If using autocommit() based transaction detection it is a
MUST to call autocommit(true). Otherwise the plugin assumes
the current transaction continues and connection
changes remain forbidden.
*/
$mysqli->autocommit(true);
/* Likewise, you'll want to start a new transaction */
$mysqli->autocommit(false);
}
/* latin1 used from now on */
$mysqli->set_charset("latin1");
?>
If a server fails in the middle of a transaction the plugin continues to refuse to switch connections until the current transaction has been finished. Recall that the plugin monitors API calls to detect transaction boundaries. Thus, you have to, for example, enable auto commit mode to end the current transaction before the plugin continues load balancing and switches the server. Likewise, you will want to start a new transaction immediately thereafter and disable auto commit mode again.
Not handling failed queries and not ending a failed transaction
using API calls may cause all following commands emit errors
such as Commands out of sync; you can't run this command now.
Thus, it is important to handle all errors.
transient_error
object
The setting has been introduced in 1.6.0.
A database cluster node may reply a transient error to a client. The client can then repeat the operation on the same node, fail over to a different node or abort the operation. Per definition is it safe for a client to retry the same operation on the same node before giving up.
PECL/mysqlnd_ms can perform the retry
loop on behalf of the application.
By configuring transient_error the plugin can be
instructed to repeat operations failing with a certain error code for
a certain maximum number of times with a pause between the retries.
If the transient error disappears during loop execution, it is
hidden from the application. Otherwise, the error is
forwarded to the application by the end of the loop.
Example #32 Retry loop for transient errors
{
"myapp": {
"master": {
"master_0": {
"host": "localhost"
}
},
"slave": {
"slave_0": {
"host": "192.168.78.136",
"port": "3306"
}
},
"transient_error": {
"mysql_error_codes": [
1297
],
"max_retries": 2,
"usleep_retry": 100
}
}
}
| Keyword | Description | Version |
|---|---|---|
mysql_error_codes
|
List of transient error codes. You may add any MySQL error code
to the list. It is possible to consider any error as transient
not only |
Since 1.6.0. |
max_retries
|
How often to retry an operation which fails with a transient error before forwarding the failure to the user.
Default: |
Since 1.6.0. |
usleep_retry
|
Milliseconds to sleep between transient error retries. The value is passed to the C function usleep(), hence the name.
Default: |
Since 1.6.0. |
xa
object
The setting has been introduced in 1.6.0.
Note: Experimental
The feature is currently under development. There may be issues and/or feature limitations. Do not use in production environments.
Whether to store the username and password of a global transaction participant in the participants table. If disabled, the garbage collection will use the default username and password when connecting to the participants. Unless you are using a different username and password for each of your MySQL servers, you can use the default and avoid storing the sensible information in state store.
Please note, username and password are stored in clear text when using the MySQL state store, which is the only one available. It is in your responsibility to protect this sensible information.
Default: false
During XA garbage collection the plugin may find a participant server
for which the host localhost
has been recorded. If the garbage collection takes place on another host
but the host that has written the participant record to the state store,
the host name localhost now resolves to a different
host. Therefore, when recording a participant servers host name
in the state store, a value of localhost must
be replaced with the actual IP address of localhost.
Setting participant_localhost_ip should be considered
only if using localhost cannot be avoided.
From a garbage collection point of view only, it is preferrable not to
configure any socket connection but to provide an IP address and port
for a node.
The MySQL state store is the only state store available.
Name of the MySQL table used to store the state of an ongoing or aborted global transaction. Use the below SQL statement to create the table. Make sure to edit the table name to match your configuration.
Default: mysqlnd_ms_xa_trx
Example #33 SQL definition for the MySQL state store transaction table
CREATE TABLE mysqlnd_ms_xa_trx (
store_trx_id int(11) NOT NULL AUTO_INCREMENT,
gtrid int(11) NOT NULL,
format_id int(10) unsigned NOT NULL DEFAULT '1',
state enum('XA_NON_EXISTING','XA_ACTIVE','XA_IDLE','XA_PREPARED','XA_COMMIT','XA_ROLLBACK') NOT NULL DEFAULT 'XA_NON_EXISTING',
intend enum('XA_NON_EXISTING','XA_ACTIVE','XA_IDLE','XA_PREPARED','XA_COMMIT','XA_ROLLBACK') DEFAULT 'XA_NON_EXISTING',
finished enum('NO','SUCCESS','FAILURE') NOT NULL DEFAULT 'NO',
modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
started datetime DEFAULT NULL,
timeout datetime DEFAULT NULL,
PRIMARY KEY (store_trx_id),
KEY idx_xa_id (gtrid,format_id,finished),
KEY idx_state (state)
) ENGINE=InnoDB
Name of the MySQL table used to store participants of an ongoing or aborted global transaction. Use the below SQL statement to create the table. Make sure to edit the table name to match your configuration.
Storing credentials can be enabled and disabled using
record_participant_credentials
Default: mysqlnd_ms_xa_participants
Example #34 SQL definition for the MySQL state store transaction table
CREATE TABLE mysqlnd_ms_xa_participants (
fk_store_trx_id int(11) NOT NULL,
bqual varbinary(64) NOT NULL DEFAULT '',
participant_id int(10) unsigned NOT NULL AUTO_INCREMENT,
server_uuid varchar(127) DEFAULT NULL,
scheme varchar(1024) NOT NULL,
host varchar(127) DEFAULT NULL,
port smallint(5) unsigned DEFAULT NULL,
socket varchar(127) DEFAULT NULL,
user varchar(127) DEFAULT NULL,
password varchar(127) DEFAULT NULL,
state enum('XA_NON_EXISTING','XA_ACTIVE','XA_IDLE','XA_PREPARED','XA_COMMIT','XA_ROLLBACK')
NOT NULL DEFAULT 'XA_NON_EXISTING',
health enum('OK','GC_DONE','CLIENT ERROR','SERVER ERROR') NOT NULL DEFAULT 'OK',
connection_id int(10) unsigned DEFAULT NULL,
client_errno smallint(5) unsigned DEFAULT NULL,
client_error varchar(1024) DEFAULT NULL,
modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (participant_id),
KEY idx_xa_bqual (bqual),
KEY idx_store_trx (fk_store_trx_id),
CONSTRAINT mysqlnd_ms_xa_participants_ibfk_1 FOREIGN KEY (fk_store_trx_id)
REFERENCES mysqlnd_ms_xa_trx (store_trx_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
Name of the MySQL table used to track and synchronize garbage collection runs. Use the below SQL statement to create the table. Make sure to edit the table name to match your configuration.
Default: mysqlnd_ms_xa_gc
Example #35 SQL definition for the MySQL state store garbage collection table
CREATE TABLE mysqlnd_ms_xa_gc ( gc_id int(10) unsigned NOT NULL AUTO_INCREMENT, gtrid int(11) NOT NULL, format_id int(10) unsigned NOT NULL DEFAULT '1', fk_store_trx_id int(11) DEFAULT NULL, modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, attempts smallint(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (gc_id), KEY idx_store_trx (gtrid,format_id,fk_store_trx_id) ) ENGINE=InnoDB
Host name of the MySQL server.
Name of the user used to connect to the MySQL server.
Password for the MySQL server user.
Database that holds the garbage collection tables. Please note, you have to create the garbage collection tables prior to using the plugin. The tables will not be created implicitly during runtime but garbage collection will fail if the tables to not exist.
Port of the MySQL server.
Unix domain socket of the MySQL server. Please note, if you have multiple PHP servers each of them will try to carry out garbage collection and need to be able to connect to the state store. In this case, you may prefer configuring an IP address and a port for the MySQL state store server to ensure all PHP servers can reach it.
Whether to automatically rollback an open global transaction when a connection is closed. If enabled, it mimics the default behaviour of local transactions. Should a client disconnect, the server rolls back any open and unfinished transactions.
Default: true
Maximum number of garbage collection runs before giving up.
Allowed values are from 0 to 100.
A setting of 0 means no limit, unless
the state store enforces a limit. Should the state store enforce a limit,
it can be supposed to be significantly higher than 100.
Available since 1.6.0.
Please note, it is important to end failed XA transactions within reasonable time to make participating servers free resources bound to the transaction. The built-in garbage collection is not expected to fail for a long period as long as crashed servers become available again quickly. Still, a situation may arise where a human is required to act because the built-in garbage collection stopped or failed. In this case, you may first want to check if the transaction still cannot be fixed by forcing mysqlnd_ms_xa_gc() to ignore the setting, prior to handling it manually.
Default: 5
Garbage collection probability.
Allowed values are from 0 to 1000.
A setting of 0 disables automatic background
garbage collection. Despite a setting of 0 it is
still possible to trigger garbage collection by calling
mysqlnd_ms_gc().
Available since 1.6.0.
The automatic garbage collection of stalled XA transaction is only available if a state store have been configured. The state store is responsible to keep track of XA transactions. Based on its recordings it can find blocked XA transactions where the client has crashed, connect to the participants and rollback the unfinished transactions.
The garbage collection is triggered as part of PHP's request shutdown
procedure at the end of a web request. That is after your PHP script
has finished working. Do decide whether to run the garbage collection
a random value between 0 and 1000
is computed. If the probability value is higher
or equal to the random value, the state stores garbage collection routines
are invoked.
Default: 5
Maximum number of unfinished XA transactions considered
by the garbage collection during one run.
Allowed values are from 1 to 32768.
Available since 1.6.0.
Cleaning up an unfinished XA transaction takes considerable amounts of time and resources. The garbage collection routine may have to connect to several participants of a failed global transaction to issue the SQL commands for rolling back the unfinished tranaction.
Default: 100
Note:
The below description applies to PECL/mysqlnd_ms < 1.1.0-beta. It is not valid for later versions.
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.
The PHP configuration directive
mysqlnd_ms.ini_file
is used to set the plugins configuration file.
The configuration file mimics standard the php.ini format.
It consists of one or more sections. Every section defines its own unit
of settings. There is no global section for setting defaults.
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 #36 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.
The master[], slave[]
and pick[] configuration directives use a list-like syntax.
Configuration directives supporting list-like syntax may appear multiple
times in a configuration section. The plugin maintains the order in
which entries appear when interpreting them. For example,
the below example shows two slave[] configuration
directives in the configuration section [myapp].
If doing round-robin load balancing for read-only queries, the plugin
will send the first read-only query to the MySQL server
mysql_slave_1 because it is the first in the list.
The second read-only query will be send to the MySQL server
mysql_slave_2 because it is the second in the list.
Configuration directives supporting list-like syntax result are ordered
from top to bottom in accordance to their appearance within a configuration
section.
Example #37 List-like syntax
[myapp] master[] = mysql_master_server slave[] = mysql_slave_1 slave[] = mysql_slave_2
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.
Note:
The section applies to mysqlnd_ms 1.1.0 or newer, not the 1.0 series.
The PECL/mysqlnd_ms test suite is in the tests/ directory of the source distribution. The test suite consists of standard phpt tests, which are described on the PHP Quality Assurance Teams website.
Running the tests requires setting up one to four MySQL servers. Some tests don't connect to MySQL at all. Others require one server for testing. Some require two distinct servers. In some cases two servers are used to emulate a replication setup. In other cases a master and a slave of an existing MySQL replication setup are required for testing. The tests will try to detect how many servers and what kind of servers are given. If the required servers are not found, the test will be skipped automatically.
Before running the tests, edit tests/config.inc to configure the MySQL servers to be used for testing.
The most basic configuration is as follows.
putenv("MYSQL_TEST_HOST=localhost");
putenv("MYSQL_TEST_PORT=3306");
putenv("MYSQL_TEST_USER=root");
putenv("MYSQL_TEST_PASSWD=");
putenv("MYSQL_TEST_DB=test");
putenv("MYSQL_TEST_ENGINE=MyISAM");
putenv("MYSQL_TEST_SOCKET=");
putenv("MYSQL_TEST_SKIP_CONNECT_FAILURE=1");
putenv("MYSQL_TEST_CONNECT_FLAGS=0");
putenv("MYSQL_TEST_EXPERIMENTAL=0");
/* replication cluster emulation */
putenv("MYSQL_TEST_EMULATED_MASTER_HOST=". getenv("MYSQL_TEST_HOST"));
putenv("MYSQL_TEST_EMULATED_SLAVE_HOST=". getenv("MYSQL_TEST_HOST"));
/* real replication cluster */
putenv("MYSQL_TEST_MASTER_HOST=". getenv("MYSQL_TEST_EMULATED_MASTER_HOST"));
putenv("MYSQL_TEST_SLAVE_HOST=". getenv("MYSQL_TEST_EMULATED_SLAVE_HOST"));
MYSQL_TEST_HOST, MYSQL_TEST_PORT and
MYSQL_TEST_SOCKET define the hostname,
TCP/IP port and Unix domain socket of the default database server.
MYSQL_TEST_USER and MYSQL_TEST_PASSWD
contain the user and password needed to connect to the database/schema
configured with MYSQL_TEST_DB. All configured
servers must have the same database user configured to give access to
the test database.
Using host, host:port or host:/path/to/socket
syntax one can set an alternate host, host and port or host and socket for any
of the servers.
putenv("MYSQL_TEST_SLAVE_HOST=192.168.78.136:3307"));
putenv("MYSQL_TEST_MASTER_HOST=myserver_hostname:/path/to/socket"));
The mysqlnd debug log can be used to debug and trace the actitivities of
PECL/mysqlnd_ms. As a mysqlnd PECL/mysqlnd_ms adds trace information to the
mysqlnd library debug file. Please, see the
mysqlnd.debug
PHP configuration directive documentation for a detailed description
on how to configure the debug log.
Configuration setting example to activate the debug log:
mysqlnd.debug=d:t:x:O,/tmp/mysqlnd.trace
Note:
This feature is only available with a debug build of PHP. Works on Microsoft Windows if using a debug build of PHP and PHP was built using Microsoft Visual C version 9 and above.
The debug log shows mysqlnd library and PECL/mysqlnd_ms plugin function calls,
similar to a trace log. Mysqlnd library calls are usually prefixed with
mysqlnd_. PECL/mysqlnd internal calls begin with
mysqlnd_ms.
Example excerpt from the debug log (connect):
[...] >mysqlnd_connect | info : host=myapp user=root db=test port=3306 flags=131072 | >mysqlnd_ms::connect | | >mysqlnd_ms_config_json_section_exists | | | info : section=[myapp] len=[5] | | | >mysqlnd_ms_config_json_sub_section_exists | | | | info : section=[myapp] len=[5] | | | | info : ret=1 | | | <mysqlnd_ms_config_json_sub_section_exists | | | info : ret=1 | | <mysqlnd_ms_config_json_section_exists [...]
The debug log is not only useful for plugin developers but also to find the cause of user errors. For example, if your application does not do proper error handling and fails to record error messages, checking the debug and trace log may help finding the cause. Use of the debug log to debug application issues should be considered only if no other option is available. Writing the debug log to disk is a slow operation and may have negative impact on the application performance.
Example excerpt from the debug log (connection failure):
[...] | | | | | | | info : adding error [Access denied for user 'root'@'localhost' (using password: YES)] to the list | | | | | | | info : PACKET_FREE(0) | | | | | | | info : PACKET_FREE(0x7f3ef6323f50) | | | | | | | info : PACKET_FREE(0x7f3ef6324080) | | | | | | <mysqlnd_auth_handshake | | | | | | info : switch_to_auth_protocol=n/a | | | | | | info : conn->error_info.error_no = 1045 | | | | | <mysqlnd_connect_run_authentication | | | | | info : PACKET_FREE(0x7f3ef63236d8) | | | | | >mysqlnd_conn::free_contents | | | | | | >mysqlnd_net::free_contents | | | | | | <mysqlnd_net::free_contents | | | | | | info : Freeing memory of members | | | | | | info : scheme=unix:///tmp/mysql.sock | | | | | | >mysqlnd_error_list_pdtor | | | | | | <mysqlnd_error_list_pdtor | | | | | <mysqlnd_conn::free_contents | | | | <mysqlnd_conn::connect [...]
The trace log can also be used to verify correct behaviour of PECL/mysqlnd_ms itself, for example, to check which server has been selected for query execution and why.
Example excerpt from the debug log (plugin decision):
[...] >mysqlnd_ms::query | info : query=DROP TABLE IF EXISTS test | >_mysqlnd_plugin_get_plugin_connection_data | | info : plugin_id=5 | <_mysqlnd_plugin_get_plugin_connection_data | >mysqlnd_ms_pick_server_ex | | info : conn_data=0x7fb6a7d3e5a0 *conn_data=0x7fb6a7d410d0 | | >mysqlnd_ms_select_servers_all | | <mysqlnd_ms_select_servers_all | | >mysqlnd_ms_choose_connection_rr | | | >mysqlnd_ms_query_is_select [...] | | | <mysqlnd_ms_query_is_select [...] | | | info : Init the master context | | | info : list(0x7fb6a7d3f598) has 1 | | | info : Using master connection | | | >mysqlnd_ms_advanced_connect | | | | >mysqlnd_conn::connect | | | | | info : host=localhost user=root db=test port=3306 flags=131072 persistent=0 state=0
In this case the statement DROP TABLE IF EXISTS test has been
executed. Note that the statement string is shown in the log file. You may want
to take measures to restrict access to the log for security considerations.
The statement has been load balanced using round robin policy,
as you can easily guess from the functions name >mysqlnd_ms_choose_connection_rr.
It has been sent to a master server running on
host=localhost user=root db=test port=3306 flags=131072 persistent=0 state=0.
Plugin activity can be monitored using the mysqlnd trace log, mysqlnd statistics, mysqlnd_ms plugin statistics and external PHP debugging tools. Use of the trace log should be limited to debugging. It is recommended to use the plugins statistics for monitoring.
Writing a trace log is a slow operation. If using an external PHP debugging tool, please refer to the vendors manual about its performance impact and the type of information collected. In many cases, external debugging tools will provide call stacks. Often, a call stack or a trace log is more difficult to interpret than the statistics provided by the plugin.
Plugin statistics tell how often which kind of cluster node has been used (slave or master), why the node was used, if lazy connections have been used and if global transaction ID injection has been performed. The monitoring information provided enables user to verify plugin decisions and to plan their cluster resources based on usage pattern. The function mysqlnd_ms_get_stats() is used to access the statistics. Please, see the functions description for a list of available statistics.
Statistics are collected on a per PHP process basis. Their scope is a PHP process. Depending on the PHP deployment model a process may serve one or multiple web requests. If using CGI model, a PHP process serves one web request. If using FastCGI or pre-fork web server models, a PHP process usually serves multiple web requests. The same is the case with a threaded web server. Please, note that threads running in parallel can update the statistics in parallel. Thus, if using a threaded PHP deployment model, statistics can be changed by more than one script at a time. A script cannot rely on the fact that it sees only its own changes to statistics.
Example #38 Verify plugin activity in a non-threaded deployment model
mysqlnd_ms.enable=1 mysqlnd_ms.collect_statistics=1
<?php
/* Load balanced following "myapp" section rules from the plugins config file (not shown) */
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
$stats_before = mysqlnd_ms_get_stats();
if ($res = $mysqli->query("SELECT 'Read request' FROM DUAL")) {
var_dump($res->fetch_all());
}
$stats_after = mysqlnd_ms_get_stats();
if ($stats_after['use_slave'] <= $stats_before['use_slave']) {
echo "According to the statistics the read request has not been run on a slave!";
}
?>
Statistics are aggregated for all plugin activities and all connections handled by the plugin. It is not possible to tell how much a certain connection handle has contributed to the overall statistics.
Utilizing PHPs register_shutdown_function() function or the
auto_append_file PHP configuration directive it is
easily possible to dump statistics into, for example, a log file when a script
finishes. Instead of using a log file it is also possible to send the statistics
to an external monitoring tool for recording and display.
Example #39 Recording statistics during shutdown
mysqlnd_ms.enable=1 mysqlnd_ms.collect_statistics=1 error_log=/tmp/php_errors.log
<?php
function check_stats() {
$msg = str_repeat("-", 80) . "\n";
$msg .= var_export(mysqlnd_ms_get_stats(), true) . "\n";
$msg .= str_repeat("-", 80) . "\n";
error_log($msg);
}
register_shutdown_function("check_stats");
?>