PECL/mysqlnd_ms 1.1.0 (download) has been released (documentation)! It is a drop-in solution to add MySQL replication support to any PHP 5.3+ application using any of the PHP MySQL APIs (mysql, mysqli, PDO_MySQL) when compiled to use the mysqlnd library. It extends the mysqlnd library by replication and load balancing funtionality. The mysqlnd library is an optional replacement for the MySQL Client Library (AKA libmysql). The mysqlnd library ships together with PHP as of version 5.3. As of PHP 5.4 the mysqlnd library is a compile time default choice for all three PHP MySQL extensions.

The plugin provides automatic read-write splitting, …

The soon to be announced version 1.1.0-beta of the mysqlnd replication and load balancing plugin (PECL/mysqlnd_ms) for PHP introduces a new concept of filters to the plugin. Filters take a list of servers to pick one or more of it. Filters can be chained, similar command line tools. Imagine a future with a filter chain like: user_multi | roundrobin, table_partitioning | random_once, table_partitioning | adaptive_loadbalancer, … For example, user_multi | roundrobin will first invoke a callback and then apply static round robin load balancing to the servers returned by the callback set with user_multi . Or, table_partitioning | adaptive_loadbalancer would first apply …

The soon to be announced version 1.1.0-beta of the mysqlnd replication and load balancing plugin (PECL/mysqlnd_ms) for PHP introduces a new concept of filters to the plugin. Filters take a list of servers to pick one or more of it. Filters can be chained, similar command line tools. Imagine a future with a filter chain like: user_multi | roundrobin, table_partitioning | random_once, table_partitioning | adaptive_loadbalancer, … For example, user_multi | roundrobin will first invoke a callback and then apply static round robin load balancing to the servers returned by the callback set with user_multi . Or, table_partitioning | adaptive_loadbalancer would first apply …

Recently I was asked if PECL/mysqlnd_ms should be used to add MySQL replication support to a yet to be developed PHP application. The mysqlnd plugin, which supports all PHP MySQL extensions (PDO, mysqli, mysql), stood up against a classical, simple, proven and fast approach: one connection for reads, one connection for writes. Let’s compare. This is a bit of an unfair challenge, because PECL/mysqlnd_ms was designed as a drop-in for existing applications, not optimized for those starting from scratch, *yell*… The plugin stands up quite well, anyway!

The classical pattern

If starting from scratch you can manually direct all reads to the slaves and all writes to the masters. Use a factory/singleton to create database objects. For database read requests, ask the factory for connection object to a slave. …

Recently I was asked if PECL/mysqlnd_ms should be used to add MySQL replication support to a yet to be developed PHP application. The mysqlnd plugin, which supports all PHP MySQL extensions (PDO, mysqli, mysql), stood up against a classical, simple, proven and fast approach: one connection for reads, one connection for writes. Let’s compare. This is a bit of an unfair challenge, because PECL/mysqlnd_ms was designed as a drop-in for existing applications, not optimized for those starting from scratch, *yell*… The plugin stands up quite well, anyway!

The classical pattern

If starting from scratch you can manually direct all reads to the slaves and all writes to the masters. Use a factory/singleton to create database objects. For database read requests, ask the factory for connection object to a slave. …

After a short sprint for PHP 5.4 beta, which is on its way with mysqlnd as a configuration default for all three PHP MySQL extensions, we continued working on the mysqlnd replication plugin (PECL/mysqlnd_ms). Please, find a high level overview presentation further below in this blog post. Because replication support is added at the mysqlnd library level, it is almost transparent from an applications point of view. If you are new to mysqlnd plugins, think of it as a proxy. A proxy that you can drop-in to any existing PHP MySQL application.

Significant progress has been made since the initial alpha release: new powerful configuration syntax, many limitations on lazy connections lifted, countless issues identified and fixed through …

After a short sprint for PHP 5.4 beta, which is on its way with mysqlnd as a configuration default for all three PHP MySQL extensions, we continued working on the mysqlnd replication plugin (PECL/mysqlnd_ms). Please, find a high level overview presentation further below in this blog post. Because replication support is added at the mysqlnd library level, it is almost transparent from an applications point of view. If you are new to mysqlnd plugins, think of it as a proxy. A proxy that you can drop-in to any existing PHP MySQL application.

Significant progress has been made since the initial alpha release: new powerful configuration syntax, many limitations on lazy connections lifted, countless issues identified and fixed through …

The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked mysqlnd to print …

The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked mysqlnd to print …

SQL injection is wonderful! MySQL Proxy can do it, mysqlnd plugins - even written in PHP (not Lua or C) - can do it. Global Transaction IDs are wonderful. A mashup of the PHP replication plugin and global transaction ID injection, makes your replication cluster fail-over much smoother and opens up an opportunity for an API to support consistent reads from slaves "immediately" after a write. Less hassle identifying and promoting a new master for fail-over, even better read load balancing - my last proposal for the future of the PHP …