Master/Slave with mysqlnd_ms

👨‍💻 Development Help
1

I needed to run a complicated report wich lead me on the path toward master/slave replication with mysqlnd_ms on our production server.

So I’d like to share some of my advice and how to install mysqnd_ms on centos 7.

To start master/slave replication on MySQL find one of the many good thorough tutorials online on how to accomplish that task. Once you are completed with a live working master/slave combo, then you can start the process of utilizing it with SuiteCRM. The method will work with a working install.

When you setup the slave, ensure it uses the same username and password. To ensure your setup is correct, and you don’t accidentally write to the slave database, change the user permissions to only do SELECT. All other permissions for that user should be removed except for SELECT on the CRM slave database.

first things first

uninstall php-mysql

You can do this in CentOS 7 by typing
WARNING it will uninstall dependencies, like phpMyAdmin,

yum remove php-mysql

if you use phpMyAdmin, then you can remove the package only with this command.

rpm --nodeps -e php-mysql

Now install the much better php-mysqlnd

yum install php-mysqlnd

Next, you need to install the php-devel packages as well as development tools group to compile mysqlnd_ms with pecl.

yum groupinstall "Development tools"
yum install php-devel

Now install the pecl php module.

pecl install mysqlnd_ms

Now that we have all the required packages installed we need to configure php to use mysqnd_ms.

This part was tricky for me as I was placing my configuration in php.ini when it needed to be placed in /etc/php.d/whatever.ini.

Create the mysqlnd_ms configuration file in /etc/mysqlnd_ms/mysqlnd_ms.json

mkdir /etc/mysqlnd_ms
vim  /etc/mysqlnd_ms/mysqlnd_ms.json

Enter the following in mysqlnd_ms.json, where MASTERIPADDRESS is the location of the master database IP and SLAVEIPADDRESS, is the address of the slave db. Either can be localhost or remote IP. So one server might be running the localhost master and another the slave and you will label it accordingly.

The “myapp” is the pseudo host name you are giving the setup. The PHP application will be looking for myapp or whatever name you give it and realize it needs to send all select queries to the slave DB and the rest of the queries to the Master.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "MASTERIPADDRESS",
                "port": "3306"

            }
        },
        "slave": {
            "slave_0": {
                "host": "SLAVEIPADDRESS",
                "port": "3306"

            }
        }
    }
}

Create a configuration .ini file in /etc/php.d/mysqlnd_ms.ini

vim /etc/php.d/mysqlnd_ms.ini

Enter the following in mysqlnd_ms.ini

;########mysqlnd_ms########
extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/etc/mysqlnd_ms/mysqlnd_ms.json

Now restart apache

systemctl restart httpd.service

Verify mysqlnd_ms installation.

php -m

mysqlnd and mysqlnd_ms should both be listed in the output.

Now assuming you already configure the mysql master/slave replication, and have the same username on both servers, and ensured the user for the slave server has only select permissions. Simply go to the root directory of your SuiteCRM install and edit your config.php

Find and edit the location of your DB configuration.

vim /path/to/your/crm/install/httpdocs/config.php

//SECTION OF DB CODE IN CONFIG.PHP
'dbconfig' =>
  array (
    'db_host_name' => 'myapp',
    'db_host_instance' => 'SQLEXPRESS',
    'db_user_name' => 'CRMDUSER',
    'db_password' => 'CRMPASSWORD',
    'db_name' => 'CRMDATABASE',
    'db_type' => 'mysql',
    'db_port' => '3306',
    'db_manager' => 'MysqliManager',
  ),

If you can access the site without any host errors, then you have correctly configure mysqlnd_ms with SuiteCRM. Congrats.

Any tips or input are always appreciated. There may be mistakes as i wrote this tutorial after my troubleshooting.

In yy particular instance, I actualy am using it in a different way.

I made my mysql master/slave setup.
I am running 2 copys of the crm. One is on the main master server, and it reads and write only to the master server.
Then one is a copy of the crm accessible through a different domain and it is running the slave db. The slave db copy of the crm is running mysqlnd_ms. This allows me to do heavy reporting on my data without affecting peformance for the normal users. I also have the mysqlnd_ms setup running incase my boss forgets he is on the reporting server and makes a change to the crm, the changes will go to the master server and the reading will come from the salve. All synced up and beautiful. This was a hurried excersize, hope it helps someone else with the same issues that I had.

1 Like