Copyright 1997-2012 the PHP Documentation Group.

Mysqlnd plugins and MySQL Proxy are different technologies using different approaches. Both are valid tools for solving a variety of common tasks such as load balancing, monitoring, and performance enhancements. An important difference is that MySQL Proxy works with all MySQL clients, whereas mysqlnd plugins are specific to PHP applications.

As a PHP Extension, a mysqlnd plugin gets installed on the PHP application server, along with the rest of PHP. MySQL Proxy can either be run on the PHP application server or can be installed on a dedicated machine to handle multiple PHP application servers.

Deploying MySQL Proxy on the application server has two advantages:

MySQL Proxy (and mysqlnd plugins) can solve problems easily which otherwise would have required changes to existing applications.

However, MySQL Proxy does have some disadvantages:

MySQL Proxy can be customized with C and Lua programming. Lua is the preferred scripting language of MySQL Proxy. For most PHP experts Lua is a new language to learn. A mysqlnd plugin can be written in C. It is also possible to write plugins in PHP using PECL/mysqlnd_uh.

MySQL Proxy runs as a daemon - a background process. MySQL Proxy can recall earlier decisions, as all state can be retained. However, a mysqlnd plugin is bound to the request-based lifecycle of PHP. MySQL Proxy can also share one-time computed results among multiple application servers. A mysqlnd plugin would need to store data in a persistent medium to be able to do this. Another daemon would need to be used for this purpose, such as Memcache. This gives MySQL Proxy an advantage in this case.

MySQL Proxy works on top of the wire protocol. With MySQL Proxy you have to parse and reverse engineer the MySQL Client Server Protocol. Actions are limited to those that can be achieved by manipulating the communication protocol. If the wire protocol changes (which happens very rarely) MySQL Proxy scripts would need to be changed as well.

Mysqlnd plugins work on top of the C API, which mirrors the libmysql client and Connector/C APIs. This C API is basically a wrapper around the MySQL Client Server protocol, or wire protocol, as it is sometimes called. You can intercept all C API calls. PHP makes use of the C API, therefore you can hook all PHP calls, without the need to program at the level of the wire protocol.

Mysqlnd implements the wire protocol. Plugins can therefore parse, reverse engineer, manipulate and even replace the communication protocol. However, this is usually not required.

As plugins allow you to create implementations that use two levels (C API and wire protocol), they have greater flexibility than MySQL Proxy. If a mysqlnd plugin is implemented using the C API, any subsequent changes to the wire protocol do not require changes to the plugin itself.

Copyright 1997-2012 the PHP Documentation Group.

The mysqlnd plugin API is simply part of the MySQL Native Driver PHP extension, ext/mysqlnd. Development started on the mysqlnd plugin API in December 2009. It is developed as part of the PHP source repository, and as such is available to the public either via Git, or through source snapshot downloads.

The following table shows PHP versions and the corresponding mysqlnd version contained within.

Plugin developers can determine the mysqlnd version through accessing MYSQLND_VERSION, which is a string of the format "mysqlnd 5.0.7-dev - 091210 - $Revision: 300535", or through MYSQLND_VERSION_ID, which is an integer such as 50007. Developers can calculate the version number as follows:

During development, developers should refer to the mysqlnd version number for compatibility and version tests, as several iterations of mysqlnd could occur during the lifetime of a PHP development branch with a single PHP version number.

Copyright 1997-2012 the PHP Documentation Group.

This section provides an overview of the mysqlnd plugin architecture.

MySQL Native Driver Overview

Before developing mysqlnd plugins, it is useful to know a little of how mysqlnd itself is organized. Mysqlnd consists of the following modules:

C Object Oriented Paradigm

At the code level, mysqlnd uses a C pattern for implementing object orientation.

In C you use a struct to represent an object. Members of the struct represent object properties. Struct members pointing to functions represent methods.

Unlike with other languages such as C++ or Java, there are no fixed rules on inheritance in the C object oriented paradigm. However, there are some conventions that need to be followed that will be discussed later.

The PHP Life Cycle

When considering the PHP life cycle there are two basic cycles:

When the PHP engine starts up it will call the module initialization (MINIT) function of each registered extension. This allows each module to setup variables and allocate resources that will exist for the lifetime of the PHP engine process. When the PHP engine shuts down it will call the module shutdown (MSHUTDOWN) function of each extension.

During the lifetime of the PHP engine it will receive a number of requests. Each request constitutes another life cycle. On each request the PHP engine will call the request initialization function of each extension. The extension can perform any variable setup and resource allocation required for request processing. As the request cycle ends the engine calls the request shutdown (RSHUTDOWN) function of each extension so the extension can perform any cleanup required.

How a plugin works

A mysqlnd plugin works by intercepting calls made to mysqlnd by extensions that use mysqlnd. This is achieved by obtaining the mysqlnd function table, backing it up, and replacing it by a custom function table, which calls the functions of the plugin as required.

The following code shows how the mysqlnd function table is replaced:

