Any application using any kind of MySQL cluster is faced with the same tasks:
The plugin is optimized for fulfilling these tasks in the context of a classical asynchronous MySQL replication cluster consisting of a single master and many slaves (primary copy). When using classical, asynchronous MySQL replication all of the above listed tasks need to be mastered at the client side.
Other types of MySQL cluster may have lower requirements on the application side. For example, if all nodes in the cluster can answer read and write requests, no read-write splitting needs to be done (multi-master, update-all). If all nodes in the cluster are synchronous, they automatically provide the highest possible quality of service which makes choosing a node easier. In this case, the plugin may serve the application after some reconfiguration to disable certain features, such as built-in read-write splitting.
Note: Documentation focus
The documentation focusses describing the use of the plugin with classical asynchronous MySQL replication clusters (primary copy). Support for this kind of cluster has been the original development goal. Use of other clusters is briefly described below. Please note, that this is still work in progress.
Primary copy (MySQL Replication)
This is the primary use case of the plugin. Follow the hints given in the descriptions of each feature.
Example #1 Enabling the plugin (php.ini)
mysqlnd_ms.enable=1 mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini
Example #2 Basic plugin configuration (mysqlnd_ms_plugin.ini) for MySQL Replication
{
"myapp": {
"master": {
"master_1": {
"host": "localhost",
"socket": "\/tmp\/mysql57.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": 3308
},
"slave_1": {
"host": "192.168.2.28",
"port": 3306
}
},
"filters": {
"random": {
"sticky": "1"
}
}
}
}
Primary copy with multi primaries (MMM - MySQL Multi Master)
MySQL Replication allows you to create cluster topologies with multiple masters (primaries). Write-write conflicts are not handled by the replication system. This is no update anywhere setup. Thus, data must be partitioned manually and clients must redirected in accordance to the partitioning rules. The recommended setup is equal to the sharding setup below.
Manual sharding, possibly combined with primary copy and multiple primaries
Use SQL hints and the node group filter for clusters that use data partitioning but leave query redirection to the client. The example configuration shows a multi master setup with two shards.
Example #3 Multiple primaries - multi master (php.ini)
mysqlnd_ms.enable=1 mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini mysqlnd_ms.multi_master=1
Example #4 Primary copy with multiple primaries and paritioning
{
"myapp": {
"master": {
"master_1": {
"host": "localhost",
"socket": "\/tmp\/mysql57.sock"
}
"master_2": {
"host": "192.168.2.27",
"socket": "3306"
}
},
"slave": {
"slave_1": {
"host": "127.0.0.1",
"port": 3308
},
"slave_2": {
"host": "192.168.2.28",
"port": 3306
}
},
"filters": {
"node_groups": {
"Partition_A" : {
"master": ["master_1"],
"slave": ["slave_1"]
},
"Partition_B" : {
"master": ["master_2"],
"slave": ["slave_2"]
}
},
"roundrobin": []
}
}
}
The plugin can also be used with a loose collection of unrelated shards. For such a cluster, configure masters only and disable read write splitting. The nodes of such a cluster are called masters in the plugin configuration as they accept both reads and writes for their partition.
Using synchronous update everywhere clusters such as MySQL Cluster
MySQL Cluster is a synchronous cluster solution. All cluster nodes accept read and write requests. In the context of the plugin, all nodes shall be considered as masters.
Use the load balancing and fail over features only.
Disabling built-in read-write splitting.
Configure masters only.
Example #5 Multiple primaries - multi master (php.ini)
mysqlnd_ms.enable=1 mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini mysqlnd_ms.multi_master=1 mysqlnd_ms.disable_rw_split=1
Example #6 Synchronous update anywhere cluster
"myapp": {
"master": {
"master_1": {
"host": "localhost",
"socket": "\/tmp\/mysql57.sock"
},
"master_2": {
"host": "192.168.2.28",
"port": 3306
}
},
"slave": {
},
"filters": {
"roundrobin": {
}
},
"failover": {
"strategy": "loop_before_master",
"remember_failed": true
}
}
}
If running an update everywhere cluster that has no built-in partitioning to avoid hot spots and high collision rates, consider using the node groups filter to keep updates on a frequently accessed table on one of the nodes. This may help to reduce collision rates and thus improve performance.