Replication is as old as life on earth. No life without replication. MySQL replication is as old as MySQL, almost. No MySQL without replication. The PECL/mysqlnd_ms 1.0.0 alpha release is brand new. My dream, no PHP without the mysqlnd library replication and load balancing plugin, which works with all the PHP MySQL extensions (mysql, mysqli, PDO_MySQL)!

PECL/mysqlnd_ms (download alpha release) is a transparent plugin for the mysqlnd library. It inspects statements executed by any of the three PHP MySQL extensions, if they are compiled to use mysqlnd. Read-only statements are load balanced and send to one of the configured MySQL slave servers. All other statements are executed on the configured MySQL master server. Connection pooling and connection switches are done automatically. The plugin does not change the user API. Depending on the usage scenario very little, if any, application changes may be needed.

All automatic decisions of the plugin can be monitored and overruled, if need be.

Usage example

Basic usage of the plugin is straight-forward. The plugin deploys its own configuration file. Among others, the configuration file instructs the plugin which MySQL master server and MySQL slave servers to use. The configuration file is divided into sections. Each section has a name. For creating a load balanced connection open a MySQL handle and use the section name as a host name.

[myapp]
master[]=localhost:/tmp/mysql.sock
slave[]=192.168.2.27:3306

/* Load balanced following "myapp" section rules from the plugins config file */
$mysqli = new mysqli("myapp", "username", "password", "database");
$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');
$mysql = mysql_connect("myapp", "username", "password");

Any non-prepared statement run on any of the three MySQL handles opened in the previous example will be load balanced. Every statement which begins with SELECT is considered read-only and sent to a slave. All other statements are run on the master server. SQL hints can be used to force using a slave, the master or the last used server. A user defined callback can be installed to replace the built-in read write split mechanism.

/* Statements will be run on the master */
if (!$mysqli->query("DROP TABLE IF EXISTS test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
if (!$mysqli->query("CREATE TABLE test(id INT)")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
if (!$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}

/* read-only: statement will be run on a slave */
if (!($res = $mysqli->query("SELECT id FROM test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
 $row = $res->fetch_assoc(); 
 $res->close();
 printf("Slave returns id = '%s'\n", $row['id'];
}
$mysqli->close();

Three load balancing policies are available: random, random once (sticky), round robin. Additionally a user defined callback can be installed to pick the server for running a statement.

Key Features at a glance

• Transparent and therefore easy to use
  • supports all PHP MySQL extensions
  • no API changes
  • very little, if any, application changes required, dependent on the usage scenario required
• Featured read-write split strategies
  • automatic detection of SELECT, supports SQL hints to overrule automatism
  • user-defined
• Featured load balancing strategies
  • round robin: choose different slave in round robin fashion for every slave request
  • random: choose random slave for every slave request
  • random once (sticky): choose random slave once to run all slave requests for the duration of a web request
  • user-defined. The application can register callbacks with mysqlnd_ms

Current status and Limitations

The plugin is released as alpha. The release contains all features planned for 1.0. It shows the final API. The release does pass all tests. There are no known bugs. However, as an alpha release it cannot be recommended for mission critical use.

The 1.0 alpha does not support multi-statements. Also, it does not support native prepared statements. Please, do not confuse the PDO prepared statement emulation used by PDO_MySQL by default with native prepared statements. By default, PDO_MySQL works with the plugin. Further limitations are documented in the manual.

Happy Easter, happy hacking!

Feedback and comments are welcome! Happy Easter, happy hacking. Enjoy replication.

Download, Documentation, Blog postings

• PECL mysqlnd_ms package and download
• Documentation (soon to appear on the mirrors)
• Related blog postings (sorted by date, latest first)
  • PECL/mysqlnd_ms: transaction aware load balancing, sort of
  • PHP replication and load balancing plugin: master_on_write, documentation
  • PECL/mysqlnd_ms: let web requests be your M/S load balancer
  • A missed load balancing opportunity?
  • What if PECL/mysqlnd_ms does not implement failover?
  • PHP: Application calling plugin – on communication
  • PHP: Master slave load balancing in mysqlnd – how transparent?
  • PHP: … want transparent master slave load balancing in mysqlnd?