Linux tips and other things…
If you get this error in Debian/Ubuntu on apt-get install/upgrade etc:
dpkg: unrecoverable fatal error, aborting: syntax error: unknown user 'user' in statoverride file
you can solve deleting ‘user’ in the statoverride file:
~ $ sed -i '/user/d' /var/lib/dpkg/statoverride
Now apt-get command should work fine.
Tested on Debian 7 / PHP 5.4
In a mysql replication scenario you can balance sql queries sending write queries to the master server and read-only queries to the slaves using mysqlnd_ms plugin with mysqlnd php extension (mysql native driver). Also is possible in a master-master replication balance write queries between two or more masters.
First install php5-mysqlnd extension if is not installed yet, and other necessary packages.
~ $ apt-get install php5-mysqlnd php5-dev
Now you have to compile mysqlnd-ms plugin for mysqlnd, so download pecl extension from php.net:
~ $ wget http://pecl.php.net/get/mysqlnd_ms-1.5.2.tgz ~ $ tar xvzf mysqlnd_ms-1.5.2.tgz ~ $ cd mysqlnd_ms-1.5.2
Done this, configure:
~ $ phpize ~ $ ./configure --with-php-config=/usr/bin/php-config --enable-mysqlnd-ms
Compile:
~ $ make
And install:
~ $ make install
At last add extension mysqlnd_ms.so at the end of specific ini file, usually /etc/php5/mods-available/mysqlnd.ini:
extension=mysqlnd_ms.so
And enable it:
mysqlnd_ms.enable="on"
Now is time to configure the balancing type, two possible scenarios would be these:
One master, two slaves
Create /opt/php/mysqlnd_ms.json and add this:
{
"mysqlbalancer": {
"master": {
"master1": {
"host": "MASTER-IP",
"port": "3306"
}
},
"slave": {
"slave1": {
"host": "SLAVE1-IP",
"port": "3306"
},
"slave2": {
"host": "SLAVE2-IP",
"port": "3306"
}
},
"filters": {
"random": {
"weights": {
"slave1":50,
"slave2":50
}
}
},
"master_on_write": 1,
"failover": { "strategy": "loop_before_master", "remember_failed": true }
}
}
Add this to the file /etc/php5/mods-available/mysqlnd.ini:
mysqlnd_ms.config_file="/opt/php/mysqlnd_ms.json"
Here, write queries go to the master server and read queries go to the slaves randomly fifty-fifty (the same weight for both).
Parameter ‘master_on_write’ means that after writing into the master the next read query go to the master too, is more reliable because asynchronous mysql replication has delay.
With the option ‘loop_before_master’, if one slave fails, try another slave before making query into the master, and ‘remember_failed’ remembers if a node has been marked as failed discarding it.
Two or more masters
Create /opt/php/mysqlnd_ms.json and add this:
{
"mysqlbalancer": {
"master": {
"master1": {
"host": "MASTER1-IP",
"port": "3306"
},
"master2": {
"host": "MASTER2-IP",
"port": "3306"
}
},
"slave": { },
"filters": { "roundrobin": [ ] },
"failover": { "strategy": "loop_before_master", "remember_failed": true }
}
}
Add this to the file /etc/php5/mods-available/mysqlnd.ini:
mysqlnd_ms.config_file="/opt/php/mysqlnd_ms.json" mysqlnd_ms.multi_master=1 mysqlnd_ms.disable_rw_split=1
Here, write and read queries go to both masters using roundrobin iterating into the master server list.
Option mysqlnd_ms.multi_master=1 enables the plugin to using several masters and mysqlnd_ms.disable_rw_split=1 disables separated read-write sentences because now masters work like a slaves too.
Once done configuration you have to restart services, if you use php-fpm do this:
~ $ /etc/init.d/php-fpm restart
if you use php module for apache then restart apache:
~ $ /etc/init.d/apache2 restart
Now you have to tell the web application that use this configuration, at the mysql connection string indicate the connector name defined above in the json file as HOST, example with pdo:
$conn = new PDO("mysql:host=mysqlbalancer;dbname=dbname", "username", "password");
If you use wordpress you have to modify wp-config.php changing DB_HOST var:
/** MySQL hostname */
define('DB_HOST', 'mysqlbalancer');
Finally, for security reasons you can force all mysql connections to use mysqlnd_ms plugin adding this option to the /opt/php/mysqlnd_ms.json file:
mysqlnd_ms.force_config_usage=1
Now if you try connecting to mysql using as HOST another name or IP address you will get an error like this:
PHP Warning: PDO::__construct(): (mysqlnd_ms) Exclusive usage of configuration enforced but did not find the correct INI file section (localhost) Connection failed: SQLSTATE[HY000] [2000] (mysqlnd_ms) Exclusive usage of configuration enforced but did not find the correct INI file section
+ Info: http://php.net/manual/en/mysqlnd-ms.configuration.php