MySQL and PHP
Copyright 1997-2021 the PHP Documentation Group.
Mysqlnd
plugins and MySQL Proxy are different
technologies using different approaches. Both are valid tools
for solving a variety of common tasks such as load balancing,
monitoring, and performance enhancements. An important
difference is that MySQL Proxy works with all MySQL clients,
whereas mysqlnd
plugins are specific to PHP
applications.
As a PHP Extension, a mysqlnd
plugin gets
installed on the PHP application server, along with the rest of
PHP. MySQL Proxy can either be run on the PHP application server
or can be installed on a dedicated machine to handle multiple
PHP application servers.
Deploying MySQL Proxy on the application server has two advantages:
No single point of failure
Easy to scale out (horizontal scale out, scale by client)
MySQL Proxy (and mysqlnd
plugins) can solve
problems easily which otherwise would have required changes to
existing applications.
However, MySQL Proxy does have some disadvantages:
MySQL Proxy is a new component and technology to master and deploy.
MySQL Proxy requires knowledge of the Lua scripting 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. It is
also possible to write plugins in PHP using
PECL/mysqlnd_uh.
MySQL Proxy runs as a daemon - a background process. MySQL Proxy
can recall earlier decisions, as all state can be retained.
However, a mysqlnd
plugin is bound to the
request-based lifecycle of PHP. MySQL Proxy can also share
one-time computed results among multiple application servers. A
mysqlnd
plugin would need to store data in a
persistent medium to be able to do this. Another daemon would
need to be used for this purpose, such as Memcache. This gives
MySQL Proxy an advantage in this case.
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 those that can be achieved by manipulating the communication protocol. If the wire protocol changes (which happens very rarely) MySQL Proxy scripts would need to be changed as well.
Mysqlnd
plugins work on top of the C API,
which mirrors the libmysqlclient
client. This
C API is basically a wrapper around the MySQL Client Server
protocol, or wire protocol, as it is sometimes called. You can
intercept all C API calls. PHP makes use of the C API, therefore
you can hook all PHP calls, without the need to program at the
level of the wire protocol.
Mysqlnd
implements the wire protocol. Plugins
can therefore parse, reverse engineer, manipulate and even
replace the communication protocol. However, this is usually not
required.
As plugins allow you to create implementations that use two
levels (C API and wire protocol), they have greater flexibility
than MySQL Proxy. If a mysqlnd
plugin is
implemented using the C API, any subsequent changes to the wire
protocol do not require changes to the plugin itself.