mysqlnd-ms-php-supportedclusters-4

  • Concepts
  • Supported clusters

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

    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.

    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.