/* a place to store original function table */struct st_mysqlnd_conn_methods org_methods;void minit_register_hooks(TSRMLS_D) {  /* active function table */  struct st_mysqlnd_conn_methods * current_methods = mysqlnd_conn_get_methods();  /* backup original function table */  memcpy(&org_methods, current_methods, sizeof(struct st_mysqlnd_conn_methods);  /* install new methods */  current_methods->query = MYSQLND_METHOD(my_conn_class, query);}

Connection function table manipulations must be done during Module Initialization (MINIT). The function table is a global shared resource. In an multi-threaded environment, with a TSRM build, the manipulation of a global shared resource during the request processing will almost certainly result in conflicts.

Calling parent methods

If the original function table entries are backed up, it is still possible to call the original function table entries - the parent methods.

In some cases, such as for Connection::stmt_init(), it is vital to call the parent method prior to any other activity in the derived method.

MYSQLND_METHOD(my_conn_class, query)(MYSQLND *conn,  const char *query, unsigned int query_len TSRMLS_DC) {  php_printf("my_conn_class::query(query = %s)\n", query);  query = "SELECT 'query rewritten' FROM DUAL";  query_len = strlen(query);  return org_methods.query(conn, query, query_len); /* return with call to parent */}

Extending properties

A mysqlnd object is represented by a C struct. It is not possible to add a member to a C struct at run time. Users of mysqlnd objects cannot simply add properties to the objects.

Arbitrary data (properties) can be added to a mysqlnd objects using an appropriate function of the mysqlnd_plugin_get_plugin_<object>_data() family. When allocating an object mysqlnd reserves space at the end of the object to hold a void * pointer to arbitrary data. mysqlnd reserves space for one void * pointer per plugin.

The following table shows how to calculate the position of the pointer for a specific plugin:

If you plan to subclass any of the mysqlnd object constructors, which is allowed, you must keep this in mind!

The following code shows extending properties:

/* any data we want to associate */typedef struct my_conn_properties {  unsigned long query_counter;} MY_CONN_PROPERTIES;/* plugin id */unsigned int my_plugin_id;void minit_register_hooks(TSRMLS_D) {  /* obtain unique plugin ID */  my_plugin_id = mysqlnd_plugin_register();  /* snip - see Extending Connection: methods */}static MY_CONN_PROPERTIES** get_conn_properties(const MYSQLND *conn TSRMLS_DC) {  MY_CONN_PROPERTIES** props;  props = (MY_CONN_PROPERTIES**)mysqlnd_plugin_get_plugin_connection_data( conn, my_plugin_id);  if (!props || !(*props)) { *props = mnd_pecalloc(1, sizeof(MY_CONN_PROPERTIES), conn->persistent); (*props)->query_counter = 0;  }  return props;}

The plugin developer is responsible for the management of plugin data memory.

Use of the mysqlnd memory allocator is recommended for plugin data. These functions are named using the convention: mnd_*loc(). The mysqlnd allocator has some useful features, such as the ability to use a debug allocator in a non-debug build.

You must not manipulate function tables at any time later than MINIT if it is not allowed according to the above table.

Some classes contain a pointer to the method function table. All instances of such a class will share the same function table. To avoid chaos, in particular in threaded environments, such function tables must only be manipulated during MINIT.

Other classes use copies of a globally shared function table. The class function table copy is created together with the object. Each object uses its own function table. This gives you two options: you can manipulate the default function table of an object at MINIT, and you can additionally refine methods of an object without impacting other instances of the same class.

The advantage of the shared function table approach is performance. There is no need to copy a function table for each and every object.

It is strongly recommended that you do not entirely replace a constructor. The constructors perform memory allocations. The memory allocations are vital for the mysqlnd plugin API and the object logic of mysqlnd. If you do not care about warnings and insist on hooking the constructors, you should at least call the parent constructor before doing anything in your constructor.

Regardless of all warnings, it can be useful to subclass constructors. Constructors are the perfect place for modifying the function tables of objects with non-shared object tables, such as Resultset, Network, Wire Protocol.

The destructors are the appropriate place to free properties, mysqlnd_plugin_get_plugin_<object>_data().

The listed destructors may not be equivalent to the actual mysqlnd method freeing the object itself. However, they are the best possible place for you to hook in and free your plugin data. As with constructors you may replace the methods entirely but this is not recommended. If multiple methods are listed in the above table you will need to hook all of the listed methods and free your plugin data in whichever method is called first by mysqlnd.

The recommended method for plugins is to simply hook the methods, free your memory and call the parent implementation immediately following this.

Copyright 1997-2012 the PHP Documentation Group.

The following is a list of functions provided in the mysqlnd plugin API:

There is no formal definition of what a plugin is and how a plugin mechanism works.

Components often found in plugins mechanisms are:

The mysqlnd plugin concept employs these features, and additionally enjoys an open architecture.

No Restrictions

A plugin has full access to the inner workings of mysqlnd. There are no security limits or restrictions. Everything can be overwritten to implement friendly or hostile algorithms. It is recommended you only deploy plugins from a trusted source.

As discussed previously, plugins can use pointers freely. These pointers are not restricted in any way, and can point into another plugin's data. Simple offset arithmetic can be used to read another plugin's data.

It is recommended that you write cooperative plugins, and that you always call the parent method. The plugins should always cooperate with mysqlnd itself.

In this scenario, a cache (ext/mysqlnd_cache) and a monitor (ext/mysqlnd_monitor) plugin are loaded. Both subclass Connection::query(). Plugin registration happens at MINIT using the logic shown previously. PHP calls extensions in alphabetical order by default. Plugins are not aware of each other and do not set extension dependencies.

By default the plugins call the parent implementation of the query method in their derived version of the method.

PHP Extension Recap

This is a recap of what happens when using an example plugin, ext/mysqlnd_plugin, which exposes the mysqlnd C plugin API to PHP:

Copyright 1997-2012 the PHP Documentation Group.

It is important to remember that a mysqlnd plugin is itself a PHP extension.

The following code shows the basic structure of the MINIT function that will be used in the typical mysqlnd plugin:

/* my_php_mysqlnd_plugin.c */ static PHP_MINIT_FUNCTION(mysqlnd_plugin) {  /* globals, ini entries, resources, classes */  /* register mysqlnd plugin */  mysqlnd_plugin_id = mysqlnd_plugin_register();  conn_m = mysqlnd_get_conn_methods();  memcpy(org_conn_m, conn_m, sizeof(struct st_mysqlnd_conn_methods));  conn_m->query = MYSQLND_METHOD(mysqlnd_plugin_conn, query);  conn_m->connect = MYSQLND_METHOD(mysqlnd_plugin_conn, connect);}

/* my_mysqlnd_plugin.c */ enum_func_status MYSQLND_METHOD(mysqlnd_plugin_conn, query)(/* ... */) {  /* ... */}enum_func_status MYSQLND_METHOD(mysqlnd_plugin_conn, connect)(/* ... */) {  /* ... */}

Task analysis: from C to userspace

 class proxy extends mysqlnd_plugin_connection {  public function connect($host, ...) { .. }}mysqlnd_plugin_set_conn_proxy(new proxy());

Process:

You need to carry out the following:

Userspace object methods can either be called using call_user_function() or you can operate at a level closer to the Zend Engine and use zend_call_method().

Optimization: calling methods from C using zend_call_method

The following code snippet shows the prototype for the zend_call_method function, taken from zend_interfaces.h.

 ZEND_API zval* zend_call_method(  zval **object_pp, zend_class_entry *obj_ce,  zend_function **fn_proxy, char *function_name,  int function_name_len, zval **retval_ptr_ptr,  int param_count, zval* arg1, zval* arg2 TSRMLS_DC);

Zend API supports only two arguments. You may need more, for example:

 enum_func_status (*func_mysqlnd_conn__connect)(  MYSQLND *conn, const char *host,  const char * user, const char * passwd,  unsigned int passwd_len, const char * db,  unsigned int db_len, unsigned int port,  const char * socket, unsigned int mysql_flags TSRMLS_DC);

To get around this problem you will need to make a copy of zend_call_method() and add a facility for additional parameters. You can do this by creating a set of MY_ZEND_CALL_METHOD_WRAPPER macros.

Calling PHP userspace

This code snippet shows the optimized method for calling a userspace function from C:

 /* my_mysqlnd_plugin.c */MYSQLND_METHOD(my_conn_class,connect)(  MYSQLND *conn, const char *host /* ... */ TSRMLS_DC) {  enum_func_status ret = FAIL;  zval * global_user_conn_proxy = fetch_userspace_proxy();  if (global_user_conn_proxy) { /* call userspace proxy */ ret = MY_ZEND_CALL_METHOD_WRAPPER(global_user_conn_proxy,host, /*...*/);  } else { /* or original mysqlnd method = do nothing, be transparent */ ret = org_methods.connect(conn, host, user, passwd,  passwd_len, db, db_len, port,  socket, mysql_flags TSRMLS_CC);  }  return ret;}

Calling userspace: simple arguments

/* my_mysqlnd_plugin.c */ MYSQLND_METHOD(my_conn_class,connect)(  /* ... */, const char *host, /* ...*/) {  /* ... */  if (global_user_conn_proxy) { /* ... */ zval* zv_host; MAKE_STD_ZVAL(zv_host); ZVAL_STRING(zv_host, host, 1); MY_ZEND_CALL_METHOD_WRAPPER(global_user_conn_proxy, zv_retval,zv_host /*, ...*/); zval_ptr_dtor(&zv_host); /* ... */  }  /* ... */}

Calling userspace: structs as arguments

/* my_mysqlnd_plugin.c */MYSQLND_METHOD(my_conn_class, connect)(  MYSQLND *conn, /* ...*/) {  /* ... */  if (global_user_conn_proxy) { /* ... */ zval* zv_conn; ZEND_REGISTER_RESOURCE(zv_conn, (void *)conn, le_mysqlnd_plugin_conn); MY_ZEND_CALL_METHOD_WRAPPER(global_user_conn_proxy, zv_retval, zv_conn,zv_host /*, ...*/); zval_ptr_dtor(&zv_conn); /* ... */  }  /* ... */}

The first argument of many mysqlnd methods is a C "object". For example, the first argument of the connect() method is a pointer to MYSQLND. The struct MYSQLND represents a mysqlnd connection object.

The mysqlnd connection object pointer can be compared to a standard I/O file handle. Like a standard I/O file handle a mysqlnd connection object shall be linked to the userspace using the PHP resource variable type.

From C to userspace and back

 class proxy extends mysqlnd_plugin_connection {  public function connect($conn, $host, ...) { /* "pre" hook */ printf("Connecting to host = '%s'\n", $host); debug_print_backtrace(); return parent::connect($conn);  }  public function query($conn, $query) { /* "post" hook */ $ret = parent::query($conn, $query); printf("Query = '%s'\n", $query); return $ret;  }}mysqlnd_plugin_set_conn_proxy(new proxy());

PHP users must be able to call the parent implementation of an overwritten method.

As a result of subclassing it is possible to refine only selected methods and you can choose to have "pre" or "post" hooks.

Buildin class: mysqlnd_plugin_connection::connect()

/*  my_mysqlnd_plugin_classes.c */ PHP_METHOD("mysqlnd_plugin_connection", connect) {  /* ... simplified! ... */  zval* mysqlnd_rsrc;  MYSQLND* conn;  char* host; int host_len;  if (zend_parse_parameters(ZEND_NUM_ARGS() TSRMLS_CC, "rs", &mysqlnd_rsrc, &host, &host_len) == FAILURE) { RETURN_NULL();  }  ZEND_FETCH_RESOURCE(conn, MYSQLND* conn, &mysqlnd_rsrc, -1, "Mysqlnd Connection", le_mysqlnd_plugin_conn);  if (PASS == org_methods.connect(conn, host, /* simplified! */ TSRMLS_CC)) RETVAL_TRUE;  else RETVAL_FALSE;}

Copyright 1997-2012 the PHP Documentation Group.

The plugin is implemented as a PHP extension. See also the installation instructions to install the PECL/mysqlnd_ms extension.

Compile or configure the PHP MySQL extension (API) (mysqli, PDO_MYSQL, mysql) that you plan to use with support for the mysqlnd library. PECL/mysqlnd_ms is a plugin for the mysqlnd library. To use the plugin with any of the PHP MySQL extensions, the extension has to use the mysqlnd library.

Then, load the extension into PHP and activate the plugin in the PHP configuration file using the PHP configuration directive named mysqlnd_ms.enable.

mysqlnd_ms.enable=1mysqlnd_ms.ini_file=/path/to/mysqlnd_ms_plugin.ini

The plugin uses its own configuration file. Use the PHP configuration directive mysqlnd_ms.ini_file to set the full file path to the plugin-specific configuration file. This file must be readable by PHP (e.g., the web server user).

Create a plugin-specific configuration file. Save the file to the path set by the PHP configuration directive mysqlnd_ms.ini_file.

The plugins configuration file is JSON based. It is divided into one or more sections. Each section has a name, for example, myapp. Every section makes its own set of configuration settings.

A section must, at a minimum, list the MySQL replication master server, and set a list of slaves. The plugin supports using only one master server per section. Multi-master MySQL replication setups are not yet fully supported. Use the configuration setting master to set the hostname, and the port or socket of the MySQL master server. MySQL slave servers are configured using the slave keyword.

{ "myapp": { "master": { "master_0": { "host": "localhost" } }, "slave": [ ] }}

Configuring a MySQL slave server list is required, although it may contain an empty list. It is recommended to always configure at least one slave server.

Server lists can use anonymous or non-anonymous syntax. Non-anonymous lists include alias names for the servers, such as master_0 for the master in the above example. The quickstart uses the more verbose non-anonymous syntax.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } }}

If there are at least two servers in total, the plugin can start to load balance and switch connections. Switching connections is not always transparent and can cause issues in certain cases. The reference sections about connection pooling and switching, transaction handling, fail over load balancing and read-write splitting all provide more details. And potential pitfalls are described later in this guide.

It is the responsibility of the application to handle potential issues caused by connection switches, by configuring a master with at least one slave server, which allows switching to work therefore related problems can be found.

The MySQL master and MySQL slave servers, which you configure, do not need to be part of MySQL replication setup. For testing purpose you can use single MySQL server and make it known to the plugin as a master and slave server as shown below. This could help you to detect many potential issues with connection switches. However, such a setup will not be prone to the issues caused by replication lag.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } } }}

Copyright 1997-2012 the PHP Documentation Group.

The plugin can be used with any PHP MySQL extension (mysqli, mysql, and PDO_MYSQL) that is compiled to use the mysqlnd library. PECL/mysqlnd_ms plugs into the mysqlnd library. It does not change the API or behavior of those extensions.

Whenever a connection to MySQL is being opened, the plugin compares the host parameter value of the connect call, with the section names from the plugin specific configuration file. If, for example, the plugin specific configuration file has a section myapp then the section should be referenced by opening a MySQL connection to the host myapp

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } }}

<?php/* Load balanced following "myapp" section rules from the plugins config file */$mysqli = new mysqli("myapp", "username", "password", "database");$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');$mysql = mysql_connect("myapp", "username", "password");?>

The connection examples above will be load balanced. The plugin will send read-only statements to the MySQL slave server with the IP 192.168.2.27 and will listen on port 3306 for the MySQL client connection. All other statements will be directed to the MySQL master server running on the host localhost. If on Unix like operating systems, the master on localhost will be accepting MySQL client connections on the Unix domain socket /tmp/mysql.sock, while TCP/IP is the default port on Windows. The plugin will use the user name username and the password password to connect to any of the MySQL servers listed in the section myapp of the plugins configuration file. Upon connect, the plugin will select database as the current schemata.

The username, password and schema name are taken from the connect API calls and used for all servers. In other words: you must use the same username and password for every MySQL server listed in a plugin configuration file section. The is not a general limitation. As of PECL/mysqlnd_ms 1.1.0, it is possible to set the username and password for any server in the plugins configuration file, to be used instead of the credentials passed to the API call.

The plugin does not change the API for running statements. Read-write splitting works out of the box. The following example assumes that there is no significant replication lag between the master and the slave.

<?php/* Load balanced following "myapp" section rules from the plugins config file */$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()));/* Statements will be run on the master */if (!$mysqli->query("DROP TABLE IF EXISTS test")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}if (!$mysqli->query("CREATE TABLE test(id INT)")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}if (!$mysqli->query("INSERT INTO test(id) VALUES (1)")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}/* read-only: statement will be run on a slave */if (!($res = $mysqli->query("SELECT id FROM test")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);} else { $row = $res->fetch_assoc(); $res->close(); printf("Slave returns id = '%s'\n", $row['id'];}$mysqli->close();?> 

Slave returns id = '1'

Copyright 1997-2012 the PHP Documentation Group.

The plugin changes the semantics of a PHP MySQL connection handle. A new connection handle represents a connection pool, instead of a single MySQL client-server network connection. The connection pool consists of a master connection, and optionally any number of slave connections.

Every connection from the connection pool has its own state. For example, SQL user variables, temporary tables and transactions are part of the state. For a complete list of items that belong to the state of a connection, see the connection pooling and switching concepts documentation. If the plugin decides to switch connections for load balancing, the application could be given a connection which has a different state. Applications must be made aware of this.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */if (!$mysqli->query("SET @myrole='master'")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}/* Connection 2, run on slave because SELECT */if (!($res = $mysqli->query("SELECT @myrole AS _role"))) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);} else { $row = $res->fetch_assoc(); $res->close(); printf("@myrole = '%s'\n", $row['_role']);}$mysqli->close();?> 

