How to setup mysqln_ms extension for php 7.x

Posted on December 9, 2019

Mysqlnd_ms is a handy php extension to automaticaly send the read queries to slave mysql servers and writes to the master.

Download repo from here

https://github.com/sergiotabanelli/mysqlnd_ms

In order to compile it you must download some packages

sudo apt-get install php-dev php-json libxml2-dev libmemcached-dev
cd Documents/mysqlnd_ms-master
phpize
./configure
make
sudo make install

Add these lines in php.ini

sudo gedit /etc/php/7.0/php.ini
[mysqlnd_ms]
mysqlnd_ms.enable = On
mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini

And add the module

sudo nano /etc/php/7.0/mods-available/mysqlnd_ms.ini
extension = mysqlnd_ms.so

And a link inside /etc/php/7.0/apache2/conf.d/ with name 20-mysqlnd_ms.ini (Lines extension = mysql.so and extension = mysqlnd.so must run before mysqlnd).

For the case of one master and one slave read we create this file on a path we chose:

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": [
            "slave_0": {
                "host": "127.0.0.1",
                "port":"3307"
            }
        ]
    }
}

After restartung the server your queries will run either on master or slaves. By default the SELECT queries run on slave and everything else on the master.

You connect with mysqli like that:

$mysqli = new mysqli("myapp", "username", "password", "database");