The free Mysqlnd replication and load balancing plugin now offers load balancing and lazy connections independent of read write splitting. This makes the plugin attractive for MySQL Cluster users. All nodes participating in a MySQL Cluster can serve all requests, they all accept read and write requests. No statement redirection needs to be done. An application using MySQL Cluster has only one task: load balance requests over MySQL frontends (SQL Nodes).

If using the new configuration setting mysqlnd_ms.disable_rw_split=1, the plugin will load balance requests over the list of configured master servers. The term master is borrowed from the primary usage scenario of the plugin, which is MySQL Replication. Don’t get confused by it. Master, slave, MySQL frontend – after all those are just servers participating in a certain kind of database cluster. All three terms refer to nodes in a MySQL database cluster. The closest analogy to a MySQL Cluster SQL node is a MySQL replication master, thus you have to configure a list of "masters", if you want to use load balancing without read write splitting. We didn’t introduce a new name for the server list, that’s all. Remember, that MySQL replication is the primary focus of the plugin.

The example plugin configuration file has two MySQL Cluster SQL nodes configured in the "master" list. The "slave" list is intentionally left empty. The load balancing policy is round robin. The plugin will iterate over the server list whenever a statement is to be executed.

lb_only_for_mysql_cluster.ini

{
 "myapp": {
  "master": {
   "master_0":{"host":"localhost"},
   "master_1":{"host":"192.168.78.136"}
  },
  "slave":{
  },
  "filters":{
   "roundrobin":[]
  }
 }
}

Because the plugins primary usage scenario is MySQL replication, we have to set two PHP configuration settings to make it accept more than one master and to disable read write splitting.

mysqlnd_ms.enable=1
mysqlnd_ms.ini_file=lb_only_for_mysql_cluster.ini
mysqlnd_ms.multi_master=1
mysqlnd_ms.disable_rw_split=1

PECL/mysqlnd_ms can be used together with any PHP MySQL API (extension) compiled to use the mysqlnd library. The test script show the use with the two recommended choices, which are mysqli and PDO_MySQL.

lb.php

printf("\nUsing mysqli\n\n");
$mysqli = new mysqli("myapp", "root", "", "test");
var_dump($mysqli->query("SELECT VERSION()")->fetch_assoc());
var_dump($mysqli->query("SELECT VERSION()")->fetch_assoc());

printf("\nUsing PDO\n\n");
$pdo = new PDO("mysql:host=myapp;dbname=test", "root", "");
var_dump($pdo->query("SELECT VERSION()")->fetchAll());
var_dump($pdo->query("SELECT VERSION()")->fetchAll());

Finally, putting things together to see them in action…

nixnutz@linux-fuxh:~/php/php-src/branches/PHP_5_3> sapi/cli/php -dmysqlnd_ms.multi_master=1 -d mysqlnd_ms.disable_rw_split=1 -dmysqlnd_ms.enable=1 -dmysqlnd_ms.ini_file=lb_only.ini lb.php

Using mysqli

array(1) {
  ["VERSION()"]=>
  string(16) "5.1.45-debug-log"
}
array(1) {
  ["VERSION()"]=>
  string(12) "5.6.2-m5-log"
}

Using PDO

array(1) {
  [0]=>
  array(2) {
    ["VERSION()"]=>
    string(16) "5.1.45-debug-log"
    [0]=>
    string(16) "5.1.45-debug-log"
  }
}
array(1) {
  [0]=>
  array(2) {
    ["VERSION()"]=>
    string(12) "5.6.2-m5-log"
    [0]=>
    string(12) "5.6.2-m5-log"
  }
}

This is the final feature addition before the 1.1.x production ready release, planned still this week. The new feature has been documented but it will take a couple of days until the php.net reference manual shows the latest edits.

Free MySQL web seminar Building High Performance and High Traffic PHP Applications with MySQL – Part 3: Succeed with Plugins on Wednesday, October 26, 2011.