@myrole = ''

The example opens a load balanced connection and executes two statements. The first statement SET @myrole='master' does not begin with the string SELECT. Therefore the plugin does not recognize it as a read-only query which shall be run on a slave. The plugin runs the statement on the connection to the master. The statement sets a SQL user variable which is bound to the master connection. The state of the master connection has been changed.

The next statement is SELECT @myrole AS _role. The plugin does recognize it as a read-only query and sends it to the slave. The statement is run on a connection to the slave. This second connection does not have any SQL user variables bound to it. It has a different state than the first connection to the master. The requested SQL user variable is not set. The example script prints @myrole = ''.

It is the responsibility of the application developer to take care of the connection state. The plugin does not monitor all connection state changing activities. Monitoring all possible cases would be a very CPU intensive task, if it could be done at all.

The pitfalls can easily be worked around using SQL hints.

Copyright 1997-2012 the PHP Documentation Group.

SQL hints can force a query to choose a specific server from the connection pool. It gives the plugin a hint to use a designated server, which can solve issues caused by connection switches and connection state.

SQL hints are standard compliant SQL comments. Because SQL comments are supposed to be ignored by SQL processing systems, they do not interfere with other programs such as the MySQL Server, the MySQL Proxy, or a firewall.

Three SQL hints are supported by the plugin: The MYSQLND_MS_MASTER_SWITCH hint makes the plugin run a statement on the master, MYSQLND_MS_SLAVE_SWITCH enforces the use of the slave, and MYSQLND_MS_LAST_USED_SWITCH will run a statement on the same server that was used for the previous statement.

The plugin scans the beginning of a statement for the existence of an SQL hint. SQL hints are only recognized if they appear at the beginning of the statement.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } }}

<?php$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()));/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */if (!$mysqli->query("SET @myrole='master'")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}/* Connection 1, run on master because of SQL hint */if (!($res = $mysqli->query(sprintf("/*%s*/SELECT @myrole AS _role",MYSQLND_MS_LAST_USED_SWITCH)))) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);} else { $row = $res->fetch_assoc(); $res->close(); printf("@myrole = '%s'\n", $row['_role']);}$mysqli->close();?> 

@myrole = 'master'

In the above example, using MYSQLND_MS_LAST_USED_SWITCH prevents session switching from the master to a slave when running the SELECT statement.

SQL hints can also be used to run SELECT statements on the MySQL master server. This may be desired if the MySQL slave servers are typically behind the master, but you need current data from the cluster.

In version 1.2.0 the concept of a service level has been introduced to address cases when current data is required. Using a service level requires less attention and removes the need of using SQL hints for this use case. Please, find more information below in the service level and consistency section.

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Force use of master, master has always fresh and current data */if (!$mysqli->query(sprintf("/*%s*/SELECT critical_data FROM important_table",MYSQLND_MS_MASTER_SWITCH))) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}?>

A use case may include the creation of tables on a slave. If an SQL hint is not given, then the plugin will send CREATE and INSERT statements to the master. Use the SQL hint MYSQLND_MS_SLAVE_SWITCH if you want to run any such statement on a slave, for example, to build temporary reporting tables.

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Force use of slave */if (!$mysqli->query(sprintf("/*%s*/CREATE TABLE slave_reporting(id INT)",MYSQLND_MS_SLAVE_SWITCH))) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}/* Continue using this particular slave connection */if (!$mysqli->query(sprintf("/*%s*/INSERT INTO slave_reporting(id) VALUES (1), (2), (3)",MYSQLND_MS_LAST_USED_SWITCH))) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}/* Don't use MYSQLND_MS_SLAVE_SWITCH which would allow switching to another slave! */if ($res = $mysqli->query(sprintf("/*%s*/SELECT COUNT(*) AS _num FROM slave_reporting",MYSQLND_MS_LAST_USED_SWITCH))) {  $row = $res->fetch_assoc();  $res->close();  printf("There are %d rows in the table 'slave_reporting'", $row['_num']);} else {  printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}$mysqli->close();?>

The SQL hint MYSQLND_MS_LAST_USED forbids switching a connection, and forces use of the previously used connection.

Copyright 1997-2012 the PHP Documentation Group.

The current version of the plugin is not transaction safe by default, because it is not aware of running transactions in all cases. SQL transactions are units of work to be run on a single server. The plugin does not always know when the unit of work starts and when it ends. Therefore, the plugin may decide to switch connections in the middle of a transaction.

No kind of MySQL load balancer can detect transaction boundaries without any kind of hint from the application.

You can either use SQL hints to work around this limitation. Alternatively, you can activate transaction API call monitoring. In the latter case you must use API calls only to control transactions, see below.

[myapp]{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Not a SELECT, will use master */if (!$mysqli->query("START TRANSACTION")) { /* Please use better error handling in your code */ die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}/* Prevent connection switch! */if (!$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (1)", MYSQLND_MS_LAST_USED_SWITCH)))) { /* Please do proper ROLLBACK in your code, don't just die */ die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}if ($res = $mysqli->query(sprintf("/*%s*/SELECT COUNT(*) AS _num FROM test", MYSQLND_MS_LAST_USED_SWITCH)))) {  $row = $res->fetch_assoc();  $res->close();  if ($row['_num'] > 1000) {   if (!$mysqli->query(sprintf("/*%s*/INSERT INTO events(task) VALUES ('cleanup')", MYSQLND_MS_LAST_USED_SWITCH)))) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));   }  }} else { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}if (!$mysqli->query(sprintf("/*%s*/UPDATE log SET last_update = NOW()", MYSQLND_MS_LAST_USED_SWITCH)))) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}if (!$mysqli->query(sprintf("/*%s*/COMMIT", MYSQLND_MS_LAST_USED_SWITCH)))) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}$mysqli->close();?>

Starting with PHP 5.4.0, the mysqlnd library allows the plugin to monitor the status of the autocommit mode, if the mode is set by API calls instead of using SQL statements such as SET AUTOCOMMIT=0. This makes it possible for the plugin to become transaction aware. In this case, you do not need to use SQL hints.

If using PHP 5.4.0 or newer, API calls that enable autocommit mode, and when setting the plugin configuration option trx_stickiness=master, the plugin can automatically disable load balancing and connection switches for SQL transactions. In this configuration, the plugin stops load balancing if autocommit is disabled and directs all statements to the master. This prevents connection switches in the middle of a transaction. Once autocommit is re-enabled, the plugin starts to load balance statements again.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, "trx_stickiness": "master" }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Disable autocommit, plugin will run all statements on the master */$mysqli->autocommit(FALSE);if (!$mysqli->query("INSERT INTO test(id) VALUES (1)")) { /* Please do proper ROLLBACK in your code, don't just die */ die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}if ($res = $mysqli->query("SELECT COUNT(*) AS _num FROM test")) {  $row = $res->fetch_assoc();  $res->close();  if ($row['_num'] > 1000) {   if (!$mysqli->query("INSERT INTO events(task) VALUES ('cleanup')")) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));   }  }} else { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}if (!$mysqli->query("UPDATE log SET last_update = NOW()")) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}if (!$mysqli->commit()) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}/* Plugin assumes that the transaction has ended and starts load balancing again */$mysqli->autocommit(TRUE);$mysqli->close();?>

Copyright 1997-2012 the PHP Documentation Group.

Different types of MySQL cluster solutions offer different service and data consistency levels to their users. An asynchronous MySQL replication cluster offers eventual consistency by default. A read executed on an asynchronous slave may return current, stale or no data at all, depending on whether the slave has replayed all changesets from the master or not.

Applications using an MySQL replication cluster need to be designed to work correctly with eventual consistent data. In some cases, however, stale data is not acceptable. In those cases only certain slaves or even only master accesses are allowed to achieve the required quality of service from the cluster.

As of PECL mysqlnd_ms 1.2.0 the plugin is capable of selecting MySQL replication nodes automatically that deliver session consistency or strong consistency. Session consistency means that one client can read its writes. Other clients may or may not see the clients' write. Strong consistency means that all clients will see all writes from the client.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } } }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* read-write splitting: master used */if (!$mysqli->query("INSERT INTO orders(order_id, item) VALUES (1, 'christmas tree, 1.8m')")) {   /* Please use better error handling in your code */  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}/* Request session consistency: read your writes */if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Plugin picks a node which has the changes, here: master */if (!$res = $mysqli->query("SELECT item FROM orders WHERE order_id = 1"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));var_dump($res->fetch_assoc());/* Back to eventual consistency: stale data allowed */if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Plugin picks any slave, stale data is allowed */if (!$res = $mysqli->query("SELECT item, price FROM specials"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));?>

Service levels can be set in the plugins configuration file and at runtime using mysqlnd_ms_set_qos. In the example the function is used to enforce session consistency (read your writes) for all future statements until further notice. The SELECT statement on the orders table is run on the master to ensure the previous write can be seen by the client. Read-write splitting logic has been adapted to fulfill the service level.

After the application has read its changes from the orders table it returns to the default service level, which is eventual consistency. Eventual consistency puts no restrictions on choosing a node for statement execution. Thus, the SELECT statement on the specials table is executed on a slave.

The new functionality supersedes the use of SQL hints and the master_on_write configuration option. In many cases mysqlnd_ms_set_qos is easier to use, more powerful improves portability.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, "failover" : "master" }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Read from slaves lagging no more than four seconds */$ret = mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_AGE, 4);if (!$ret)  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Plugin picks any slave, which may or may not have the changes */if (!$res = $mysqli->query("SELECT item, price FROM daytrade"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Back to default: use of all slaves and masters permitted */if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));?>

The eventual consistency service level can be used with an optional parameter to set a maximum slave lag for choosing slaves. If set, the plugin checks SHOW SLAVE STATUS for all configured slaves. In case of the example, only slaves for which Slave_IO_Running=Yes, Slave_SQL_Running=Yes and Seconds_Behind_Master <= 4 is true are considered for executing the statement SELECT item, price FROM daytrade.

Checking SHOW SLAVE STATUS is done transparently from an applications perspective. Errors, if any, are reported as warnings. No error will be set on the connection handle. Even if all SHOW SLAVE STATUS SQL statements executed by the plugin fail, the execution of the users statement is not stopped, given that master fail over is enabled. Thus, no application changes are required.

