mysqlnd-ms-php-plugin-ini-json-6

  • Installing/Configuring
  • Plugin configuration file (>=1.1.x)

  • Plugin configuration file (>=1.1.x)
  • Plugin configuration file (>=1.1.x)

    Plugin configuration file (>=1.1.x)

    The following documentation applies to
    PECL/mysqlnd_ms >= 1.1.0-beta. It is not valid for prior
    versions. For documentation covering earlier versions, see the
    configuration documentation for mysqlnd_ms 1.0.x and below.

    Introduction

    Note: Changelog: Feature
    was added in PECL/mysqlnd_ms 1.1.0-beta

    The below description applies to PECL/mysqlnd_ms
    >= 1.1.0-beta. It is not valid for prior versions.

    The plugin uses its own configuration file. The
    configuration file holds information about the MySQL replication
    master server, the MySQL replication slave servers, the server pick
    (load balancing) policy, the failover strategy, and the use of lazy
    connections.

    The plugin loads its configuration file at the
    beginning of a web request. It is then cached in memory and used
    for the duration of the web request. This way, there is no need to
    restart PHP after deploying the configuration file. Configuration
    file changes will become active almost instantly.

    The PHP configuration directive mysqlnd_ms.config_file is used to set the
    plugins configuration file. Please note, that the PHP configuration
    directive may not be evaluated for every web request. Therefore,
    changing the plugins configuration file name or location may
    require a PHP restart. However, no restart is required to read
    changes if an already existing plugin configuration file is
    updated.

    Using and parsing JSON is efficient, and using
    JSON makes it
    easier to express hierarchical data structures than the standard
    php.ini format.

    Example #1 Converting a PHP array (hash) into JSON
    format

    Or alternatively, a developer may be more familiar with the PHP
    array syntax, and prefer it. This example
    demonstrates how a developer might convert a PHP array to
    JSON.

    <?php
    $config 
    = array(
      
    "myapp" => array(
        
    "master" => array(
          
    "master_0" => array(
            
    "host"   => "localhost",
            
    "socket" => "/tmp/mysql.sock",
          ),
        ),
        
    "slave" => array(),
      ),
    );

    file_put_contents("mysqlnd_ms.ini"json_encode($configJSON_PRETTY_PRINT));
    printf("mysqlnd_ms.ini file created...\n");
    printf("Dumping file contents...\n");
    printf("%s\n"str_repeat("-"80));
    echo 
    file_get_contents("mysqlnd_ms.ini");
    printf("\n%s\n"str_repeat("-"80));
    ?>

    The above example will output:

    mysqlnd_ms.ini file created...
    Dumping file contents...
    --------------------------------------------------------------------------------
    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost",
                    "socket": "\/tmp\/mysql.sock"
                }
            },
            "slave": [
    
            ]
        }
    }
    --------------------------------------------------------------------------------
    

    A plugin configuration file consists of one or more
    sections. Sections are represented by the top-level object
    properties of the object encoded in the JSON file. Sections could
    also be called configuration names.

    Applications reference sections by their name.
    Applications use section names as the host (server) parameter to
    the various connect methods of the mysqli, mysql
    and PDO_MYSQL
    extensions. Upon connect, the mysqlnd plugin compares the hostname with all of the
    section names from the plugin configuration file. If the hostname
    and section name match, then the plugin will load the settings for
    that section.

    Example #2 Using section names example

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "192.168.2.27"
                },
                "slave_1": {
                    "host": "192.168.2.27",
                    "port": 3306
                }
            }
        },
        "localhost": {
            "master": [
                {
                    "host": "localhost",
                    "socket": "\/path\/to\/mysql.sock"
                }
            ],
            "slave": [
                {
                    "host": "192.168.3.24",
                    "port": "3305"
                },
                {
                    "host": "192.168.3.65",
                    "port": "3309"
                }
            ]
        }
    }
    
    <?php
    /* All of the following connections will be load balanced */
    $mysqli = new mysqli("myapp""username""password""database");
    $pdo = new PDO('mysql:host=myapp;dbname=database''username''password');
    $mysql mysql_connect("myapp""username""password");

    $mysqli = new mysqli("localhost""username""password""database");
    ?>

    Section names are strings. It is valid to use a
    section name such as 192.168.2.1, 127.0.0.1 or
    localhost. If, for example, an application connects to
    localhost and a plugin configuration section
    localhost exists, the semantics of the connect operation
    are changed. The application will no longer only use the MySQL
    server running on the host localhost, but the plugin will
    start to load balance MySQL queries following the rules from the
    localhost configuration section. This way you can load
    balance queries from an application without changing the
    applications source code. Please keep in mind, that such a
    configuration may not contribute to overall readability of your
    applications source code. Using section names that can be mixed up
    with host names should be seen as a last resort.

    Each configuration section contains, at a minimum, a list of master
    servers and a list of slave servers. The master list is configured
    with the keyword master, while the slave list is
    configured with the slave keyword. Failing to provide a
    slave list will result in a fatal
    E_ERROR level error, although a slave
    list may be empty. It is possible to allow no slaves. However, this
    is only recommended with synchronous clusters, please see also
    supported
    clusters
    . The main part of the documentation focusses on the
    use of asynchronous MySQL replication clusters.

    The master and slave server lists can be optionally
    indexed by symbolic names for the servers they describe.
    Alternatively, an array of descriptions for slave and master
    servers may be used.

    Example #3 List of anonymous slaves

    "slave": [
        {
            "host": "192.168.3.24",
            "port": "3305"
        },
        {
            "host": "192.168.3.65",
            "port": "3309"
        }
    ]
    

    An anonymous server list is encoded by the JSON
    array
    type. Optionally, symbolic names may be used for
    indexing the slave or master servers of a server list, and done so
    using the JSON object type.

    Example #4 Master list using symbolic names

    "master": {
        "master_0": {
            "host": "localhost"
        }
    }
    

    It is recommended to index the server lists with
    symbolic server names. The alias names will be shown in error
    messages.

    The order of servers is preserved and taken into
    account by mysqlnd_ms. If, for example, you configure round robin
    load balancing strategy, the first SELECT statement will
    be executed on the slave that appears first in the slave server
    list.

    A configured server can be described with the
    host, port, socket, db,
    user, password and connect_flags. It is
    mandatory to set the database server host using the host
    keyword. All other settings are optional.

    Example #5 Keywords to configure a server

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "db_server_host",
                    "port": "db_server_port",
                    "socket": "db_server_socket",
                    "db": "database_resp_schema",
                    "user": "user",
                    "password": "password",
                    "connect_flags": 0
                }
            },
            "slave": {
                "slave_0": {
                    "host": "db_server_host",
                    "port": "db_server_port",
                    "socket": "db_server_socket"
                }
            }
        }
    }
    

    If a setting is omitted, the plugin will use the
    value provided by the user API call used to open a connection.
    Please, see the using section names example above.

    The configuration file format has been changed in
    version 1.1.0-beta to allow for chained filters. Filters are
    responsible for filtering the configured list of servers to
    identify a server for execution of a given statement. Filters are
    configured with the filter keyword. Filters are executed
    by mysqlnd_ms in the order of their appearance. Defining filters is
    optional. A configuration section in the plugins configuration file
    does not need to have a filters entry.

    Filters replace the pick[] setting from prior versions. The
    new random and roundrobin provide the same
    functionality.

    Example #6 New roundrobin filter, old
    functionality

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "192.168.78.136",
                    "port": "3306"
                },
                "slave_1": {
                    "host": "192.168.78.137",
                    "port": "3306"
                }
            },
            "filters": {
                "roundrobin": [
    
                ]
            }
        }
    }
    

    The function mysqlnd_ms_set_user_pick_server() has been
    removed. Setting a callback is now done with the user
    filter. Some filters accept parameters. The user filter
    requires and accepts a mandatory callback parameter to set
    the callback previously set through the function mysqlnd_ms_set_user_pick_server().

    Example #7 The user filter replaces
    mysqlnd_ms_set_user_pick_server()

    "filters": {
        "user": {
            "callback": "pick_server"
        }
    }
    

    The
    validity of the configuration file is checked both when reading the
    configuration file and later when establishing a connection. The
    configuration file is read during PHP request startup. At this
    early stage a PHP extension may not display error messages
    properly. In the worst case, no error is shown and a connection
    attempt fails without an adequate error message. This problem has
    been cured in version 1.5.0.

    Example #8 Common error message in case of configuration
    file issues (upto version 1.5.0)

    <?php
    $mysqli 
    = new mysqli("myapp""username""password""database");
    ?>

    The above example will output:

    Warning: mysqli::mysqli(): (mysqlnd_ms) (mysqlnd_ms) Failed to parse config file [s1.json]. Please, verify the JSON in Command line code
    
    Warning: mysqli::mysqli(): (HY000/2002): php_network_getaddresses: getaddrinfo failed: Name or service not known in Command line code on line 1
    
    Warning: mysqli::query(): Couldn't fetch mysqli in Command line code on line 1
    
    Fatal error: Call to a member function fetch_assoc() on a non-object in Command line code on line 1
    

    Since version 1.5.0 startup errors are additionally
    buffered and emitted when a connection attempt is made. Use the
    configuration directive mysqlnd_ms.force_config_usage to set the
    error type used to display buffered errors. By default an error of
    type E_WARNING will be emitted.

    Example #9 Improved configuration file validation since
    1.5.0

    <?php
    $mysqli 
    = new mysqli("myapp""username""password""database");
    ?>

    The above example will output:

    Warning: mysqli::mysqli(): (mysqlnd_ms) (mysqlnd_ms) Failed to parse config file [s1.json]. Please, verify the JSON in Command line code on line 1
    

    It can be useful to set mysqlnd_ms.force_config_usage = 1 when
    debugging potential configuration file errors. This will not only
    turn the type of buffered startup errors into
    E_RECOVERABLE_ERROR but also help detecting misspelled
    section names.

    Example #10 Possibly more precise error due to
    mysqlnd_ms.force_config_usage=1

    mysqlnd_ms.force_config_usage=1
    
    <?php
    $mysqli 
    = new mysqli("invalid_section""username""password""database");
    ?>

    The above example will output:

    Warning: mysqli::mysqli(): (mysqlnd_ms) Exclusive usage of configuration enforced but did not find the correct INI file section (invalid_section) in Command line code on line 1 line 1
    

    Configuration Directives

    Here is a short explanation of the configuration
    directives that can be used.

    master array or object

    List of MySQL replication master servers. The list
    of either of the JSON type array to declare an anonymous
    list of servers or of the JSON type object. Please, see
    above for examples.

    Setting at least one master server is mandatory.
    The plugin will issue an error of type E_ERROR if the user
    has failed to provide a master server list for a configuration
    section. The fatal error may read (mysqlnd_ms) Section [master]
    doesn’t exist for host [name_of_a_config_section] in %s on line
    %d
    .

    A server is described with the host,
    port, socket, db, user,
    password and connect_flags. It is mandatory to
    provide at a value for host. If any of the other values is
    not given, it will be taken from the user API connect call, please,
    see also: using section names example.

    Table of server
    configuration keywords.

    Keyword Description Version
    host

    Database server host. This is a mandatory setting.
    Failing to provide, will cause an error of type
    E_RECOVERABLE_ERROR when the plugin tries to connect to
    the server. The error message may read (mysqlnd_ms) Cannot find
    [host] in [%s] section in config in %s on line %d
    .

    Since 1.1.0.
    port

    Database server TCP/IP port.

    Since 1.1.0.
    socket

    Database server Unix domain socket.

    Since 1.1.0.
    db

    Database (schemata).

    Since 1.1.0.
    user

    MySQL database user.

    Since 1.1.0.
    password

    MySQL database user password.

    Since 1.1.0.
    connect_flags

    Connection flags.

    Since 1.1.0.

    The plugin supports using only one master server.
    An experimental setting exists to enable multi-master support. The
    details are not documented. The setting is meant for development
    only.

    slave array or object

    List of one or more MySQL replication slave
    servers. The syntax is identical to setting master servers, please,
    see master above for details.

    The plugin supports using one or more slave
    servers.

    Setting a list of slave servers is mandatory. The
    plugin will report an error of the type E_ERROR if
    slave is not given for a configuration section. The fatal
    error message may read (mysqlnd_ms) Section [slave] doesn’t
    exist for host [%s] in %s on line %d
    . Note, that it is valid
    to use an empty slave server list. The error has been introduced to
    prevent accidentally setting no slaves by forgetting about the
    slave setting. A master-only setup is still possible using
    an empty slave server list.

    If an empty slave list is configured and an attempt
    is made to execute a statement on a slave the plugin may emit a
    warning like mysqlnd_ms) Couldn’t find the appropriate slave
    connection. 0 slaves to choose from.
    upon statement execution.
    It is possible that another warning follows such as
    (mysqlnd_ms) No connection selected by the last
    filter
    .

    global_transaction_id_injection array or
    object

    Global transaction identifier configuration related
    to both the use of the server built-in global transaction ID
    feature and the client-side emulation.

    Keyword Description Version
    fetch_last_gtid

    SQL statement for accessing the latest global
    transaction identifier. The SQL statement is run if the plugin
    needs to know the most recent global transaction identifier. This
    can be the case, for example, when checking MySQL Replication slave
    status. Also used with mysqlnd_ms_get_last_gtid().

    Since 1.2.0.
    check_for_gtid

    SQL statement for checking if a replica has
    replicated all transactions up to and including ones searched for.
    The SQL statement is run when searching for replicas which can
    offer a higher level of consistency than eventual consistency. The
    statement must contain a placeholder #GTID which is to be
    replaced with the global transaction identifier searched for by the
    plugin. Please, check the quickstart for examples.

    Since 1.2.0.
    report_errors

    Whether to emit an error of type warning if an
    issue occurs while executing any of the configured SQL
    statements.

    Since 1.2.0.
    on_commit

    Client-side global transaction ID emulation only.
    SQL statement to run when a transaction finished to update the
    global transaction identifier sequence number on the master.
    Please, see the quickstart for examples.

    Since 1.2.0.
    wait_for_gtid_timeout

    Instructs the plugin to wait up to
    wait_for_gtid_timeout seconds for a slave to catch up when
    searching for slaves that can deliver session consistency. The
    setting limits the time spend for polling the slave status. If
    polling the status takes very long, the total clock time spend
    waiting may exceed wait_for_gtid_timeout. The plugin calls
    sleep(1) to sleep one second between each two polls.

    The setting can be used both with the plugins
    client-side emulation and the server-side global transaction
    identifier feature of MySQL 5.6.

    Waiting for a slave to replicate a certain GTID
    needed for session consistency also means throttling the client. By
    throttling the client the write load on the master is reduced
    indirectly. A primary copy based replication system, such as MySQL
    Replication, is given more time to reach a consistent state. This
    can be desired, for example, to increase the number of data copies
    for high availability considerations or to prevent the master from
    being overloaded.

    Since 1.4.0.
    fabric object

    MySQL Fabric related settings. If the plugin is
    used together with MySQL Fabric, then the plugins configuration
    file no longer contains lists of MySQL servers. Instead, the plugin
    will ask MySQL Fabric which list of servers to use to perform a
    certain task.

    A minimum plugin configuration for use with MySQL
    Fabric contains a list of one or more MySQL Fabric hosts that the
    plugin can query. If more than one MySQL Fabric host is configured,
    the plugin will use a roundrobin strategy to choose among them.
    Other strategies are currently not available.

    Example #11 Minimum pluging configuration for use with
    MySQL Fabric

    {
        "myapp": {
            "fabric": {
                "hosts": [
                    {
                        "host" : "127.0.0.1",
                        "port" : 8080
                    }
                ]
            }
        }
    }
    

    Each MySQL Fabric host is described using a JSON
    object with the following members.

    Keyword Description Version
    host

    Host name of the MySQL Fabric host.

    Since 1.6.0.
    port

    The TCP/IP port on which the MySQL Fabric host
    listens for remote procedure calls sent by clients such as the
    plugin.

    Since 1.6.0.

    The plugin is using PHP streams to communicate with
    MySQL Fabric through XML RPC over HTTP. By default no timeouts are
    set for the network communication. Thus, the plugin defaults to PHP
    stream default timeouts. Those defaults are out of control of the
    plugin itself.

    An optional timeout value can be set to overrule
    the PHP streams default timeout setting. Setting the timeout in the
    plugins configuration file has the same effect as setting a timeout
    for a PHP user space HTTP connection established through PHP
    streams.

    The plugins Fabric timeout value unit is seconds.
    The allowed value range is from 0 to 65535. The setting exists
    since version 1.6.

    Example #12 Optional timeout for communication with
    Fabric

    {
        "myapp": {
            "fabric": {
                "hosts": [
                    {
                        "host" : "127.0.0.1",
                        "port" : 8080
                    }
                ],
                "timeout": 2
            }
        }
    }
    

    Transaction stickiness and MySQL Fabric logic can
    collide. The stickiness option disables switching between servers
    for the duration of a transaction. When using Fabric and sharding
    the user may (erroneously) start a local transaction on one share
    and then attempt to switch to a different shard using either
    mysqlnd_ms_fabric_select_shard() or
    mysqlnd_ms_fabric_select_global(). In this
    case, the plugin will not reject the request to switch servers in
    the middle of a transaction but allow the user to switch to another
    server regardless of the transaction stickiness setting used. It is
    clearly a user error to write such code.

    If transaction stickiness is enabled and you would
    like to get an error of type warning when calling mysqlnd_ms_fabric_select_shard() or
    mysqlnd_ms_fabric_select_global(), set the
    boolean flag trx_warn_server_list_changes.

    Example #13 Warnings about the violation of transaction
    boundaries

    {
        "myapp": {
            "fabric": {
                "hosts": [
                    {
                        "host" : "127.0.0.1",
                        "port" : 8080
                    }
                ],
                "trx_warn_serverlist_changes": 1
            },
            "trx_stickiness": "on"
        }
    }
    
    <?php
    $link 
    = new mysqli("myapp""root""""test");
    /*
      For the demo the call may fail.
      Failed or not we get into the state
      needed for the example.
    */
    @mysqlnd_ms_fabric_select_global($link1);
    $link->begin_transaction();
    @
    $link->query("DROP TABLE IF EXISTS test");
    /*
      Switching servers/shards is a mistake due to open
      local transaction!
    */
    mysqlnd_ms_select_global($link1);
    ?>

    The above example will output:

    PHP Warning: mysqlnd_ms_fabric_select_global(): (mysqlnd_ms) Fabric server exchange in the middle of a transaction in %s on line %d
    

    Please, consider the feature experimental. Changes
    to syntax and semantics may happen.

    filters object

    List of filters. A filter is responsible to filter
    the list of available servers for executing a given statement.
    Filters can be chained. The random and roundrobin
    filter replace the pick[] directive used in prior version to
    select a load balancing policy. The user filter replaces
    the mysqlnd_ms_set_user_pick_server()
    function.

    Filters may accept parameters to refine their
    actions.

    If no load balancing policy is set, the plugin will
    default to random_once. The random_once policy
    picks a random slave server when running the first read-only
    statement. The slave server will be used for all read-only
    statements until the PHP script execution ends. No load balancing
    policy is set and thus, defaulting takes place, if neither the
    random nor the roundrobin are part of a
    configuration section.

    If a filter chain is configured so that a filter
    which output no more than once server is used as input for a filter
    which should be given more than one server as input, the plugin may
    emit a warning upon opening a connection. The warning may read:
    (mysqlnd_ms) Error while creating filter ‘%s’ . Non-multi
    filter ‘%s’ already created. Stopping in %s on line %d
    .
    Furthermore, an error of the error code 2000, the sql
    state HY000 and an error message similar to the warning
    may be set on the connection handle.

    Example #14 Invalid filter sequence

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "192.168.78.136",
                    "port": "3306"
                }
            },
            "filters": [
                "roundrobin",
                "random"
            ]
        }
    }
    
    <?php
    $link 
    = new mysqli("myapp""root""""test");
    printf("[%d] %s\n"mysqli_connect_errno(), mysqli_connect_error());
    $link->query("SELECT 1 FROM DUAL");
    ?>

    The above example will output:

    PHP Warning:  mysqli::mysqli(): (HY000/2000): (mysqlnd_ms) Error while creating filter 'random' . Non-multi filter 'roundrobin' already created. Stopping in filter_warning.php on line 1
    [2000] (mysqlnd_ms) Error while creating filter 'random' . Non-multi filter 'roundrobin' already created. Stopping
    PHP Warning:  mysqli::query(): Couldn't fetch mysqli in filter_warning.php on line 3
    
    Filter:
    random object

    The random filter features the random and
    random once load balancing policies, set through the pick[] directive in older versions.

    The random policy will pick a random server
    whenever a read-only statement is to be executed. The random once
    strategy picks a random slave server once and continues using the
    slave for the rest of the PHP web request. Random once is a
    default, if load balancing is not configured through a filter.

    If the random filter is not given any
    arguments, it stands for random load balancing policy.

    Example #15 Random load balancing with random
    filter

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "192.168.78.136",
                    "port": "3306"
                },
                "slave_1": {
                    "host": "192.168.78.137",
                    "port": "3306"
                }
            },
            "filters": [
                "random"
            ]
        }
    }
    

    Optionally, the sticky argument can be
    passed to the filter. If the parameter sticky is set to
    the string 1, the filter follows the random once load
    balancing strategy.

    Example #16 Random once load balancing with
    random filter

    {
        "filters": {
            "random": {
                "sticky": "1"
            }
        }
    }
    

    Both the random and roundrobin
    filters support setting a priority, a weight for a server, since
    PECL/mysqlnd_ms 1.4.0. If the weight argument is passed to
    the filter, it must assign a weight for all servers. Servers must
    be given an alias name in the slave respectively
    master server lists. The alias must be used to reference
    servers for assigning a priority with weight.

    Example #17 Referencing error

    [E_RECOVERABLE_ERROR] mysqli_real_connect(): (mysqlnd_ms) Unknown server 'slave3' in 'random' filter configuration. Stopping in %s on line %d
    

    Using a wrong alias name with weight may
    result in an error similar to the shown above.

    If weight is omitted, the default weight
    of all servers is one.

    Example #18 Assigning a weight for load
    balancing

    {
       "myapp": {
           "master": {
               "master1":{
                   "host":"localhost",
                   "socket":"\/var\/run\/mysql\/mysql.sock"
               }
           },
           "slave": {
               "slave1": {
                   "host":"192.168.2.28",
                   "port":3306
               },
               "slave2": {
                   "host":"192.168.2.29",
                   "port":3306
               },
               "slave3": {
                   "host":"192.0.43.10",
                   "port":3306
               },
           },
           "filters": {
               "random": {
                   "weights": {
                       "slave1":8,
                       "slave2":4,
                       "slave3":1,
                       "master1":1
                   }
               }
           }
       }
    }
    

    At the average a server assigned a weight of two
    will be selected twice as often as a server assigned a weight of
    one. Different weights can be assigned to reflect differently sized
    machines, to prefer co-located slaves which have a low network
    latency or, to configure a standby failover server. In the latter
    case, you may want to assign the standby server a very low weight
    in relation to the other servers. For example, given the
    configuration above slave3 will get only some eight
    percent of the requests in the average. As long as slave1
    and slave2 are running, it will be used sparsely, similar
    to a standby failover server. Upon failure of slave1 and
    slave2, the usage of slave3 increases. Please,
    check the notes on failover before using weight this
    way.

    Valid weight values range from 1 to 65535.

    Unknown arguments are ignored by the filter. No
    warning or error is given.

    The filter expects one or more servers as input.
    Outputs one server. A filter sequence such as random,
    roundrobin may cause a warning and an error message to be
    set on the connection handle when executing a statement.

    List of filter arguments.

    Keyword Description Version
    sticky

    Enables or disabled random once load balancing
    policy. See above.

    Since 1.2.0.
    weight

    Assigns a load balancing weight/priority to a
    server. Please, see above for a description.

    Since 1.4.0.
    Filter:
    roundrobin object

    If using the roundrobin filter, the plugin
    iterates over the list of configured slave servers to pick a server
    for statement execution. If the plugin reaches the end of the list,
    it wraps around to the beginning of the list and picks the first
    configured slave server.

    Example #19 roundrobin filter

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "192.168.78.136",
                    "port": "3306"
                }
            },
            "filters": [
                "roundrobin"
            ]
        }
    }
    

    Expects one or more servers as input. Outputs one
    server. A filter sequence such as roundrobin,
    random may cause a warning and an error message to be set
    on the connection handle when executing a statement.

    List of filter arguments.

    Keyword Description Version
    weight

    Assigns a load balancing weight/priority to a
    server. Please, find a description above.

    Since 1.4.0.
    Filter:
    user object

    The user replaces mysqlnd_ms_set_user_pick_server()
    function, which was removed in 1.1.0-beta. The filter sets a
    callback for user-defined read/write splitting and server
    selection.

    The plugins built-in read/write query split
    mechanism decisions can be overwritten in two ways. The easiest way
    is to prepend a query string with the SQL hints
    MYSQLND_MS_MASTER_SWITCH,
    MYSQLND_MS_SLAVE_SWITCH or
    MYSQLND_MS_LAST_USED_SWITCH. Using
    SQL hints one can control, for example, whether a query shall be
    send to the MySQL replication master server or one of the slave
    servers. By help of SQL hints it is not possible to pick a certain
    slave server for query execution.

    Full control on server selection can be gained
    using a callback function. Use of a callback is recommended to
    expert users only because the callback has to cover all cases
    otherwise handled by the plugin.

    The plugin will invoke the callback function for
    selecting a server from the lists of configured master and slave
    servers. The callback function inspects the query to run and picks
    a server for query execution by returning the hosts URI, as found
    in the master and slave list.

    If the lazy connections are enabled and the
    callback chooses a slave server for which no connection has been
    established so far and establishing the connection to the slave
    fails, the plugin will return an error upon the next action on the
    failed connection, for example, when running a query. It is the
    responsibility of the application developer to handle the error.
    For example, the application can re-run the query to trigger a new
    server selection and callback invocation. If so, the callback must
    make sure to select a different slave, or check slave availability,
    before returning to the plugin to prevent an endless loop.

    Example #20 Setting a callback

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "192.168.78.136",
                    "port": "3306"
                }
            },
            "filters": {
                "user": {
                    "callback": "pick_server"
                }
            }
        }
    }
    

    The callback is supposed to return a host to run
    the query on. The host URI is to be taken from the master and slave
    connection lists passed to the callback function. If callback
    returns a value neither found in the master nor in the slave
    connection lists the plugin will emit an error of the type
    E_RECOVERABLE_ERROR The error may read like
    (mysqlnd_ms) User filter callback has returned an unknown
    server. The server ‘server that is not in master or slave list’ can
    neither be found in the master list nor in the slave list
    . If
    the application catches the error to ignore it, follow up errors
    may be set on the connection handle, for example, (mysqlnd_ms)
    No connection selected by the last filter
    with the error code
    2000 and the sqlstate HY000. Furthermore a
    warning may be emitted.

    Referencing a non-existing function as a callback
    will result in any error of the type E_RECOVERABLE_ERROR
    whenever the plugin tries to callback function. The error message
    may reads like: (mysqlnd_ms) Specified callback (pick_server)
    is not a valid callback
    . If the application catches the error
    to ignore it, follow up errors may be set on the connection handle,
    for example, (mysqlnd_ms) Specified callback (pick_server) is
    not a valid callback
    with the error code 2000 and the
    sqlstate HY000. Furthermore a warning may be emitted.

    The following parameters are passed from the plugin
    to the callback.

    Parameter Description Version
    connected_host

    URI of the currently connected database server.

    Since 1.1.0.
    query

    Query string of the statement for which a server
    needs to be picked.

    Since 1.1.0.
    masters

    List of master servers to choose from. Note, that
    the list of master servers may not be identical to the list of
    configured master servers if the filter is not the first in the
    filter chain. Previously run filters may have reduced the master
    list already.

    Since 1.1.0.
    slaves

    List of slave servers to choose from. Note, that
    the list of slave servers may not be identical to the list of
    configured slave servers if the filter is not the first in the
    filter chain. Previously run filters may have reduced the slave
    list already.

    Since 1.1.0.
    last_used_connection

    URI of the server of the connection used to execute
    the previous statement on.

    Since 1.1.0.
    in_transaction

    Boolean flag indicating whether the statement is
    part of an open transaction. If autocommit mode is turned off, this
    will be set to TRUE. Otherwise it is
    set to FALSE.

    Transaction detection is based on monitoring the
    mysqlnd library call set_autocommit. Monitoring is not
    possible before PHP 5.4.0. Please, see connection pooling and
    switching
    concepts discussion for further details.

    Since 1.1.0.

    Example #21 Using a callback

    {
        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost"
                }
            },
            "slave": {
                "slave_0": {
                    "host": "192.168.2.27",
                    "port": "3306"
                },
                "slave_1": {
                    "host": "192.168.78.136",
                    "port": "3306"
                }
            },
            "filters": {
                "user": {
                    "callback": "pick_server"
                }
            }
        }
    }
    
    <?php
    function pick_server($connected$query$masters$slaves$last_used_connection$in_transaction)
    {
     static 
    $slave_idx 0;
     static 
    $num_slaves NULL;
     if (
    is_null($num_slaves))
      
    $num_slaves count($slaves);

     /* default: fallback to the plugins build-in logic */
     
    $ret NULL;

     printf("User has connected to '%s'...\n"$connected);
     
    printf("... deciding where to run '%s'\n"$query);

     $where mysqlnd_ms_query_is_select($query);
     switch (
    $where)
     {
      case 
    MYSQLND_MS_QUERY_USE_MASTER:
       
    printf("... using master\n");
       
    $ret $masters[0];
       break;
      case 
    MYSQLND_MS_QUERY_USE_SLAVE:
       
    /* SELECT or SQL hint for using slave */
       
    if (stristr($query"FROM table_on_slave_a_only"))
       {
        
    /* a table which is only on the first configured slave  */
        
    printf("... access to table available only on slave A detected\n");
        
    $ret $slaves[0];
       }
       else
       {
        
    /* round robin */
        
    printf("... some read-only query for a slave\n");
        
    $ret $slaves[$slave_idx++ % $num_slaves];
       }
       break;
      case 
    MYSQLND_MS_QUERY_LAST_USED:
       
    printf("... using last used server\n");
       
    $ret $last_used_connection;
       break;
     }

     printf("... ret = '%s'\n"$ret);
     return 
    $ret;
    }

    $mysqli = new mysqli("myapp""root""""test");

    if (!($res