SQL hints can be used to force the plugin to pick a certain server from the connection pool. Hinting the plugin to use a certain server 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 infere with other programs such as the MySQL Server, the MySQL Proxy or a firewall.
Three SQL hints are supported by the plugin: MYSQLND_MS_MASTER_SWITCH, MYSQLND_MS_SLAVE_SWITCH and MYSQLND_MS_LAST_USED_SWITCH. MYSQLND_MS_MASTER_SWITCH makes the plugin run a statement on the master, MYSQLND_MS_SLAVE_SWITCH enforces the use of the slave and MYSQLND_MS_MASTER_SWITCH will run a statement on the same server that has been used for running the previous statement.
The plugin scans the beginning of a statement for the existance of a SQL hint. SQL hints are only recognized if they appear at the very beginning of the statement.
例1 Plugin config with one slave and one master
[myapp] master[]=localhost:/tmp/mysql.sock slave[]=192.168.2.27:3306
例2 SQL hints to prevent connection switches
<?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 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 example the session variables issue from the previous page is solved using MYSQLND_MS_LAST_USED_SWITCH to prevent 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 tend to be behind the master but you need current data from the database.
例3 Fighting replication lag
<?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);
}
?>
use case may include the creation of tables on a slave. If no SQL hint is given, 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.
例4 Table creation on a slave
<?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 connection and forces the use of the previously used connection.