MySQL Native Driver for PHP, mysqlnd
By Giri Mandalika on Jan 17, 2009
In order to communicate with the MySQL database server from a PHP application, ext/mysql, ext/mysqli and the PDO MYSQL driver rely on the MySQL client library, libmysql - that has the required implementation for the client-server protocol. The MySQL native driver for PHP (will simply be referred as mysqlnd from this point), is an additional, alternative way to connect from PHP 5 and PHP 6 to the MySQL Server 4.1 or later versions. mysqlnd is a replacement for the MySQL client library, libmysql; and it is tightly integrated into PHP starting with the release of PHP 5.3. That is, from PHP 5.3 onwards the developers can choose between libmysql and mysqlnd when using mysql, mysqli or PDO_MySQL extensions to connect to the MySQL server 4.1 or newer. Due to the tight integration into PHP 5.3 (and later), mysqlnd eliminates the dependency on the MySQL client programming support when the database extension(s) and the database driver are built with the support for mysqlnd.
mysqlnd is not another PHP extension like mysqli nor it has an exposed API to the userland. It is a library that provides almost similar functionality as that of the MySQL client library, libmysql. mysqlnd and libmysql libraries implement the MySQL communication protocol - hence both of those libraries can be used to connect to the MySQL Server.
Since mysqlnd is neither a new extension nor a programming API, but just an alternative to libmysql to connect from PHP to the MySQL Server, there is no need to make changes to the existing PHP scripts. The existing scripts which were running properly with the mysql, mysqli and PDO_MySQL extensions built with libmysql support, continue to run with the exact same behavior even when the mysql, mysqli and PDO_MySQL extensions are built with the mysqlnd support.
From the performance perspective, mysqlnd might be as fast as libmysql; and may even outperform libmysql in some cases. The generic recommendation is to try mysqlnd with your PHP application and to decide based on the performance results.
Some of the advantages of using mysqlnd are listed below:
- Easy to compile: no linking against libmysql, and no dependency on the MySQL client programming support.
- may outperform libmysql in certain cases
- persistent connections for ext/mysqli
- uses PHP memory management, supports PHP memory limit
- reduced memory footprint -- keeps every row only once in memory, where as with libmysql you have it twice in memory
- keeps a long list of performance related statistics for bottle-neck analysis
client-side result set cache(still in experimental stage)
Installing PHP with the MySQL native driver, mysqlnd
As most of the pre-packaged PHP binary installations may not have the support for mysqlnd enabled by default, it is recommended to build PHP and the required database extensions with mysqlnd from the source code. The rest of this section focuses on the installation of PHP 5.3 from the source code. Check the php-mysqlnd web page at dev.mysql.com for the installation instructions for the prior versions of PHP with mysqlnd support.
Make sure that Autoconf 2.13 or later and GNU M4 1.4.7 or later are installed on the machine. Adjust theeg.,
PATHenvironment variable to include the path to the
bash# ls /usr/local/bin/autoconf ; ls /usr/local/bin/m4 /usr/local/bin/autoconf /usr/local/bin/m4 bash# export PATH=.:/usr/local/bin:$PATH
On Sun Solaris, create a soft link to
gmakein the source directory.
bash# ln -s /usr/bin/gmake make
Navigate to the source directory and runeg.,
bash# cd php5.3-200811132130 bash# ./buildconf --force
For building PHP 5.3 or later with mysqlnd support on UNIX/Linux systems, you can decide for all three MySQL extensions (ext/mysql, ext/mysqli, PDO_mysql) whether they should be built using mysqlnd or libmysql. When choosing mysqlnd, use "mysqlnd" as path to the mysql client library. If you don't specify "mysqlnd" as library location, by default, PHP tries to use libmysql. It is possible to build one extension with one library, and another extension with another library. For example, you can build mysqli extension with mysqlnd support, and PDO_MYSQL with libmysql.
The configure option shown below builds all the three extensions with mysqlnd support.eg.,
bash# ./configure --prefix=/export/home/php53 --enable-mysqlnd \\ --with-mysqli=shared,mysqlnd --with-mysql=shared,mysqlnd \\ --with-pdo-mysql=shared,mysqlnd --with-zlib=shared [other options]
configure script in PHP 6.0 and some builds of PHP 5.3 may not recognize the
--enable-mysqlndoption, so check the configure options by running
./configure --helpbefore specifying
--enable-mysqlndin the list of configure options when building PHP.
On Windows platform, mysqli extension uses the MySQL Native Driver by default in PHP versions 5.3 and newer. Hence you don't need to configure libmysql.dll
On Sun Solaris, pass the
-z muldefsoption to the linker to pro-actively defend against linker errors like
ld: fatal: symbol `<symbol>' is multiply-defined.
bash# export LDFLAGS="-z muldefs"
Install PHP in the destination location.
bash# make install
Enable the required database extensions in php.inieg.,
bash# grep extension php.ini | grep -v \\; extension_dir=/export/home/php53/lib/php/extensions/no-debug-non-zts-20071006 extension="mysql.so" extension="mysqli.so" extension="pdo_mysql.so"
Finally verify the new PHP installation by checking the list of PHP modules.eg.,
bash# cd /export/home/php53/bin bash# ./php -m | grep mysql mysql mysqli mysqlnd pdo_mysql bash# ./php -i | grep -i mysql .. mysql MySQL Support => enabled Client API version => mysqlnd 5.0.5-dev - 081106 - $Revision: 18.104.22.168 $ .. mysqli MysqlI Support => enabled Client API library version => mysqlnd 5.0.1-beta - 070402 - $Revision: 321 $ .. mysqlnd mysqlnd => enabled Version => mysqlnd 5.0.5-dev - 081106 - $Revision: 22.214.171.124 $ .. pdo_mysql PDO Driver for MySQL => enabled Client API version => mysqlnd 5.0.5-dev - 081106 - $Revision: 126.96.36.199 $ ..
Andrey Hristov & Ulf Wendel, Sun-MySQL