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 …

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 …

While Andrey is busy implementing partitioned replication infrastructure code for the PHP replication and load balancing plugin (PECL/mysqlnd_ms), I continued my search for ideas to steal. Mr. Robert Hodges, I’ve robbed the idea of a service level and caching.. If an application is able to function with stale data read from a MySQL replication slave, it can also deal with stale data from a local cache. The replication plugin (PECL/mysqlnd_ms) could, in certain cases, populate the query cache plugin (PECL/mysqlnd_qc) for you and read replies from it.

In the blog posting " …

While Andrey is busy implementing partitioned replication infrastructure code for the PHP replication and load balancing plugin (PECL/mysqlnd_ms), I continued my search for ideas to steal. Mr. Robert Hodges, I’ve robbed the idea of a service level and caching.. If an application is able to function with stale data read from a MySQL replication slave, it can also deal with stale data from a local cache. The replication plugin (PECL/mysqlnd_ms) could, in certain cases, populate the query cache plugin (PECL/mysqlnd_qc) for you and read replies from it.

In the blog posting " …

The Mysqlnd replication and load balancing plugin alpha release has focused on laying foundations for read-write splitting and load balancing. Thus, we can now look into more juicy topics such as support of schemata based partitioning to increase the granularity of load balancing. Not every slave server needs to replicate all schemata (databases) and tables of the master. The plugin load balancer shall be aware of it and transparently pick the server which has the entities required to execute a statement. Some brainstorming…

The Mysqlnd replication and load balancing plugin alpha release has focused on laying foundations for read-write splitting and load balancing. Thus, we can now look into more juicy topics such as support of schemata based partitioning to increase the granularity of load balancing. Not every slave server needs to replicate all schemata (databases) and tables of the master. The plugin load balancer shall be aware of it and transparently pick the server which has the entities required to execute a statement. Some brainstorming…

The PHP replication and load balancing mysqlnd plugin as been released as an alpha through PECL. Alpha is for those who want to try out the 1.0 feature set as early as possible. Alpha is for those who do not fear debugging, if need be. Notes on troubleshooting.

Testing load balancing and read/write split

There are three ways to verify that load balancing and read/write split works as it should:

• within PHP script: checking thread/connection ids
• within PHP script: monitoring statistics
• externally: mysqlnd debug log file

Thread/connection ids to distinguish connections

PECL/mysqlnd_ms changes the semantics of a PHP MySQL connection handle. If using the plugin, a connection handle returned by any of the three PHP MySQL extensions ( …