What is PHP’s MySQLnd and How It Performs Easy Read/Write Splitting?

MySQL is the most sought-after database server used with PHP. In fact, PHP web applications are connected to MySQL server by default. Although, some people also use other database servers like Oracle, SQL Server etc. for data storage, but for handling the web workload, MySQL is the most commonly used database.

In the past – PHP’s mysql extension, PHP’s mysqli and the PDO MYSQL driver – used MySQL Client Library (also known as libmysqlclient) – for communicating with the MySQL database server. But, libmysqlclient interface wasn’t  optimized for interaction with PHP applications, as the library was primarily built keeping C applications in mind. That’s why, a replacement to the libmysqlclient was developed, called as the MySQL Native Driver (also referred to as mysqlnd); mysqlnd is also a library that provides almost similar functionality as provided by  MySQL Client Library.

Also Read: PHP Frameworks and Libraries That Every Web Developer Must Know About

The MySQL Native Driver was made available in PHP 5.3. And it has been the default library that is used to connect to the MySQL Server since the release of PHP 5.4 (although, you can even compile against libmysqlclient). The MySQL Native Driver offers additional features, improved performance, and better memory usage compared to libmysqlclient.

In this post we’ll talk about how you can perform read/write splitting easily with help of PHP’s MySQLnd. But before that it is important to learn about mysqlnd installation process. Also, we’ll discuss about MySQL Native Driver Plugins that you’ll require in read/write splitting. 

For installing mysqlnd, we’ll have to compile one out of the three MySQL extensions named “ext/pdo_mysql”, “ext/mysqli” and “ext/mysql”. Remember, not to define the path to the MySQL Client Library ( libmysqlclient) in each instance.

Note: Installation of ext/mysql or ext/mysqli, automatically enables the third extension – ext/pdo_mysql. 

Furthermore, you can choose an extension, by selecting one or more configure flags as listed below:

    –with-mysql

    –with-mysqli

    –with-pdo-mysql

Lastly, keep in mind that in case you’re using Debian, or Ubuntu operating system, you can install the php5-mysqlnd package without a fuss using the following line of code:

$ sudo apt-get install php5-mysqlnd

This will help you get rid of the libmysqlclient php5-mysql package, and instead will let you include all three MySQL extensions.

MySQL Native Driver not only provide performance benefit, but the biggest benefit it provide is its plugins. You can access the plugins through PECL, and install them using the following line of code:

$ pecl install mysqlnd_<name>

Let’s discuss about some of the stable plugins:

• mysqlnd_ms: Helps to carry out read or write splitting between the “master and slave” servers effortlessly, with help of simple load balancing.
• mysqlnd_qc: It embeds a simple query cache to PHP
• mysqlnd_uh: It lets you write mysqlnd plugins in PHP

In order to split reads and writes, we’ll be using the mysqlnd_ms plugin. 

After installation of the mysqlnd_ms plugin using PECL, we’ll have to configure php.ini as well as the mysqlnd_ms configuration file.

In php.ini, we’ll add the following lines of code:

extension=mysqlnd_ms.so

mysqlnd_ms.enable=1

mysqlnd_ms.config_file=/path/to/mysqlnd_ms.json

Next, create the mysqlnd_ms.json file, which helps to determine the master and slave servers. In addition, the file also help define the “read or write splitting” and “load balancing strategies”. 

Our configuration file consists of one master and one slave:

{

“appname”: {

“master”: {

“master_0”: {

“host”: “master.mysql.host”,

“port”: “3306”,

“user”: “dbuser”,

“password”: “dbpassword”,

“db”: “dbname”

}

},

“slave”: {

“slave_0”: {

“host”: “slave.mysql.host”,

“port”: “3306”

“user”: “dbuser”,

“password”: “dbpassword”,

“db”: “dbname”

},

}

}

}

You only need to make changes to one setting called as host, and all others are optional. 

The mysqlnd_ms plugin transparently route the queries – by default – that starts with SELECT to the slave servers. Besides this, the plugin route the queries that doesn’t start with SELECT to the master.

This can prove good as well as bad for you. Well, being transparent spares you from making any changes to the code. However, you won’t be able to know whether a query is read-only or not, as the plugin doesn’t analyze the query. 

Apart from not only sending a query that does not start with SELECT to the master, the plugin will send a write query with “SELECT..into the slave” that can prove to be a disaster. Fortunately, the plugin boasts the ability to provide hint regarding sending the query to the right server (i.e. master or slave server). For this, it places one among the below listed three SQL hint constants within the query:

• MYSQLND_MS_MASTER_SWITCH: It helps to run the query statement on the master
• MYSQLND_MS_SLAVE_SWITCH: Allows to run the query statement on the slave
• MYSQLND_MS_LAST_USED_SWITCH: This one enables to run the query statement on the server that was used in the last

In order to use any one of the SQL hints, we’ll need to add a comment prior to the query. One of the easiest way to so requires using sprintf(). Let’s consider an example, where the mysqlnd_ms plugin is sending a SELECT to the master, using the first SQL hint constant:

$sql = sprintf(“/*%s*/ SELECT * FROM table_name;”, MYSQLND_MS_MASTER_SWITCH);

Below mentioned query is used for not sending a SELECT to a slave, using the second SQL hint as discussed above:

$sql = sprintf(“/*%s*/ CREATE TEMPORARY TABLE `temp_table_name` SELECT * FROM table_name;”, MYSQLND_MS_SLAVE_SWITCH);

Now, let’s consider an example where the last hint will help you ensure how the same connection is used just like the one for the query mentioned above. This will ensure that switch from the master to reading has been made, after the data is modified, but still hasn’t replicated. In addition, it also ensure that the switch is made when carrying out certain transactions including both read as well as write statements. 

if ($request->isPost() && $form->isValid()) {

$user>setValues($form->getValues());

$user->save();

}

$sql = sprintf(“/*%s*/ SELECT * FROM user_session WHERE user_id = :user_id”, MYSQLND_LAST_USED_SWITCH);

You can split read or write between servers using the mysqlnd_ms plugin. It’s a useful plugin, especially when you want to move large legacy PHP applications to using distributed read/write. Though the plugin might not appear to be perfect to many users, but it will definitely get you 80-90% of success – and you’ll be able to move most applications – without fiddling with the code.

Author Bio

Maria Mincey is a web developer by profession and a writer by hobby and works for Xicom Technologies, a PHP development company. She loves sharing information regarding PHP development tips & tricks. If you are looking forward to hire PHP developers then just get in touch with her.