Are you interested in a master slave load balancing plugin for mysqlnd? It could have two operational modes. The automatic mode would operate transparent from an applications point of view. The manual mode would give you full control over all decisions. Automatic mode is for install and forget when you start with MySQL replication. Manual mode is for growing as your application evolves.

Start small…

Let’s assume you have to run a forum for a customer. You have picked, more or less randomly, the second-best PHP forum application. The customer’s website becomes popular. The forum is read-mostly and qualifies for scaling through MySQL replication. You add two MySQL slaves to your web farm. Now, how do you get the second-best PHP forum application to direct all writes to the MySQL master? Of course, the second-best application was not designed to support MySQL replication and you do not want to spend a fortune to change the application. The free solution of your problem is five minutes away:

1. Get the mysqlnd master slave load balancing plugin
2. Install the plugin
3. Write a config file describing your replication setup

Precondition: the plugin exists (read on…) and you are using PHP 5.3 compiled with mysqlnd support.

First you get the plugin. Then you edit your php.ini configuration file.

extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.ini_file=mysqlnd_ms_repl_setup.ini

Well done, your PHP mysqlnd library has now built-in replication load balancing support. Now you need to tell the load balancer where to find the master and the slave server and how to distribute read requests over the slaves. You need to create the "mysqlnd_ms_repl_setup.ini" referenced by your php.ini file.

[second_best_forum]
master[] = master.mynetwork
slave[] = slave_1.mynetwork
slave[] = slave_2.mynetwork

Well done, replication load balancing has been configured. Read requests will be distributed over the slaves in a round robin fashion. All of the following connects will give you load balanced connections. Whatever PHP mysql extension/API the forum application is using, it will end up being load balanced.

/* all of those will be load balanced */
$mysqli = new mysqli("second_best_forum", "user", "password");
$mysql = mysql_connect("second_best_forum", "user", "password");
$pdo = new PDO("mysql:host=second_best_forum", "user", "password");

/* goes to the master */
$mysqli->query("INSERT INTO users(name) VALUES ('Andrey')");
printf("insert_id = %d\n", $mysqli->insert_id);

/* goes to the slave in roundrobin fashion */
$res = $mysqli->query("SELECT 'Hello '");
var_dump($res->fetch_assoc());

Didn’t I say it would be transparent? If so, why touch the second-best forum applications source code ?! No need to. The section name "[second_best_forum]" from the configuration file "mysqlnd_ms_repl_setup.ini" could also read " [" host_or_ip_used_so_far]" Let’s assume that your forum had been configured to connect to "192.168.2.12" before you started with replication. Try this config…

[192.168.2.12]
master[] = master.mynetwork
slave[] = slave_1.mynetwork
slave[] = slave_2.mynetwork

… and grow

Your customer loves you. You solved the load problem within minutes using MySQL replication and the magic mysqlnd plugin for PHP 5.3 hiding the little mistake you made when picking the second-best PHP forum application. No application developer was involved. Costs have been saved. Unfortunately, the customers forum becomes more and more popular. You reach a point where you have to introduce a second master. You need to start to plan your data distribution. You need more control over the query distribution.

The plugin can still serve you. Implement a callback to control the server selection, to control which server gets picked.

1. Get the mysqlnd master slave load balancing plugin
2. Install the plugin
3. Write a config file describing your replication setup
4. NEW: write user-defined load balancer algorithm

Start with changing your plugin configuration file. Add to lines to tell the load balancing plugin that you want to use a user-defined callback for server selection. If the callback is not available, the plugin shall fallback to random query distribution over all configures slaves.

[as_we_grow]
master[] = master.mynetwork
slave[] = slave_1.mynetwork
slave[] = slave_2.mynetwork
pick[]=user
pick[]=random

As said, at this point you have to start to change your application. You are doomed to write PHP code. But hey, that’s better than writing Lua code, isn’t it? I mean, if you would be a Lua fan, you would not be reading PlanetPHP :-D.

Add an auto_prepend file to your php.ini configuration file. We will use the auto_prepend to register the server selection callback before the second-best PHP forum starts.

auto_prepend=mysqlnd_ms_callback.php

extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.ini_file=mysqlnd_ms_repl_setup.ini

In your auto_prepend file "mysqlnd_ms_callback.php" implement and register a callback for picking the server based on the query string.

function pick_server($query, $slaves,  $master) {
   /* built-in SQL parser of the plugin - can check if query is SELECT */
   $is_select = mysqlnd_is_select($query);
  /* analyze query for server selection */
   if (stristr($query, "your_criteria_goes_here"))
        return "name_of_host_to_run_query_on"
  else
      /* fallback to automatic server selection */
       return ""
}

Well done, you have implemented a rule to choose the replication server based on the query run by the application. The application change is minimal. You added an auto_prepend file. You have not touched a single line of code of the second-best PHP forum application. Upgrading the second-best PHP forum application to the latest version from its vendor is as easy as it can get for a second-best application. You have not customized it for replication. At any time you have been running an out-of-the box solution. In that sense one may still speak about a transparent master slave load balancing solution…

Truth or fiction?

Truth! I have presented you the basic API of PECL/mysqlnd_ms. PECL/mysqlnd_ms is a prototype, a proof-of-concept. It is pre-alpha. As of today it roughly implements what is needed for basic query load balancing.

Fiction! PECL/mysqlnd_ms (ms = master slave) is not production ready. It is just good enough for a blog posting like this.

You want this?

If you find the idea behing PECL/mysqlnd_ms useful, please leave a comment or send us an email. Have a nice weekend!