MYSQLND_MS_QUERY_IS_SELECT(3) 1 MYSQLND_MS_QUERY_IS_SELECT(3)mysqlnd_ms_query_is_select - Find whether to send the query to the master, the slave or the last used MySQL serverSYNOPSIS
int mysqlnd_ms_query_is_select (string $query)
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
o $query
- Query string to test.
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
Example #1
mysqlnd_ms_query_is_select(3) example
<?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.
", $query);
break;
case MYSQLND_MS_QUERY_USE_SLAVE:
printf("'%s' should be run on a slave.
", $query);
break;
case MYSQLND_MS_QUERY_USE_LAST_USED:
printf("'%s' should be run on the server that has run the previous query
", $query);
break;
default:
printf("No suggestion where to run the '%s', fallback to master recommended
", $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");
?>
The above example will output:
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
Predefined Constants, user filter .Runtime configuration, mysqlnd_ms.disable_rw_split, mysqlnd_ms.enable.
PHP Documentation Group MYSQLND_MS_QUERY_IS_SELECT(3)
Check Out this Related Man Page
MYSQLND_MS_SET_USER_PICK_SERVER(3) 1 MYSQLND_MS_SET_USER_PICK_SERVER(3)mysqlnd_ms_set_user_pick_server - Sets a callback for user-defined read/write splitting
SYNOPSIS
bool mysqlnd_ms_set_user_pick_server (string $function)
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 con-
trol, 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 estab-
lishing 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
o $function
- The function to be called. Class methods may also be invoked statically using this function by passing array($classname,
$methodname) to this parameter. Additionally class methods of an object instance may be called by passing array($objectinstance,
$methodname) to this parameter.
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 call-
back returns a value neither found in the master nor in the slave connection lists the plugin will fallback to the second pick method con-
figured 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
Note
mysqlnd_ms_set_user_pick_server(3) is available with PECL mysqlnd_ms < 1.1.0. It has been replaced by the user filter. Please, check
the Change History for upgrade notes.
EXAMPLES
Example #1
mysqlnd_ms_set_user_pick_server(3) example
[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'...
", $connected);
printf("... deciding where to run '%s'
", $query);
$where = mysqlnd_ms_query_is_select($query);
switch ($where)
{
case MYSQLND_MS_QUERY_USE_MASTER:
printf("... using master
");
$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
");
$ret = $slaves[0];
}
else
{
/* round robin */
printf("... some read-only query for a slave
");
$ret = $slaves[$slave_idx++ % $num_slaves];
}
break;
case MYSQLND_MS_QUERY_LAST_USED:
printf("... using last used server
");
$ret = $last_used;
break;
}
printf("... ret = '%s'
", $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
", $mysqli->errno, $mysqli->error);
else
$res->close();
if (!($res = $mysqli->query("SELECT 2 FROM DUAL")))
printf("[%d] %s
", $mysqli->errno, $mysqli->error);
else
$res->close();
if (!($res = $mysqli->query("SELECT * FROM table_on_slave_a_only")))
printf("[%d] %s
", $mysqli->errno, $mysqli->error);
else
$res->close();
$mysqli->close();
?>
The above example will output:
User has connected to 'myapp'...
User has connected to 'myapp'...
User has connected to 'myapp'...
SEE ALSO mysqlnd_ms_query_is_select(3), Filter concept, user filter .
PHP Documentation Group MYSQLND_MS_SET_USER_PICK_SERVER(3)