注意: Version requirement
A client-side global transaction ID injection has been introduced in mysqlnd_ms version 1.2.0-alpha. The feature is not required for synchronous clusters, such as MySQL Cluster. Use it with asynchronous clusters such as classical MySQL replication.
As of MySQL 5.6.5-m8 release candidate the MySQL server features built-in global transaction identifiers. The MySQL built-in global transaction ID feature is supported by
PECL/mysqlnd_ms
1.3.0-alpha or later. However, the final feature set found in MySQL 5.6 production releases to date is not sufficient to support the ideas discussed below in all cases. Please, see also the concepts section.
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.
例1 Create counter table on master
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=latin1 INSERT 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.
例2 Plugin config: SQL for client-side GTID 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 } } }
例3 Transparent global transaction ID injection
<?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.
注意: SQL for global transaction ID: efficient solution wanted!
The SQL used for the client-side global transaction ID emulation is inefficient. It is optimized for clearity not for performance. Do not use it for production environments. Please, help finding an efficient solution for inclusion in the manual. We appreciate your input.
例4 Plugin config: SQL for fetching 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":{ "on_commit":"UPDATE test.trx SET trx_id = trx_id + 1", "fetch_last_gtid" : "SELECT MAX(trx_id) FROM test.trx", "report_error":true } } }
例5 Obtaining GTID after injection
<?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 7 GTID 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 accidentally 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.
例6 Plugin config: Checking for a certain 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":{ "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 } } }
例7 Session consistency service level and GTID combined
<?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
注意: Insufficient server support in MySQL 5.6
The plugin has been developed against a pre-production version of MySQL 5.6. It turns out that all released production versions of MySQL 5.6 do not provide clients with enough information to enforce session consistency based on GTIDs. Please, read the concepts section for details.
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 in some cases but not all!
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().
例8 Plugin config: using MySQL 5.6.5-m8 built-in GTID feature
{ "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 } } }