I had the pleasure of presenting to the PHP Users Group Philippines a few days ago aboutmysqlnd_ms. The mysqlnd plugin, MySQL Master Slave, is a transparent layer on top of mysqlnd extension. This allows you to do read-write splitting and slave reads load balancing without needing to change anything from your application. But do you know you can also achieve a form of high availability with this plugin? I shared 2 forms on my presentation, using async MySQL replication either in master-slave configuration or master-master configuration, while the second form is having an all primary cluster where you can write to all nodes.
This first part is to demonstrate how you can achieve a simple HA solution using the first form. First, all the sample code here can be found on myGitHub repository. So, to use the mysqlnd_ms plugin, it uses an additional external configuration file in JSON format. This configuration file, will define your master and slave nodes, failover properties and any filters (connection selection method) you want to dictate how the algorithm will provide you the connection.
Let’s start with the mysqlnd_ms configuration I used,mysqlnd_ms_ms.ini :
JavaScript
{"primary": {"master": {"master_1": {"host": "127.0.0.1","port": "33001"}},"slave": {}},"standby": {"master": {"master_1": {"host": "127.0.0.1","port": "33002"}},"slave": {}}}|
{ "primary":{ "master":{ "master_1":{ "host":"127.0.0.1", "port":"33001" } }, "slave":{ } }, "standby":{ "master":{ "master_1":{ "host":"127.0.0.1", "port":"33002" } }, "slave":{ } } } |
Here, I have two applications defined, one called “primary” and another called “standby”, I have not defined any slaves for simplicity. The two MySQL instances running on port 33001 and 33002 are in master-master configuration.
mysqlnd_ms.enable = 1mysqlnd_ms.disable_rw_split = 1mysqlnd_ms.force_config_usage = 1mysqlnd_ms.config_file = /home/revin/git/demo-me/phpugph201407/mysqlnd_ms_ms.ini
|
mysqlnd_ms.enable=1 mysqlnd_ms.disable_rw_split=1 mysqlnd_ms.force_config_usage=1 mysqlnd_ms.config_file=/home/revin/git/demo-me/phpugph201407/mysqlnd_ms_ms.ini |
This is the custom INI file I used for the tests,master-slave.ini . The first line simply enables the plugin for use. The second line, mysqlnd_ms.disable_rw_split instructs the plugin that I should only send all queries to the master because I only have masters for this test.
As for the PHP script, the full copy can be foundhere, as it is a bit lengthy I will just explain the logic on what it does.
Here is an example run, my primary has a server id or 101 while my standby is 102:
[revin@forge phpugph201407]$ php -c master-slave.ini master-slave-ng.phpLast value 0001 from server id 101 thread id 7Last value 0003 from server id 101 thread id 837: [2002] Connection refusedConnection to host 'primary' failed: [0] Connection refused, retrying (1 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (2 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (3 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (4 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (5 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (6 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (7 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (8 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (9 of 10) in 3 secondsConnection to host 'primary' failed: [0] Connection refused, retrying (10 of 10) in 3 secondsThe primary host 'primary' has failed after 30 seconds, failing over to standby!52: [2002] Connection refusedLast value 0004 from server id 102 thread id 635Last value 0006 from server id 102 thread id 636Last value 0008 from server id 102 thread id 637[...]
|
[revin@forgephpugph201407]$php-cmaster-slave.inimaster-slave-ng.php Lastvalue0001fromserverid101threadid7 Lastvalue0003fromserverid101threadid8 37:[2002]Connectionrefused Connectiontohost'primary'failed:[0]Connectionrefused,retrying(1of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(2of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(3of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(4of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(5of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(6of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(7of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(8of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(9of10)in3seconds Connectiontohost'primary'failed:[0]Connectionrefused,retrying(10of10)in3seconds Theprimaryhost'primary'hasfailedafter30seconds,failingovertostandby! 52:[2002]Connectionrefused Lastvalue0004fromserverid102threadid635 Lastvalue0006fromserverid102threadid636 Lastvalue0008fromserverid102threadid637 [...] |
This is not the perfect setup and there are a number of limitations, however it tells us that if you have a simple HA requirement like if you’re not running a very critical application but still do not want to be waken up at night but rather deal with issues in the morning, this might just fit. So here are some more notes:
The same effect can be achieved with a little more coding, but you can already take advantage of the plugin with less.
I’ve also tested the plugin on the second form where you can write to multiple masters using Percona XtraDB Cluster. I’ve found a few interesting issues there so stay tuned.
