• Quickstart and
  • Partitioning and Sharding

  • Partitioning and Sharding
  • Partitioning and Sharding

    Partitioning and Sharding

    Database clustering is done for various reasons.
    Clusters can improve availability, fault tolerance, and increase
    performance by applying a divide and conquer approach as work is
    distributed over many machines. Clustering is sometimes combined
    with partitioning and sharding to further break up a large complex
    task into smaller, more manageable units.

    The mysqlnd_ms plugin aims to support a wide
    variety of MySQL database clusters. Some flavors of MySQL database
    clusters have built-in methods for partitioning and sharding, which
    could be transparent to use. The plugin supports the two most
    common approaches: MySQL Replication table filtering, and Sharding
    (application based partitioning).

    MySQL Replication supports partitioning as filters
    that allow you to create slaves that replicate all or specific
    databases of the master, or tables. It is then in the
    responsibility of the application to choose a slave according to
    the filter rules. You can either use the mysqlnd_ms node_groups filter to manually support this,
    or use the experimental table filter.

    Manual partitioning or sharding is supported
    through the node grouping filter, and SQL hints as of 1.5.0. The
    node_groups filter lets you assign a symbolic name to a group of
    master and slave servers. In the example, the master
    master_0 and slave_0 form a group with the name
    Partition_A. It is entirely up to you to decide what makes
    up a group. For example, you may use node groups for sharding, and
    use the group names to address shards like

    Example #1 Cluster node groups

      "myapp": {
           "master": {
                "master_0": {
                    "host": "localhost",
                    "socket": "\/tmp\/mysql.sock"
            "slave": {
                "slave_0": {
                    "host": "simulate_slave_failure",
                    "port": "0"
                "slave_1": {
                    "host": "",
                    "port": 3311
            "filters": {
                "node_groups": {
                    "Partition_A" : {
                        "master": ["master_0"],
                        "slave": ["slave_0"]
               "roundrobin": []

    Example #2 Manual partitioning using SQL

    function select($mysqli$msg$hint '')
    /* Note: weak test, two connections to two servers may have the same thread id */
    $sql sprintf("SELECT CONNECTION_ID() AS _thread, '%s' AS _hint FROM DUAL"$msg);
        if (
    $hint) {
    $sql $hint $sql;
        if (!(
    $res $mysqli->query($sql))) {
    printf("[%d] %s"$mysqli->errno$mysqli->error);
    $row =  $res->fetch_assoc();
    printf("%d - %s - %s\n"$row['_thread'], $row['_hint'], $sql);

    $mysqli = new mysqli("myapp""user""password""database");
    if (!
    $mysqli) {
    /* Of course, your error handling is nicer... */
    die(sprintf("[%d] %s\n"mysqli_connect_errno(), mysqli_connect_error()));

    /* All slaves allowed */

    /* only servers of node group "Partition_A" allowed */

    6804 - slave_0 - SELECT CONNECTION_ID() AS _thread, 'slave1' AS _hint FROM DUAL
    2442 - slave_1 - SELECT CONNECTION_ID() AS _thread, 'slave2' AS _hint FROM DUAL
    6804 - slave_0 - /*Partition_A*/SELECT CONNECTION_ID() AS _thread, 'slave1' AS _hint FROM DUAL
    6804 - slave_0 - /*Partition_A*/SELECT CONNECTION_ID() AS _thread, 'slave1' AS _hint FROM DUAL

    By default, the plugin will use all configured
    master and slave servers for query execution. But if a query begins
    with a SQL hint like /*node_group*/, the plugin will only
    consider the servers listed in the node_group for query
    execution. Thus, SELECT queries prefixed with
    /*Partition_A*/ will only be executed on