Skip to content

Rage Against the Shell

Linux tips and other things…

  • Home
  • Contact
  • Privacy Policy

Day: September 18, 2016

Unknown user in statoverride file

Posted on September 18, 2016 - September 18, 2016 by Mr. Reboot

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.

Posted in DistrosLeave a comment

MySQL load balancing with mysqlnd_ms plugin

Posted on September 18, 2016 - October 1, 2016 by Mr. Reboot

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

Posted in MySQLLeave a comment

Search

Calendar

September 2016
M T W T F S S
 1234
567891011
12131415161718
19202122232425
2627282930  
« Jul   Oct »

Categories

  • Apache
  • Cisco
  • Command line
  • Distros
  • Dovecot
  • File systems
  • Gadgets
  • GlusterFS
  • MySQL
  • Nginx
  • NTP
  • Opendkim
  • Pacemaker + Corosync
  • Postfix
  • Raspberrypi
  • SSH
  • SSL
  • Varnish

RSS RSS

  • Using qrencode January 16, 2022
  • Compile varnish module vmod_vsthrottle April 22, 2020
  • SSH vpn with sshuttle April 9, 2020
  • Disable swap in systemd December 16, 2019
  • Getting the parent process pid October 12, 2018
Proudly powered by WordPress | Theme: micro, developed by DevriX.