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 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.

• 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.

mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini

{
  "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.

mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/mysqlnd_ms_plugin.ini
mysqlnd_ms.multi_master=1

{
  "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.

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

"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.