To prevent mysqlnd_ms from emitting a warning if no slaves can be found that lag no more than the defined number of seconds behind the master, it is necessary to enable master fail over in the plugins configuration file. If no slaves can be found and fail over is turned on, the plugin picks a master for executing the statement.

If no slave can be found and fail over is turned off, the plugin emits a warning, it does not execute the statement and it sets an error on the connection.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } } }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Read from slaves lagging no more than four seconds */$ret = mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_AGE, 4);if (!$ret)  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Plugin picks any slave, which may or may not have the changes */if (!$res = $mysqli->query("SELECT item, price FROM daytrade"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Back to default: use of all slaves and masters permitted */if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));?> 

PHP Warning:  mysqli::query(): (mysqlnd_ms) Couldn't find the appropriate slave connection. 0 slaves to choose from. Something is wrong in %s on line %dPHP Warning:  mysqli::query(): (mysqlnd_ms) No connection selected by the last filter in %s on line %d[2000] (mysqlnd_ms) No connection selected by the last filter

Copyright 1997-2012 the PHP Documentation Group.

PECL/mysqlnd_ms can either use its own global transaction ID emulation or the global transaction ID feature built-in to MySQL 5.6.5-m8 or later. From a developer perspective the client-side and server-side approach offer the same features with regards to service levels provided by PECL/mysqlnd_ms. Their differences are discussed in the concepts section.

The quickstart first demonstrates the use of the client-side global transaction ID emulation built-in to PECL/mysqlnd_ms before its show how to use the server-side counterpart. The order ensures that the underlying idea is discussed first.

Idea and client-side emulation

In its most basic form a global transaction ID (GTID) is a counter in a table on the master. The counter is incremented whenever a transaction is committed on the master. Slaves replicate the table. The counter serves two purposes. In case of a master failure, it helps the database administrator to identify the most recent slave for promoting it to the new master. The most recent slave is the one with the highest counter value. Applications can use the global transaction ID to search for slaves which have replicated a certain write (identified by a global transaction ID) already.

PECL/mysqlnd_ms can inject SQL for every committed transaction to increment a GTID counter. The so created GTID is accessible by the application to identify an applications write operation. This enables the plugin to deliver session consistency (read your writes) service level by not only querying masters but also slaves which have replicated the change already. Read load is taken away from the master.

Client-side global transaction ID emulation has some limitations. Please, read the concepts section carefully to fully understand the principles and ideas behind it, before using in production environments. The background knowledge is not required to continue with the quickstart.

First, create a counter table on your master server and insert a record into it. The plugin does not assist creating the table. Database administrators must make sure it exists. Depending on the error reporting mode, the plugin will silently ignore the lack of the table or bail out.

CREATE TABLE `trx` (  `trx_id` int(11) DEFAULT NULL,  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=latin1INSERT INTO `trx`(`trx_id`) VALUES (1);

In the plugins configuration file set the SQL to update the global transaction ID table using on_commit from the global_transaction_id_injection section. Make sure the table name used for the UPDATE statement is fully qualified. In the example, test.trx is used to refer to table trx in the schema (database) test. Use the table that was created in the previous step. It is important to set the fully qualified table name because the connection on which the injection is done may use a different default database. Make sure the user that opens the connection is allowed to execute the UPDATE.

Enable reporting of errors that may occur when mysqlnd_ms does global transaction ID injection.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, "global_transaction_id_injection":{ "on_commit":"UPDATE test.trx SET trx_id = trx_id + 1", "report_error":true } }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("DROP TABLE IF EXISTS test"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("CREATE TABLE test(id INT)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("INSERT INTO test(id) VALUES (1)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* auto commit mode, read on slave, no increment */if (!($res = $mysqli->query("SELECT id FROM test")))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));var_dump($res->fetch_assoc());?> 

array(1) {  ["id"]=>  string(1) "1"}

The example runs three statements in auto commit mode on the master, causing three transactions on the master. For every such statement, the plugin will inject the configured UPDATE transparently before executing the users SQL statement. When the script ends the global transaction ID counter on the master has been incremented by three.

The fourth SQL statement executed in the example, a SELECT, does not trigger an increment. Only transactions (writes) executed on a master shall increment the GTID counter.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, "global_transaction_id_injection":{ "on_commit":"UPDATE test.trx SET trx_id = trx_id + 1", "fetch_last_gtid" : "SELECT MAX(trx_id) FROM test.trx", "report_error":true } }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("DROP TABLE IF EXISTS test"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("CREATE TABLE test(id INT)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));?> 

GTID after transaction 7GTID after transaction 8

Applications can ask PECL mysqlnd_ms for a global transaction ID which belongs to the last write operation performed by the application. The function mysqlnd_ms_get_last_gtid returns the GTID obtained when executing the SQL statement from the fetch_last_gtid entry of the global_transaction_id_injection section from the plugins configuration file. The function may be called after the GTID has been incremented.

Applications are adviced not to run the SQL statement themselves as this bares the risk of accidently causing an implicit GTID increment. Also, if the function is used, it is easy to migrate an application from one SQL statement for fetching a transaction ID to another, for example, if any MySQL server ever features built-in global transaction ID support.

The quickstart shows a SQL statement which will return a GTID equal or greater to that created for the previous statement. It is exactly the GTID created for the previous statement if no other clients have incremented the GTID in the time span between the statement execution and the SELECT to fetch the GTID. Otherwise, it is greater.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, "global_transaction_id_injection":{ "on_commit":"UPDATE test.trx SET trx_id = trx_id + 1", "fetch_last_gtid" : "SELECT MAX(trx_id) FROM test.trx", "check_for_gtid" : "SELECT trx_id FROM test.trx WHERE trx_id >= #GTID", "report_error":true } }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)") || !$mysqli->query("INSERT INTO test(id) VALUES (1)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* GTID as an identifier for the last write */$gtid = mysqlnd_ms_get_last_gtid($mysqli);/* Session consistency (read your writes): try to read from slaves not only master */if (false == mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION, MYSQLND_MS_QOS_OPTION_GTID, $gtid)) { die(sprintf("[006] [%d] %s\n", $mysqli->errno, $mysqli->error));}/* Either run on master or a slave which has replicated the INSERT */if (!($res = $mysqli->query("SELECT id FROM test"))) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}var_dump($res->fetch_assoc());?>

A GTID returned from mysqlnd_ms_get_last_gtid can be used as an option for the session consistency service level. Session consistency delivers read your writes. Session consistency can be requested by calling mysqlnd_ms_set_qos. In the example, the plugin will execute the SELECT statement either on the master or on a slave which has replicated the previous INSERT already.

PECL mysqlnd_ms will transparently check every configured slave if it has replicated the INSERT by checking the slaves GTID table. The check is done running the SQL set with the check_for_gtid option from the global_transaction_id_injection section of the plugins configuration file. Please note, that this is a slow and expensive procedure. Applications should try to use it sparsely and only if read load on the master becomes to high otherwise.

Use of the server-side global transaction ID feature

Starting with MySQL 5.6.5-m8 the MySQL Replication system features server-side global transaction IDs. Transaction identifiers are automatically generated and maintained by the server. Users do not need to take care of maintaining them. There is no need to setup any tables in advance, or for setting on_commit. A client-side emulation is no longer needed.

Clients can continue to use global transaction identifier to achieve session consistency when reading from MySQL Replication slaves. The algorithm works as described above. Different SQL statements must be configured for fetch_last_gtid and check_for_gtid. The statements are given below. Please note, MySQL 5.6.5-m8 is a development version. Details of the server implementation may change in the future and require adoption of the SQL statements shown.

Using the following configuration any of the above described functionality can be used together with the server-side global transaction ID feature. mysqlnd_ms_get_last_gtid and mysqlnd_ms_set_qos continue to work as described above. The only difference is that the server does not use a simple sequence number but a string containing of a server identifier and a sequence number. Thus, users cannot easily derive an order from GTIDs returned by mysqlnd_ms_get_last_gtid.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, "global_transaction_id_injection":{ "fetch_last_gtid" : "SELECT @@GLOBAL.GTID_DONE AS trx_id FROM DUAL", "check_for_gtid" : "SELECT GTID_SUBSET('#GTID', @@GLOBAL.GTID_DONE) AS trx_id FROM DUAL", "report_error":true } }}

Copyright 1997-2012 the PHP Documentation Group.

Databases clusters can deliver different levels of consistency. As of PECL/mysqlnd_ms 1.2.0 it is possible to advice the plugin to consider only cluster nodes that can deliver the consistency level requested. For example, if using asynchronous MySQL Replication with its cluster-wide eventual consistency, it is possible to request session consistency (read your writes) at any time using mysqlnd_ms_set_quos. Please, see also the service level and consistency introduction.

/* Request session consistency: read your writes */if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));

Assuming PECL/mysqlnd has been explicitly told to deliver no consistency level higher than eventual consistency, it is possible to replace a database node read access with a client-side cache using time-to-live (TTL) as its invalidation strategy. Both the database node and the cache may or may not serve current data as this is what eventual consistency defines.

Replacing a database node read access with a local cache access can improve overall performance and lower the database load. If the cache entry is every reused by other clients than the one creating the cache entry, a database access is saved and thus database load is lowered. Furthermore, system performance can become better if computation and delivery of a database query is slower than a local cache access.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "127.0.0.1", "port": "3306" } }, }}

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)") || !$mysqli->query("INSERT INTO test(id) VALUES (1)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Explicitly allow eventual consistency and caching (TTL <= 60 seconds) */if (false == mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_CACHE, 60)) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}/* To make this example work, we must wait for a slave to catch up. Brute force style. */$attempts = 0;do {  /* check if slave has the table */  if ($res = $mysqli->query("SELECT id FROM test")) { break;  } else if ($mysqli->errno) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));  }  /* wait for slave to catch up */  usleep(200000);} while ($attempts++ < 10);/* Query has been run on a slave, result is in the cache */assert($res);var_dump($res->fetch_assoc());/* Served from cache */$res = $mysqli->query("SELECT id FROM test");?>

The example shows how to use the cache feature. First, you have to set the quality of service to eventual consistency and explicitly allow for caching. This is done by calling mysqlnd_ms_set_qos. Then, the result set of every read-only statement is cached for upto that many seconds as allowed with mysqlnd_ms_set_qos.

