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

<?php
printf("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 = 1
mysqlnd_ms.collect_statistics = 1
array(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

<?php
var_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

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

Since 1.5.0 calls will fail when done in the middle of a transaction if transaction stickiness is enabled and transaction boundaries have been detected. properly.

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[] = localhost
slave[] = 192.168.2.27:3306
slave[] = 192.168.78.136:3306
pick[] = user

    

<?php

function 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