Supported clusters
Supported clusters
Supported clusters
Any application using any kind of MySQL cluster is
faced with the same tasks:
- Identify nodes capable
of executing a given statement with the required service
level - Load balance requests
within the list of candidates - Automatic fail over
within candidates, if needed
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
focusThe 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.
- Configure one master and
one or more slaves. Server configuration details are given in the
setup section. - Use random load
balancing policy together with the sticky flag. - If you do not plan to
use the service level API calls, add the master on write flag. - Please, make yourself
aware of the properties of automatic failover before adding a
failover directive. - Consider the use of
trx_stickiness to execute transactions on the
primary only. Please, read carefully how it works before you rely
on it.
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.
- Disable the plugins
built-in read-write
splitting. - Configure masters
only. - Consider random once
load balancing strategy, which is the plugins default. If random
once is used, only masters are configured and no SQL hints are used
to force using a certain node, no connection switches will happen
for the duration of a web request. Thus, no special handling is
required for transactions. The plugin will pick one master at the
beginning of the PHP script and use it until the script
terminates. - Do not set the quality
of service. All nodes have all the data. This automatically gives
you the highest possible service quality (strong
consistency). - Do not enable
client-side global transaction injection. It is neither required to
help with server-side fail over nor to assist the quality of
service filter choosing an appropriate node.
Disabling built-in read-write splitting.
- Set mysqlnd_ms.disable_rw_split=1
- Do not use SQL hints to enforce the
use of slaves
Configure masters only.
- Set mysqlnd_ms.multi_master=1.
- Do not configure any
slaves. -
Set failover=loop_before_master in the plugins
configuration file to avoid warnings about the empty slave list and
to make the failover logic loop over all configured masters before
emitting an error. Please, note the
warnings about automatic failover given in the previous
sections.
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.