The actual TTL is lower or equal to the value set with mysqlnd_ms_set_qos. The value passed to the function sets the maximum age (seconds) of the data delivered. To calculate the actual TTL value the replication lag on a slave is checked and subtracted from the given value. If, for example, the maximum age is set to 60 seconds and the slave reports a lag of 10 seconds the resulting TTL is 50 seconds. The TTL is calculated individually for every cached query.

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)") || !$mysqli->query("INSERT INTO test(id) VALUES (1)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Explicitly allow eventual consistency and caching (TTL <= 60 seconds) */if (false == mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_CACHE, 60)) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}/* To make this example work, we must wait for a slave to catch up. Brute force style. */$attempts = 0;do {  /* check if slave has the table */  if ($res = $mysqli->query("SELECT id FROM test")) { break;  } else if ($mysqli->errno) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));  }  /* wait for slave to catch up */  usleep(200000);} while ($attempts++ < 10);assert($res);/* Query has been run on a slave, result is in the cache */var_dump($res->fetch_assoc());/* Served from cache */if (!($res = $mysqli->query("SELECT id FROM test"))) die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));var_dump($res->fetch_assoc());/* Update on master */if (!$mysqli->query("UPDATE test SET id = 2")) die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Read your writes */if (false == mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION)) { die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));}/* Fetch latest data */if (!($res = $mysqli->query("SELECT id FROM test"))) die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));var_dump($res->fetch_assoc());?>

The quality of service can be changed at any time to avoid further cache usage. If needed, you can switch to read your writes (session consistency). In that case, the cache will not be used and fresh data is read.

Copyright 1997-2012 the PHP Documentation Group.

By default, the plugin does not attempt to fail over if connecting to a host fails. This prevents pitfalls related to connection state. It is recommended to manually handle connection errors in a way similar to a failed transaction. You should catch the error, rebuild the connection state and rerun your query as shown below.

If connection state is no issue to you, you can alternatively enable automatic and silent failover. Depending on the configuration, the automatic and silent failover will either attempt to fail over to the master before issuing and error or, try to connect to other slaves, given the query allowes for it, before attempting to connect to a master.

  { "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "simulate_slave_failure", "port": "0" }, "slave_1": { "host": "127.0.0.1", "port": 3311 } },   "filters": { "roundrobin": [] } } }

<?php$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));$sql = "SELECT 1 FROM DUAL";/* error handling as it should be done regardless of the plugin */if (!($res = $link->query($sql))) {  /* plugin specific: check for connection error */  switch ($link->errno) { case 2002: case 2003: case 2005:  printf("Connection error - trying next slave!\n");  /* load balancer will pick next slave */  $res = $link->query($sql);  break; default:  /* no connecion error, failover is unlikely to help */  die(sprintf("SQL error: [%d] %s", $link->errno, $link->error));  break;  }}if ($res) {  var_dump($res->fetch_assoc());}?>

Copyright 1997-2012 the PHP Documentation Group.

The mysqlnd replication and load balancing plugin is implemented as a PHP extension. It is written in C and operates under the hood of PHP. During the startup of the PHP interpreter, in the module init phase of the PHP engine, it gets registered as a mysqlnd plugin to replace selected mysqlnd C methods.

At PHP runtime, it inspects queries sent from mysqlnd (PHP) to the MySQL server. If a query is recognized as read-only, it will be sent to one of the configured slave servers. Statements are considered read-only if they either start with SELECT, the SQL hint /*ms=slave*/ or a slave had been chosen for running the previous query, and the query started with the SQL hint /*ms=last_used*/. In all other cases, the query will be sent to the MySQL replication master server.

For better portability, applications should use the MYSQLND_MS_MASTER_SWITCH , MYSQLND_MS_SLAVE_SWITCH , and MYSQLND_MS_LAST_USED_SWITCH predefined mysqlnd_ms constants, instead of their literal values, such as /*ms=slave*/.

The plugin handles the opening and closing of database connections to both master and slave servers. From an application point of view, there continues to be only one connection handle. However, internally, this one public connection handle represents a pool of network connections that are managed by the plugin. The plugin proxies queries to the master server, and to the slaves using multiple connections.

Database connections have a state consisting of, for example, transaction status, transaction settings, character set settings, and temporary tables. The plugin will try to maintain the same state among all internal connections, whenever this can be done in an automatic and transparent way. In cases where it is not easily possible to maintain state among all connections, such as when using BEGIN TRANSACTION, the plugin leaves it to the user to handle.

Copyright 1997-2012 the PHP Documentation Group.

The replication and load balancing plugin changes the semantics of a PHP MySQL connection handle. The existing API of the PHP MySQL extensions (mysqli, mysql, and PDO_MYSQL) are not changed in a way that functions are added or removed. But their behaviour changes when using the plugin. Existing applications do not need to be adapted to a new API, but they may need to be modified because of the behaviour changes.

The plugin breaks the one-by-one relationship between a mysqli, mysql, and PDO_MYSQL connection handle and a MySQL network connection. And a mysqli, mysql, and PDO_MYSQL connection handle represents a local pool of connections to the configured MySQL replication master and MySQL replication slave servers. The plugin redirects queries to the master and slave servers. At some point in time one and the same PHP connection handle may point to the MySQL master server. Later on, it may point to one of the slave servers or still the master. Manipulating and replacing the network connection referenced by a PHP MySQL connection handle is not a transparent operation.

Every MySQL connection has a state. The state of the connections in the connection pool of the plugin can differ. Whenever the plugin switches from one wire connection to another, the current state of the user connection may change. The applications must be aware of this.

The following list shows what the connection state consists of. The list may not be complete.

Connection switches happen right before queries are executed. The plugin does not switch the current connection until the next statement is executed.

Broadcasted messages

The plugins philosophy is to align the state of connections in the pool only if the state is under full control of the plugin, or if it is necessary for security reasons. Just a few actions that change the state of the connection fall into this category.

The following is a list of connection client library calls that change state, and are broadcasted to all open connections in the connection pool.

If any of the listed calls below are to be executed, the plugin loops over all open master and slave connections. The loop continues until all servers have been contacted, and the loop does not break if a server indicates a failure. If possible, the failure will propagate to the called user API function, which may be detected depending on which underlying library function was triggered.

Broadcasting and lazy connections

The plugin does not proxy or "remember" all settings to apply them on connections opened in the future. This is important to remember, if using lazy connections. Lazy connections are connections which are not opened before the client sends the first connection. Use of lazy connections is the default plugin action.

The following connection library calls each changed state, and their execution is recorded for later use when lazy connections are opened. This helps ensure that the connection state of all connections in the connection pool are comparable.

Charsets and string escaping

Due to the use of lazy connections, which are a default, it can happen that an application tries to escape a string for use within SQL statements before a connection has been established. In this case string escaping is not possible. The string escape function does not know what charset to use before a connection has been established.

To overcome the problem a new configuration setting server_charset has been introduced in version 1.4.0.

Attention has to be paid on escaping strings with a certain charset but using the result on a connection that uses a different charset. Please note, that PECL/mysqlnd_ms manipulates connections and one application level connection represents a pool of multiple connections that all may have different default charsets. It is recommended to configure the servers involved to use the same default charsets. The configuration setting server_charset does help with this situation as well. If using server_charset, the plugin will set the given charset on all newly opened connections.

Copyright 1997-2012 the PHP Documentation Group.

Transaction handling is fundamentally changed. An SQL transaction is a unit of work that is run on one database server. The unit of work consists of one or more SQL statements.

By default the plugin is not aware of SQL transactions. The plugin may switch connections for load balancing at any point in time. Connection switches may happen in the middle of a transaction. This is against the nature of an SQL transaction. By default, the plugin is not transaction safe.

Any kind of MySQL load balancer must be hinted about the begin and end of a transaction. Hinting can either be done implicitly by monitoring API calls or using SQL hints. Both options are supported by the plugin, depending on your PHP version. API monitoring requires PHP 5.4.0 or newer. The plugin, like any other MySQL load balancer, cannot detect transaction boundaries based on the MySQL Client Server Protocol. Thus, entirely transparent transaction aware load balancing is not possible. The least intrusive option is API monitoring, which requires little to no application changes, depending on your application.

Please, find examples of using SQL hints or the API monitoring in the examples section. The details behind the API monitoring, which makes the plugin transaction aware, are described below.

Beginning with PHP 5.4.0, the mysqlnd library allows this plugin to subclass the library C API call set_autocommit(), to detect the status of autocommit mode.

The PHP MySQL extensions either issue a query (such as SET AUTOCOMMIT=0|1), or use the mysqlnd library call set_autocommit() to control the autocommit setting. If an extension makes use of set_autocommit(), the plugin can be made transaction aware. Transaction awareness cannot be achieved if using SQL to set the autocommit mode. The library function set_autocommit() is called by the mysqli_autocommit and PDO::setAttribute(PDO::ATTR_AUTOCOMMIT) user API calls.

The plugin configuration option trx_stickiness=master can be used to make the plugin transactional aware. In this mode, the plugin stops load balancing if autocommit becomes disabled, and directs all statements to the master until autocommit gets enabled.

An application that does not want to set SQL hints for transactions but wants to use the transparent API monitoring to avoid application changes must make sure that the autocommit settings is changed exclusively through the listed API calls.

Copyright 1997-2012 the PHP Documentation Group.

Applications using PECL/mysqlnd_ms should implement proper error handling for all user API calls. And because the plugin changes the semantics of a connection handle, API calls may return unexpected errors. If using the plugin on a connection handle that no longer represents an individual network connection, but a connection pool, an error code and error message will be set on the connection handle whenever an error occurs on any of the network connections behind.

If using lazy connections, which is the default, connections are not opened until they are needed for query execution. Therefore, an API call for a statement execution may return a connection error. In the example below, an error is provoked when trying to run a statement on a slave. Opening a slave connection fails because the plugin configuration file lists an invalid host name for the slave.

{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "invalid_host_name", } }, "lazy_connections": 1 }}

The explicit activation of lazy connections is for demonstration purpose only.

<?php$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()));/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */if (!$mysqli->query("SET @myrole='master'")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}/* Connection 2, run on slave because SELECT, provoke connection error */if (!($res = $mysqli->query("SELECT @myrole AS _role"))) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);} else { $row = $res->fetch_assoc(); $res->close(); printf("@myrole = '%s'\n", $row['_role']);}$mysqli->close();?> 

PHP Warning:  mysqli::query(): php_network_getaddresses: getaddrinfo failed: Name or service not known in %s on line %dPHP Warning:  mysqli::query(): [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known (trying to connect via tcp://invalid_host_name:3306)in %s on line %d[2002] php_network_getaddresses: getaddrinfo failed: Name or service not known

Applications are expected to handle possible connection errors by implementing proper error handling.

Depending on the use case, applications may want to handle connection errors differently from other errors. Typical connection errors are 2002 (CR_CONNECTION_ERROR) - Can't connect to local MySQL server through socket '%s' (%d), 2003 (CR_CONN_HOST_ERROR) - Can't connect to MySQL server on '%s' (%d) and 2005 (CR_UNKNOWN_HOST) - Unknown MySQL server host '%s' (%d). For example, the application may test for the error codes and manually perform a fail over. The plugins philosophy is not to offer automatic fail over, beyond master fail over, because fail over is not a transparent operation.

{ "myapp": { "master": { "master_0": { "host": "localhost" } }, "slave": { "slave_0": { "host": "invalid_host_name" }, "slave_1": { "host": "192.168.78.136" } }, "lazy_connections": 1, "filters": { "roundrobin": [ ] } }}

Explicitly activating lazy connections is done for demonstration purposes, as is round robin load balancing as opposed to the default random once type.

<?php$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()));/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */if (!$mysqli->query("SET @myrole='master'")) { printf("[%d] %s\n", $mysqli->errno, $mysqli->error);}/* Connection 2, first slave */$res = $mysqli->query("SELECT VERSION() AS _version");/* Hackish manual fail over */if (2002 == $mysqli->errno || 2003 == $mysqli->errno || 2004 == $mysqli->errno) {  /* Connection 3, first slave connection failed, trying next slave */  $res = $mysqli->query("SELECT VERSION() AS _version");}if (!$res) {  printf("ERROR, [%d] '%s'\n", $mysqli->errno, $mysqli->error);} else { /* Error messages are taken from connection 3, thus no error */ printf("SUCCESS, [%d] '%s'\n", $mysqli->errno, $mysqli->error); $row = $res->fetch_assoc(); $res->close(); printf("version = %s\n", $row['_version']);}$mysqli->close();?> 

