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.