MySQL Router 2.0  /  ...  /  Connection Routing (Standalone)

3.3.2 Connection Routing (Standalone)

The following options are available to routing strategy sections identified by [routing:<section_key>].

Bind Address

Information related to the optional bind_address option:

  • Routing entries can bind to a network interface (NIC). The default bind_address is 127.0.0.1. If a port is not defined here, then setting bind_port is required.

  • Binding to a specific IPv4 or IPv6 address allows and ensures that MySQL Router is not starting and routing the service on a NIC on which nothing is allowed to execute.

  • It is not possible to specify more than one binding address, per routing configuration group. However, using 0.0.0.0:$port (where you define $port) will bind to all network interfaces (IPs) on the host. You can also use IPv6 addresses.

bind_address = 127.0.0.1:7001
Note

The bind_address cannot be listed in the destinations list.

Bind Port

Optionally, you can define a default port using bind_port. If a port is not configured in bind_address, then bind_port is required and used.

The four examples below all result in bind_address = 127.0.0.1:7001

[routing:example_1]
bind_port = 7001
[routing:example_2]
bind_port = 7001
[routing:example_3]
bind_port = 8001
[routing:example_4]
bind_address = 127.0.0.1:7001

Connect Timeout

Information related to the connect_timeout option. This is used by the routing plugin when connecting to the destination MySQL server. The default value is 1 second. The value cannot be unlimited, and an invalid value results in a configuration error. The valid range is between 1 and 65536. You should keep this value low.

For example, when using read-write mode, the value can be a bit higher if you want to wait for the server (Master) to become available. When using read-only mode, connecting to a slave it is good to use a lower value since the Router will select a new server during connection routing.

connect_timeout = 1

Destinations

Information related to the destinations option:

  • Provides a comma-separated list of destination addresses that should be used when establishing connections. The default port is 3306.

    destinations = a.example.com,b.example.com,c.example.com
  • Behavior depends on the mode option.

Modes

Information related to the mode option. Setting this parameter is required, and each mode has different scheduling. Two modes are supported:

  • read-write: Typically used for routings to a MySQL master.

    Mode Schedule: In read-write mode, all traffic is directed to the initial address on the list. If that fails, then MySQL Router will try the next entry on the list, and will continue trying each MySQL server on the list. If no more MySQL servers are available on the list, then routing is aborted. This method is also known as "first-available".

    The first successful MySQL server contacted is saved in memory as the first to try for future incoming connections. This is a temporary state, in that it won't be remembered after MySQL Router is restarted.

    [routing:example_strategy]
    bind_port = 7001
    destinations = master1.example.com,master2.example.com,master3.example.com
    mode = read-write
  • read-only: Typically used for routings to a MySQL slave.

    Mode Schedule: Mode read-only uses a simple round-robin method to go through the list of MySQL Servers. It sends the first connection to the first address on the list, the next connection to the second address, and so on, and will circle back to the first address after the list is exhausted.

    If a MySQL server is not available, then the next server is tried. When none of the MySQL servers on the list are available, then the routing is aborted.

    Unavailable MySQL server's are quarantined. Their availability is checked, and when available they are put back on the available destinations list. The destinations order is maintained.

    [routing:ro_route]
    bind_port = 7002
    destinations = slave1.example.com,slave2.example.com,slave3.example.com
    mode = read-only
Note

Both modes are available for connection routing with or without MySQL Fabric (standalone or Fabric integration).

Max Connections

Information related to the max_connections option. Each routing can limit the number of routes or connections. One possible use is to help prevent possible Denial-Of-Service (DOS) attacks. The default value is 512, and the valid range is between 1 and 65536.

This is similar to MySQL Server's max_connections server system variable.

max_connections = 512

Max Connection Errors

Information related to the max_connect_errors option. The default value is 100, and the valid range is between 1 and 2^32 (an unsigned int).

This is similar to MySQL Server's max_connect_errors server system variable.

This can cause a slight performance penalty if an application performs frequent reconnections, because Router attempts to discover if connection related errors are present.

Each routing has its own list of blocked hosts. Blocked clients receive the MySQL Server error 1129 code with a slightly different error message: "1129: Too many connection errors from fail.example.com". The Router logs contain extra information for blocked clients, such as: INFO [...] 1 authentication errors for fail.example.com (max 100) WARNING [...] blocking client host fail.example.com

max_connect_errors = 100
Note

This option was added in Router 2.0.3.

Client Connection Timeout

Information related to the client_connect_timeout option. The default value is 9, which is one less than the MySQL 5.7 default. The valid range is between 2 and 31536000.

This is similar to MySQL Server's connect_timeout server system variable.

client_connect_timeout = 9
Note

This option was added in Router 2.0.3.


User Comments
Sign Up Login You must be logged in to post a comment.