[1045] Access denied for user 'username'@'localhost' (using password: YES)PHP Warning:  mysqli::query(): php_network_getaddresses: getaddrinfo failed: Name or service not known in %s on line %dPHP Warning:  mysqli::query(): [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known (trying to connect via tcp://invalid_host_name:3306) in %s on line %dSUCCESS, [0] ''version = 5.6.2-m5-log

In some cases, it may not be easily possible to retrieve all errors that occur on all network connections through a connection handle. For example, let's assume a connection handle represents a pool of three open connections. One connection to a master and two connections to the slaves. The application changes the current database using the user API call mysqli_select_db, which then calls the mysqlnd library function to change the schemata. mysqlnd_ms monitors the function, and tries to change the current database on all connections to harmonize their state. Now, assume the master succeeds in changing the database, and both slaves fail. Upon the initial error from the first slave, the plugin will set an appropriate error on the connection handle. The same is done when the second slave fails to change the database. The error message from the first slave is lost.

Such cases can be debugged by either checking for errors of the type E_WARNING (see above) or, if no other option, investigation of the mysqlnd_ms debug and trace log.

Copyright 1997-2012 the PHP Documentation Group.

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 resonable stable. Syntax and semantics may change in future versions.

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.

Copyright 1997-2012 the PHP Documentation Group.

Four load balancing strategies are supported to distribute statements over the configured MySQL slave servers:

The load balancing policy is configured in the plugins configuration file using the random, roundrobin, and user filters.

Servers can be prioritized assigning a weight. A server that has been given a weight of two will get twice as many requests as a server that has been given the default weight of one. Prioritization can be handy in heterogenous environments. For example, you may want to assign more requests to a powerful machine than to a less powerful. Or, you may have configured servers that are close or far from the client, thus expose different latencies.

Copyright 1997-2012 the PHP Documentation Group.

The plugin executes read-only statements on the configured MySQL slaves, and all other queries on the MySQL master. Statements are considered read-only if they either start with SELECT, the SQL hint /*ms=slave*/, or if a slave had been chosen for running the previous query and the query starts with the SQL hint /*ms=last_used*/. In all other cases, the query will be sent to the MySQL replication master server. It is recommended to use the constants MYSQLND_MS_SLAVE_SWITCH , MYSQLND_MS_MASTER_SWITCH and MYSQLND_MS_LAST_USED_SWITCH instead of /*ms=slave*/. See also the list of mysqlnd_ms constants.

SQL hints are a special kind of standard compliant SQL comments. The plugin does check every statement for certain SQL hints. The SQL hints are described within the mysqlnd_ms constants documentation, constants that are exported by the extension. Other systems involved with the statement processing, such as the MySQL server, SQL firewalls, and SQL proxies, are unaffected by the SQL hints, because those systems are designed to ignore SQL comments.

The built-in read-write splitter can be replaced by a user-defined filter, see also the user filter documentation.

A user-defined read-write splitter can request the built-in logic to send a statement to a specific location, by invoking mysqlnd_ms_is_select.

Copyright 1997-2012 the PHP Documentation Group.

filters. PHP applications that implement a MySQL replication cluster must first identify a group of servers in the cluster which could execute a statement before the statement is executed by one of the candidates. In other words: a defined list of servers must be filtered until only one server is available.

The process of filtering may include using one or more filters, and filters can be chained. And they are executed in the order they are defined in the plugins configuration file.

Available filters:

The random filter implements the 'random' and 'random once' load balancing policies. The 'round robin' load balancing can be configured through the roundrobin filter. Setting a 'user defined callback' for server selection is possible with the user filter. The quality_of_service filter finds cluster nodes capable of delivering a certain service, for example, read-your-writes or, not lagging more seconds behind the master than allowed.

Filters can accept parameters to change their behaviour. The random filter accepts an optional sticky parameter. If set to true, the filter changes load balancing from random to random once. Random picks a random server every time a statement is to be executed. Random once picks a random server when the first statement is to be executed and uses the same server for the rest of the PHP request.

One of the biggest strength of the filter concept is the possibility to chain filters. This strength does not become immediately visible because tje random, roundrobin and user filters are supposed to output no more than one server. If a filter reduces the list of candidates for running a statement to only one server, it makes little sense to use that one server as input for another filter for further reduction of the list of candidates.

An example filter sequence that will fail:

A second type of filter exists: multi filter. A multi filter emits zero, one or multiple servers after processing. The quality_of_service filter is an example. If the service quality requested sets an upper limit for the slave lag and more than one slave is lagging behind less than the allowed number of seconds, the filter returns more than one cluster node. A multi filter must be followed by other to further reduce the list of candidates for statement execution until a candidate is found.

A filter sequence with the quality_of_service multi filter followed by a load balancing filter.

A filter sequence must not end with a multi filter. If trying to use a filter sequence which ends with a multi filter the plugin may emit a warning like (mysqlnd_ms) Error in configuration. Last filter is multi filter. Needs to be non-multi one. Stopping in %s on line %d. Furthermore, an appropriate error on the connection handle may be set.

Copyright 1997-2012 the PHP Documentation Group.

The plugin can be used with different kinds of MySQL database clusters. Different clusters can deliver different levels of service to applications. The service levels can be grouped by the data consistency levels that can be achieved. The plugin knows about:

Depending how a cluster is used it may be possible to achieve higher service levels than the default one. For example, a read from an asynchronous MySQL replication slave is eventual consistent. Thus, one may say the default consistency level of a MySQL replication cluster is eventual consistency. However, if the master only is used by a client for reading and writing during a session, session consistency (read your writes) is given. PECL mysqlnd 1.2.0 abstracts the details of choosing an appropriate node for any of the above service levels from the user.

Service levels can be set through the qualify-of-service filter in the plugins configuration file and at runtime using the function mysqlnd_ms_set_qos.

The plugin defines the different service levels as follows.

Eventual consistency is the default service provided by an asynchronous cluster, such as classical MySQL replication. A read operation executed on an arbitrary node may or may not return stale data. The applications view of the data is eventual consistent.

Session consistency is given if a client can always read its own writes. An asynchronous MySQL replication cluster can deliver session consistency if clients always use the master after the first write or never query a slave which has not yet replicated the clients write operation.

The plugins understanding of strong consistency is that all clients always see the committed writes of all other clients. This is the default when using MySQL Cluster or any other cluster offering synchronous data distribution.

Service level parameters

Eventual consistency and session consistency service level accept parameters.

Eventual consistency is the service provided by classical MySQL replication. By default, all nodes qualify for read requests. An optional age parameter can be given to filter out nodes which lag more than a certain number of seconds behind the master. The plugin is using SHOW SLAVE STATUS to measure the lag. Please, see the MySQL reference manual to learn about accuracy and reliability of the SHOW SLAVE STATUS command.

Session consistency (read your writes) accepts an optional GTID parameter to consider reading not only from the master but also from slaves which already have replicated a certain write described by its transaction identifier. This way, when using asynchronous MySQL replication, read requests may be load balanced over slaves while still ensuring session consistency.

The latter requires the use of client-side global transaction id injection.

Advantages of the new approach

The new approach supersedes the use of SQL hints and the configuration option master_on_write in some respects. If an application running on top of an asynchronous MySQL replication cluster cannot accept stale data for certain reads, it is easier to tell the plugin to choose appropriate nodes than prefixing all read statements in question with the SQL hint to enforce the use of the master. Furthermore, the plugin may be able to use selected slaves for reading.

The master_on_write configuration option makes the plugin use the master after the first write (session consistency, read your writes). In some cases, session consistency may not be needed for the rest of the session but only for some, few read operations. Thus, master_on_write may result in more read load on the master than necessary. In those cases it is better to request a higher than default service level only for those reads that actually need it. Once the reads are done, the application can return to default service level. Switching between service levels is only possible using mysqlnd_ms_set_qos.

Performance considerations

A MySQL replication cluster cannot tell clients which slaves are capable of delivering which level of service. Thus, in some cases, clients need to query the slaves to check their status. PECL mysqlnd_ms transparently runs the necessary SQL in the background. However, this is an expensive and slow operation. SQL statements are run if eventual consistency is combined with an age (slave lag) limit and if session consistency is combined with a global transaction ID.

If eventual consistency is combined with an maximum age (slave lag), the plugin selects candidates for statement execution and load balancing for each statement as follows. If the statement is a write all masters are considered as candidates. Slaves are not checked and not considered as candidates. If the statement is a read, the plugin transparently executes SHOW SLAVE STATUS on every slaves connection. It will loop over all connections, send the statement and then start checking for results. Usually, this is slightly faster than a loop over all connections in which for every connection a query is send and the plugin waits for its results. A slave is considered a candidate if SHOW SLAVE STATUS reports Slave_IO_Running=Yes, Slave_SQL_Running=Yes and Seconds_Behind_Master is less or equal than the allowed maximum age. In case of an SQL error, the plugin emits a warning but does not set an error on the connection. The error is not set to make it possible to use the plugin as a drop-in.

If session consistency is combined with a global transaction ID, the plugin executes the SQL statement set with the fetch_last_gtid entry of the global_transaction_id_injection section from the plugins configuration file. Further details are identical to those described above.

In version 1.2.0 no additional optimizations are done for executing background queries. Future versions may contain optimizations, depending on user demand.

If no parameters and options are set, no SQL is needed. In that case, the plugin consider all nodes of the type shown below.

Throttling

The quality of service filter can be combied with Global transaction IDs to throttle clients. Throttling does reduce the write load on the master by slowing down clients. If session consistency is requested and global transactions idenentifier are used to check the status of a slave, the check can be done in two ways. By default a slave is checked and skipped immediately if it does not match the criteria for session consistency. Alternatively, the plugin can wait for a slave to catch up to the master until session consistency is possible. To enable the throttling, you have to set wait_for_gtid_timeout configuration option.

Copyright 1997-2012 the PHP Documentation Group.

Idea and client-side emulation

PECL/mysqlnd_ms can do client-side transparent global transaction ID injection. In its most basic form, a global transaction identifier is a counter which is incremented for every transaction executed on the master. The counter is held in a table on the master. Slaves replicate the counter table.

In case of a master failure a database administrator can easily identify the most recent slave for promiting it as a new master. The most recent slave has the highest transaction identifier.

Application developers can ask the plugin for the global transaction identifier (GTID) for their last successful write operation. The plugin will return an identifier that refers to an transaction no older than that of the clients last write operation. Then, the GTID can be passed as a parameter to the quality of service (QoS) filter as an option for session consistency. Session consistency ensures read your writes. The filter ensures that all reads are either directed to a master or a slave which has replicated the write referenced by the GTID.

When injection is done

The plugin transparently maintains the GTID table on the master. In autocommit mode the plugin injects an UPDATE statement before executing the users statement for every master use. In manual transaction mode, the injection is done before the application calls commit() to close a transaction. The configuration option report_error of the GTID section in the plugins configuration file is used to control whether a failed injection shall abort the current operation or be ignored silently (default).

Please note, the PHP version requirements for transaction boundary monitoring and their limits.

Limitations

Client-side global transaction ID injection has shortcomings. The potential issues are not specific to PECL/mysqlnd_ms but are rather of general nature.

Using server-side global transaction identifier

Starting with PECL/mysqlnd_ms 1.3.0-alpha the MySQL 5.6.5-m8 or newer built-in global transaction identifier feature is supported. Use of the server feature lifts all of the above listed limitations. Please, see the MySQL Reference Manual for limitations and preconditions for using server built-in global transaction identifiers.

Whether to use the client-side emulation or the server built-in functionality is a question not directly related to the plugin, thus it is not discussed in depth. There are no plans to remove the client-side emulation and you can continue to use it, if the server-side solution is no option. This may be the case in heterogenous environments with old MySQL server or, if any of the server-side solution limitations is not acceptable.

From an applications perspective there is hardly a difference in using one or the other approach. The following properties differ.

Copyright 1997-2012 the PHP Documentation Group.

Support for MySQL replication clusters (asynchronous primary copy) is the main focus of PECL/mysqlnd_ms. The slaves of a MySQL replication cluster may or may not reflect the latest updates from the master. Slaves are asynchronous and can lag behind the master. A read from a slave is eventual consistent from a cluster-wide perspective.

The same level of consistency is offered by a local cache using time-to-live (TTL) invalidation strategy. Current data or stale data may be served. Eventually, data searched for in the cache is not available and the source of the cache needs to be accessed.

Given that both a MySQL Replication slave (asynchronous secondary) and a local TTL-driven cache deliver the same level of service it is possible to transparently replace a remote database access with a local cache access to gain better possibility.

As of PECL/mysqlnd_ms 1.3.0-beta the plugin is capable of transparently controlling PECL/mysqlnd_ms 1.1.0-alpha or newer to cache a read-only query if explicitly allowed by setting an appropriate quality of service through mysqlnd_ms_set_qos. Please, see the quickstart for a code example. Both plugins must be installed, PECL/mysqlnd_ms must be compiled to support the cache feature and PHP 5.4.0 or newer has to be used.

Applications have full control of cache usage and can request fresh data at any time, if need be. Thec ache usage can be enabled and disabled time during the execution of a script. The cache will be used if mysqlnd_ms_set_qos sets the quality of service to eventual consistency and enables cache usage. Cache usage is disabled by requesting higher consistency levels, for example, session consistency (read your writes). Once the quality of service has been relaxed to eventual consistency the cache can be used again.

If caching is enabled for a read-only statement, PECL/mysqlnd_ms may inject SQL hints to control caching by PECL/mysqlnd_qc. It may modify the SQL statement it got from the application. Subsequent SQL processors are supposed to ignore the SQL hints. A SQL hint is a SQL comment. Comments must not be ignored, for example, by the database server.

The TTL of a cache entry is computed on a per statement basis. Applications set an maximum age for the data they want to retrieve using mysqlnd_ms_set_qos. The age sets an approximate upper limit of how many seconds the data returned may lag behind the master.

The following logic is used to compute the actual TTL if caching is enabled. The logic takes the estimated slave lag into account for choosing a TTL. If, for example, there are two slaves lagging 5 and 10 seconds behind and the maximum age allowed is 60 seconds, the TTL is set to 50 seconds. Please note, the age setting is no more than an estimated guess.

The algorithm may seem expensive. SHOW SLAVE STATUS is a very fast operation. Given a sufficient number of requests and cache hits per second the cost of checking the slaves lag can easily outweight the costs of the cache decision.

Suggestions on a better algorithm are always welcome.

Copyright 1997-2012 the PHP Documentation Group.

Any application using any kind of MySQL cluster is faced with the same tasks:

The plugin is optimized for fulfilling these tasks in the context of a classical asynchronous MySQL replication cluster consisting of a single master and many slaves (primary copy). When using classical, asynchronous MySQL replication all of the above listed tasks need to be mastered at the client side.

Other types of MySQL cluster may have lower requirements on the application side. For example, if all nodes in the cluster can answer read and write requests, no read-write splitting needs to be done (multi-master, update-all). If all nodes in the cluster are synchronous, they automatically provide the highest possible quality of service which makes choosing a node easier. In this case, the plugin may serve the application after some reconfiguration to disable certain features, such as built-in read-write splitting.

Using asynchronous clusters with single master

Primary use case of the plugin. Follow the hints given in the descriptions of each feature.

Using asynchronous clusters with multiple masters

This setup is currently unsupported.

The plugin has no built-in functionality to direct certain writes to certain masters. It is considered to add table filtering to future versions. Table filter would allow restricting both read and writes to certain slaves and masters based on the database/schema and table used by a statement.

A table filtering feature is prepared in the plugins source code. However, it is instable and must not be used. Bug reports on table filtering will be rejected.

Using synchronous clusters such as MySQL Cluster

MySQL Cluster is a synchronous cluster solution. All cluster nodes accept read and write requests. In the context of the plugin, all nodes shall be considered as masters.

Use the load balancing and fail over features only.

Disabling built-in read-write splitting.

Configure masters only.

Copyright 1997-2012 the PHP Documentation Group.

PHP 5.3.6 or newer. Some advanced functionality requires PHP 5.4.0 or newer.

The mysqlnd_ms replication and load balancing plugin supports all PHP applications and all available PHP MySQL extensions (mysqli, mysql, PDO_MYSQL). The PHP MySQL extension must be configured to use mysqlnd in order to be able to use the mysqlnd_ms plugin for mysqlnd.

Copyright 1997-2012 the PHP Documentation Group.

This PECL extension is not bundled with PHP.

Information for installing this PECL extension may be found in the manual chapter titled Installation of PECL extensions. Additional information such as new releases, downloads, source files, maintainer information, and a CHANGELOG, can be located here: http://pecl.php.net/package/mysqlnd_ms

A DLL for this PECL extension is currently unavailable. See also the building on Windows section.

Copyright 1997-2012 the PHP Documentation Group.

The behaviour of these functions is affected by settings in php.ini.

Here's a short explanation of the configuration directives.

Copyright 1997-2012 the PHP Documentation Group.

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.ini_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.

<?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.

{ "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.

"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.

"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.

{ "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.

   { "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.

"filters": { "user": { "callback": "pick_server" }}

Here is a short explanation of the configuration directives that can be used.

Copyright 1997-2012 the PHP Documentation Group.

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.

[myapp]master[] = localhostslave[] = 192.168.2.27slave[] = 192.168.2.28:3306[localhost]master[] = localhost:/tmp/mysql/mysql.sockslave[] = 192.168.3.24:3305slave[] = 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.

[myapp]master[] = mysql_master_serverslave[] = mysql_slave_1slave[] = mysql_slave_2

Here is a short explanation of the configuration directives that can be used.

Copyright 1997-2012 the PHP Documentation Group.

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"));

Copyright 1997-2012 the PHP Documentation Group.

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  

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=0state=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.

Copyright 1997-2012 the PHP Documentation Group.

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.

mysqlnd_ms.enable=1mysqlnd_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.

mysqlnd_ms.enable=1mysqlnd_ms.collect_statistics=1error_log=/tmp/php_errors.log 

<?phpfunction 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");?>

Copyright 1997-2012 the PHP Documentation Group.

Description

Returns a global transaction identifier which belongs to a write operation no older than the last write performed by the client. It is not guaranteed that the global transaction identifier is identical to that one created for the last write transaction performed by the client.

Parameters

Return Values

Returns a global transaction ID (GTID) on success. Otherwise, returns FALSE .

The function mysqlnd_ms_get_last_gtid returns the GTID obtained when executing the SQL statement from the fetch_last_gtid entry of the global_transaction_id_injection section from the plugins configuration file.

The function may be called after the GTID has been incremented.

Notes

Examples

<?php/* Open mysqlnd_ms connection using mysqli, PDO_MySQL or mysql extension */$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("DROP TABLE IF EXISTS test"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("CREATE TABLE test(id INT)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));?>

See Also

Copyright 1997-2012 the PHP Documentation Group.

Description

Returns an array which describes the last used connection from the plugins connection pool currently pointed to by the user connection handle. If using the plugin, a user connection handle represents a pool of database connections. It is not possible to tell from the user connection handles properties to which database server from the pool the user connection handle points.

The function can be used to debug or monitor PECL mysqlnd_ms.

Parameters

Return Values

FALSE on error. Otherwise, an array which describes the connection used to execute the last statement on.

Array which describes the connection.

Notes

Examples

The example assumes that myapp refers to a plugin configuration file section and represents a connection pool.

<?php$link = new mysqli("myapp", "user", "password", "database");$res = $link->query("SELECT 1 FROM DUAL");var_dump(mysqlnd_ms_get_last_used_connection($link));?> 

array(10) {  ["scheme"]=>  string(22) "unix:///tmp/mysql.sock"  ["host_info"]=>  string(25) "Localhost via UNIX socket"  ["host"]=>  string(0) ""  ["port"]=>  int(3306)  ["socket_or_pipe"]=>  string(15) "/tmp/mysql.sock"  ["thread_id"]=>  int(46253)  ["last_message"]=>  string(0) ""  ["errno"]=>  int(0)  ["error"]=>  string(0) ""  ["sqlstate"]=>  string(5) "00000"}

Copyright 1997-2012 the PHP Documentation Group.

Description

Returns an array of statistics collected by the replication and load balancing plugin.

The PHP configuration setting mysqlnd_ms.collect_statistics controls the collection of statistics. The collection of statistics is disabled by default for performance reasons.

The scope of the statistics is the PHP process. Depending on your deployment model a PHP process may handle one or multiple requests.

Statistics are aggregated for all connections and all storage handler. It is not possible to tell how much queries originating from mysqli, PDO_MySQL or mysql API calls have contributed to the aggregated data values.

Parameters

This function has no parameters.

Return Values

Returns NULL if the PHP configuration directive mysqlnd_ms.enable has disabled the plugin. Otherwise, returns array of statistics.

Array of statistics

Examples

<?phpprintf("mysqlnd_ms.enable = %d\n", ini_get("mysqlnd_ms.enable"));printf("mysqlnd_ms.collect_statistics = %d\n", ini_get("mysqlnd_ms.collect_statistics"));var_dump(mysqlnd_ms_get_stats());?> 

mysqlnd_ms.enable = 1mysqlnd_ms.collect_statistics = 1array(26) {  ["use_slave"]=>  string(1) "0"  ["use_master"]=>  string(1) "0"  ["use_slave_guess"]=>  string(1) "0"  ["use_master_guess"]=>  string(1) "0"  ["use_slave_sql_hint"]=>  string(1) "0"  ["use_master_sql_hint"]=>  string(1) "0"  ["use_last_used_sql_hint"]=>  string(1) "0"  ["use_slave_callback"]=>  string(1) "0"  ["use_master_callback"]=>  string(1) "0"  ["non_lazy_connections_slave_success"]=>  string(1) "0"  ["non_lazy_connections_slave_failure"]=>  string(1) "0"  ["non_lazy_connections_master_success"]=>  string(1) "0"  ["non_lazy_connections_master_failure"]=>  string(1) "0"  ["lazy_connections_slave_success"]=>  string(1) "0"  ["lazy_connections_slave_failure"]=>  string(1) "0"  ["lazy_connections_master_success"]=>  string(1) "0"  ["lazy_connections_master_failure"]=>  string(1) "0"  ["trx_autocommit_on"]=>  string(1) "0"  ["trx_autocommit_off"]=>  string(1) "0"  ["trx_master_forced"]=>  string(1) "0"  ["gtid_autocommit_injections_success"]=>  string(1) "0"  ["gtid_autocommit_injections_failure"]=>  string(1) "0"  ["gtid_commit_injections_success"]=>  string(1) "0"  ["gtid_commit_injections_failure"]=>  string(1) "0"  ["gtid_implicit_commit_injections_success"]=>  string(1) "0"  ["gtid_implicit_commit_injections_failure"]=>  string(1) "0"}

See Also

Copyright 1997-2012 the PHP Documentation Group.

Description

Finds whether a table name matches a wildcard pattern or not.

This function is not of much practical relevance with PECL mysqlnd_ms 1.1.0 because the plugin does not support MySQL replication table filtering yet.

Parameters

Return Values

Returns TRUE table_name is matched by wildcard. Otherwise, returns FALSE

Examples

<?phpvar_dump(mysqlnd_ms_match_wild("schema_name.table_name", "schema%"));var_dump(mysqlnd_ms_match_wild("abc", "_"));var_dump(mysqlnd_ms_match_wild("table1", "table_"));var_dump(mysqlnd_ms_match_wild("asia_customers", "%customers"));var_dump(mysqlnd_ms_match_wild("funny%table","funny\%table"));var_dump(mysqlnd_ms_match_wild("funnytable", "funny%table"));?> 

bool(true)bool(false)bool(true)bool(true)bool(true)bool(true)

Copyright 1997-2012 the PHP Documentation Group.

Description

Finds whether to send the query to the master, the slave or the last used MySQL server.

The plugins built-in read/write split mechanism will be used to analyze the query string to make a recommendation where to send the query. The built-in read/write split mechanism is very basic and simple. The plugin will recommend sending all queries to the MySQL replication master server but those which begin with SELECT, or begin with a SQL hint which enforces sending the query to a slave server. Due to the basic but fast algorithm the plugin may propose to run some read-only statements such as SHOW TABLES on the replication master.

Parameters

Return Values

A return value of MYSQLND_MS_QUERY_USE_MASTER indicates that the query should be send to the MySQL replication master server. The function returns a value of MYSQLND_MS_QUERY_USE_SLAVE if the query can be run on a slave because it is considered read-only. A value of MYSQLND_MS_QUERY_USE_LAST_USED is returned to recommend running the query on the last used server. This can either be a MySQL replication master server or a MySQL replication slave server.

If read write splitting has been disabled by setting mysqlnd_ms.disable_rw_split, the function will always return MYSQLND_MS_QUERY_USE_MASTER or MYSQLND_MS_QUERY_USE_LAST_USED .

Examples

<?phpfunction is_select($query){ switch (mysqlnd_ms_query_is_select($query)) {  case MYSQLND_MS_QUERY_USE_MASTER:   printf("'%s' should be run on the master.\n", $query);   break;  case MYSQLND_MS_QUERY_USE_SLAVE:   printf("'%s' should be run on a slave.\n", $query);   break;  case MYSQLND_MS_QUERY_USE_LAST_USED:   printf("'%s' should be run on the server that has run the previous query\n", $query);   break;  default:   printf("No suggestion where to run the '%s', fallback to master recommended\n", $query);   break; }}is_select("INSERT INTO test(id) VALUES (1)");is_select("SELECT 1 FROM DUAL");is_select("/*" . MYSQLND_MS_LAST_USED_SWITCH . "*/SELECT 2 FROM DUAL");?> 

INSERT INTO test(id) VALUES (1) should be run on the master.SELECT 1 FROM DUAL should be run on a slave./*ms=last_used*/SELECT 2 FROM DUAL should be run on the server that has run the previous query

See Also

Copyright 1997-2012 the PHP Documentation Group.

Description

Sets the quality of service needed from the cluster. A database cluster delivers a certain quality of service to the user depending on its architecture. A major aspect of the quality of service is the consistency level the cluster can offer. An asynchronous MySQL replication cluster defaults to eventual consistency for slave reads: a slave may serve stale data, current data, or it may have not the requested data at all, because it is not synchronous to the master. In a MySQL replication cluster, only master accesses can give strong consistency, which promises that all clients see each others changes.

PECL/mysqlnd_ms hides the complexity of choosing appropriate nodes to achieve a certain level of service from the cluster. The "Quality of Service" filter implements the necessary logic. The filter can either be configured in the plugins configuration file, or at runtime using mysqlnd_ms_set_qos.

Similar results can be achieved with PECL mysqlnd_ms < 1.2.0, if using SQL hints to force the use of a certain type of node or using the master_on_write plugin configuration option. The first requires more code and causes more work on the application side. The latter is less refined than using the quality of service filter. Settings made through the function call can be reversed, as shown in the example below. The example temporarily switches to a higher service level (session consistency, read your writes) and returns back to the clusters default after it has performed all operations that require the better service. This way, read load on the master can be minimized compared to using master_on_write, which would continue using the master after the first write.

Parameters

Return Values

Returns TRUE if the connections service level has been switched to the requested. Otherwise, returns FALSE

Notes

Examples

<?php/* Open mysqlnd_ms connection using mysqli, PDO_MySQL or mysql extension */$mysqli = new mysqli("myapp", "username", "password", "database");if (!$mysqli)  /* Of course, your error handling is nicer... */  die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));/* Session consistency: read your writes */$ret = mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION);if (!$ret)  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Will use master and return fresh data, client can see his last write */if (!$res = $mysqli->query("SELECT item, price FROM orders WHERE order_id = 1"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Back to default: use of all slaves and masters permitted, stale data can happen */if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));?>

See Also

Copyright 1997-2012 the PHP Documentation Group.

Description

Sets a callback for user-defined read/write splitting. The plugin will call the callback only if pick[]=user is the default rule for server picking in the relevant section of the plugins configuration file.

The plugins built-in read/write query split mechanism decisions can be overwritten in two ways. The easiest way is to prepend the 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.

Parameters

Return Values

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 fallback to the second pick method configured via the pick[] setting in the plugin configuration file. If not second pick method is given, the plugin falls back to the build-in default pick method for server selection.

Notes

Examples

[myapp]master[] = localhostslave[] = 192.168.2.27:3306slave[] = 192.168.78.136:3306pick[] = user 

<?phpfunction pick_server($connected, $query, $master, $slaves, $last_used){ 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 = $master[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;   break; } printf("... ret = '%s'\n", $ret); return $ret;}mysqlnd_ms_set_user_pick_server("pick_server");$mysqli = new mysqli("myapp", "root", "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'

See Also

Copyright 1997-2012 the PHP Documentation Group.

1.5.0-alpha

Bug fixes

Feature changes

Copyright 1997-2012 the PHP Documentation Group.

1.4.1-beta

Bug fixes

1.4.0-alpha

Feature changes

Copyright 1997-2012 the PHP Documentation Group.

1.3.2-stable

Bug fixes

1.3.1-beta

Bug fixes

1.3.0-alpha

The 1.3 series aims to improve the performance of applications and the overall load of an asynchronous MySQL cluster, for example, a MySQL cluster using MySQL Replication. This is done by transparently replacing a slave access with a local cache access, if the application allows it by setting an appropriate quality of service flag. When using MySQL replication a slave can serve stale data. An application using MySQL replication must continue to work correctly with stale data. Given that the application is know to work correctly with stale data, the slave access can transparently be replace with a local cache access.

PECL/mysqlnd_qc serves as a cache backend. PECL/mysqlnd_qc supports use of various storage locations, among others main memory, APC and MEMCACHE.

Feature changes

Copyright 1997-2012 the PHP Documentation Group.

1.2.1-beta

Minor test changes.

1.2.0-alpha

In version 1.2 the focus continues to be on supporting MySQL database clusters with asynchronous replication. The plugin tries to make using the cluster introducing a quality-of-service filter which applications can use to define what service quality they need from the cluster. Service levels provided are eventual consistency with optional maximum age/slave slag, session consistency and strong consistency.

Additionally the plugin can do client-side global transaction id injection to make manual master failover easier.

Feature changes

Copyright 1997-2012 the PHP Documentation Group.

1.1.0

The 1.1 and 1.0 series expose a similar feature set. Internally, the 1.1 series has been refactored to plan for future feature additions. A new configuration file format has been introduced, and limitations have been lifted. And the code quality and quality assurance has been improved.

Feature changes

Bug fixes

The mysqlnd_ms_set_user_pick_server function was removed, and replaced in favor of a new user filter. You can no longer set a callback function using mysqlnd_ms_set_user_pick_server at runtime, but instead have to configure it in the plugins configuration file. The user filter will pass the same arguments to the callback as before. Therefore, you can continue to use the same procedural function as a callback.callback It is no longer possible to use static class methods, or class methods of an object instance, as a callback. Doing so will cause the function executing a statement handled by the plugin to emit an E_RECOVERABLE_ERROR level error, which might look like: "(mysqlnd_ms) Specified callback (picker) is not a valid callback." Note: this may halt your application.

Copyright 1997-2012 the PHP Documentation Group.

1.0.1-alpha

1.0.0-alpha

The first release of practical use. It features basic automatic read-write splitting, SQL hints to overrule automatic redirection, load balancing of slave requests, lazy connections, and optional, automatic use of the master after the first write.

The public feature set is close to that of the 1.1 release.

1.0.0-pre-alpha

Initial check-in. Essentially a demo of the mysqlnd plugin API.