The mysqlnd plugin API is a well hidden gem of mysqlnd. Mysqlnd plugins operate on a layer between PHP applications and the MySQL server. This is comparable to MySQL Proxy. MySQL Proxy operates on a layer between any MySQL client application, for example, a PHP application and, the MySQL server. Plugins can take over classical MySQL Proxy tasks such as Load Balancing, Monitoring and Performance optimizations. But due to the different architecture and location mysqlnd plugins do not share some common MySQL Proxy annoyances: no single point of failure, no dedicated proxy server to deploy, no new programming language to learn (Lua).

Slides from the IPC Spring conference

This blog posting and today’s presentation at the IPC Spring 2010, a PHP conference in Berlin, are the first public
information on the new toy. Enjoy the slides or continue reading the blog posting.

• The mysqlnd plugin talk (PDF)
• The mysqlnd plugin talk (OpenOffice.org presentation – *.odp)

What mysqlnd plugins can do!

A mysqlnd plugin is kind of an extension to mysqlnd. Plugins can hook virtually all mysqlnd functions. The mysqlnd functions are called by the PHP MySQL extensions (ext/mysql, ext/mysqli, PDO_MYSQL). Consequently it can be said that a mysqlnd plugin can hook all PHP MySQL userspace functions.

Internal mysqlnd function calls cannot only be hooked but also be replaced. There are no limits for manipulating mysqlnd internal function tables: maximum freedom!

Drupal, phpMyFAQ, phpMyAdmin, Oxid, …
|
ext/mysql, ext/mysqli, ext/PDO_MYSQL
Mysqlnd
Mysqlnd plugin
Load Balancing	Monitoring	Performance
|
MySQL Server

Plugins operate on the C level inside PHP and mysqlnd. They can be made 100% transparent to PHP applications. No application changes are needed because plugins operate on a different layer. A mysqlnd plugin adds a new layer to your setup. However, the new layer is part of a software you already deploy: PHP! A mysqlnd plugin is just another PHP extension in your existing PHP infrastructure.

• Load Balancing
  • Read/Write Splitting
  • Failover
  • Round-Robin, least loaded
• Monitoring
  • Query Logging
  • Query Analysis
  • Query Auditing
• Performance
  • Caching
  • Throttling
  • Sharding

Mysqlnd plugins are a different technology than MySQL Proxy. Both are valid tools for solving a variety of common tasks ranging from Load Balancing over Monitoring to Performance. Both have their room, their disadvantages and advantages. An obvious difference: MySQL Proxy works with all MySQL Clients whereas mysqlnd plugins are specific and limited to PHP.

mysqlnd plugin vs. MySQL Proxy: architecture

A mysqlnd plugin gets installed on the PHP application server. MySQL Proxy can either be run on the PHP applications server or be installed on a dedicated machine to handle multple PHP application servers.

Deploying a proxy layer on the application machines has two advantages:

• no single point of failure
• easy to scale out (horizontal scale out, scale by client)

MySQL Proxy is a wonderful and unique piece of software. MySQL Proxy (and mysqlnd plugins) can solve problems easily which otherwise would have required massive changes to existing applications. But MySQL Proxy comes at a price:

• MySQL Proxy is a new component and technology to master and deploy
• MySQL Proxy Lua scripts are not PHP: Lua is a new additional language

MySQL Proxy can be customized with C and Lua programming. Lua is the preferred scripting language of MySQL Proxy. For most PHP experts Lua is a new language to learn. A mysqlnd plugin can be written in C (or PHP – as will be shown in future blog posts). C and PHP should be a natural fit for your existing man power and teams.

Hardware	Software
Application server	PHP application	C/Java/PHP/… application
	mysqlnd plugin	MySQL Proxy
Dedicated machine			MySQL Proxy
Database server	MySQL

Lifecycle: deamon vs. PHP lifecycle. MySQL Proxy is a deamon. It runs forever. MySQL Proxy can recall earlier decisions. A mysqlnd plugin is bound to the PHP lifecycle of one or multiple web requests. MySQL Proxy can share once computed results among multiple application server. To do the same a mysqlnd plugin needs to store its knowledge in a persistent medium, for example, using another daemon such as Memcache. MySQL Proxy has the edge.

mysqlnd plugin: choose C API or wire protocol

MySQL Proxy works on top of the wire protocol. With MySQL Proxy you have to parse and reverse engineer the MySQL Client Server Protocol. Actions are limited to what can be done by manipulating the communication protocol. If the wire protocol changes, which happens very rarely, MySQL Proxy scripts need to be changed as well.

Layer	Software
	PHP application	C/Java/PHP/… application
C API	mysqlnd plugin
Wire protocol	mysqlnd plugin	MySQL Proxy

Mysqlnd plugins work on top of the C API (and thus also on top of the wire protocol). You can hook all C API calls. PHP makes use of the C API. Therefore you can hook all PHP calls. There is no need to go down to the level of the wire protocol.

Mysqlnd implements the wire protocol. Plugins can parse, reverse engineer,manipulate and even replace the communication protocol. However, there are few use cases which require you to work on this low level.

b>Mysqlnd plugins usually operate the C API layer but they can, if need be, operate on the wire protocol as well..

Plugins let you do your manipulations on two layers: C API and wire protocol. This is more than what MySQL Proxy has to offer. Wire protocol changes do not require plugin changes.

Get the mysqlnd plugin API today!

If you have recently downloaded a PHP 5.3 development code snapshot you already downloaded the "mysqlnd plugin API". No, there is no downloadable mysqlnd plugin which gives you all the fabled features sketched above. The mysqlnd plugin API is under development since at least christmas 2009. It is developed in the PHP source repository and available to public.

The "mysqlnd plugin API" has its roots in the ancient history of mysqlnd. It is a bit of a side effect of Andrey’s attempt to modularize the more than 15k loc of mysqlnd. It took us some time to realize the potential of Andrey’s changes. But this is another story, this is for another blog posting.

Like it? More blogging to come

Several companies have expressed severe interest in the mysqlnd plugin idea after todays presentation at the IPC Spring. If you see some potential in the new toy, please drop us a note. You can reach Andrey, me and Johannes through the usual channels.

More blog postings will follow describing more and more aspects of the new toy. Ideally we manage to teach you how to hack your own mysqlnd plugins – start your GCC’s boys!