Mysqlnd replication and load balancing plugin (
mysqlnd_ms
)
2564
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.
20.7.6.4.4. SQL Hints
Copyright 1997-2012 the PHP Documentation Group. [2230]
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.
Example 20.224. Plugin config with one slave and one master
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27",
"port": "3306"
}
}
}
}
Example 20.225. SQL hints to prevent connection switches
<?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);
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
