MySQL Router  /  ...  /  Configuration File Options

4.3.2 Configuration File Options

When started, MySQL Router reads a list of configuration files that together make up the configuration of the router. At least one configuration file is required.

MySQL Router reads options from configuration files that closely resemble the traditional INI file format, with sections and options. These specify the options set when MySQL Router starts. For file syntax information, see Section 4.1, “Configuration File Syntax”.

Options are defined under sections, that dictate the option's meaning. For example, user under the [DEFAULT] section refers to the system user running router, while user under the [metadata_cache] section refers to the MySQL user that accesses metadata.

The following tables are separated by section, and summarize the MySQL Router options defined in a MySQL Router configuration file. Detailed information about each of these options, such as descriptions and allowed values, is documented below these tables.

General Options

Table 4.7 [DEFAULT]

Option Name Description Type
config_folder Path to configuration files string
keyring_path Path to keyring file string
logging_folder Path to router logs string
master_key_path Path to master keyring file string
plugin_folder Path to router plugins string
runtime_folder Path to runtime files string
user System user that router is run as string

Routing Options

Table 4.8 [routing]

Option Name Description Type
bind_address Address router is bound to, also uses bind_port if a port is not defined string
bind_port Default port used by bind_address integer
client_connect_timeout Maximum number of seconds to receive packets from MySQL server integer
connect_timeout Number of seconds before connection attempts to a MySQL server are considered timed out integer
destinations Routing destinations as a comma-separated list of MySQL servers string
max_connect_errors Maximum number of failed MySQL server connections before giving up integer
max_connections Maximum number of connections assigned to a routed destination MySQL server integer
mode Routing mode, how router chooses destination MySQL servers string
protocol Protocol for connecting to MySQL Server string
socket Path to unix domain socket file string

Metadata Cache Options

Table 4.9 [metadata_cache]

Option Name Description Type
bootstrap_server_addresses MySQL servers with metadata, as a comma-separated list string
metadata_cluster InnoDB cluster name string
router_id Router ID integer
ssl_mode SSL connection mode for connecting to the metadata server, defaults to PREFERRED if not set string
ttl Time To Live, in seconds integer
user MySQL user that accesses the MySQL Server's metadata schema string

Logging Options

Table 4.10 [logger]

Option Name Description Type
level Logging level string

MySQL Router Configuration File Option Descriptions

  • logging_folder

    Permitted Values Type string
    Default $router_basepath

    Path to the MySQL Router log file directory. The log file is named mysqlrouter.log, and it is either generated or appended to if this file already exists.

    Setting logging_folder to an empty value sends the messages to the console (stdout).

    Note

    The default logging_folder value changed from "" to Router's base path in MySQL Router 2.1.

    An example that sends logs to /var/log/mysqlrouter/mysqlrouter.log:

    [DEFAULT]
    logging_folder = /var/log/mysqlrouter

    When the --directory bootstrap option is used, the generated configuration file sets it to $directory/log/.

  • plugin_folder

    Permitted Values (Windows) Type string
    Default
    Permitted Values (Other) Type string
    Default /usr/local/lib/mysqlrouter

    Path to the MySQL Router plugins. This folder must match the MySQL Router installation directory. You should only set this if you have a custom installation where the plugins are not in the standard installation location.

    Default value: /usr/local/lib/mysqlrouter

  • runtime_folder

    Permitted Values (Windows) Type string
    Default
    Permitted Values (Other) Type string
    Default /run/mysqlrouter

    Path to the MySQL Router runtime files.

    Default value: /run/mysqlrouter

  • config_folder

    Permitted Values (Windows) Type string
    Default
    Permitted Values (Other) Type string
    Default /usr/local/etc/mysqlrouter

    Path to the MySQL Router configuration files.

    Note

    The config_folder is currently set at compile time. The option could be used by future plugins when they have their own configuration files.

    Default value: /usr/local/etc/mysqlrouter

  • keyring_path

    Permitted Values (Windows) Type string
    Default %PROGRAMDATA%\MySQL\MySQL Router\keyring-data
    Permitted Values (Other) Type string
    Default /run/mysql-router/keyring-data

    Points to the keyring file's location.

    A system-wide bootstrap does not add this option to the generated configuration file, and assumes the keyring file is located in the system-wide runtime state directory. If --directory is also used, then the keyring file is stored under the runtime state directory of that instance, under run/ in the specified directory.

    System-wide default paths are used if this option is not defined.

    Example usage:

    keyring_path = /opt/myrouter/data/keyring
    master_key_path = /opt/myrouter/mysqlrouter.key
    Note

    This option was added in MySQL Router 2.1.

  • master_key_path

    Permitted Values (Windows) Type string
    Default %PROGRAMDATA%\MySQL\MySQL Router\mysqlrouter.key
    Permitted Values (Other) Type string
    Default /run/mysql-router/mysqlrouter.key

    The master key file's location. This option allows unattended decryption, as otherwise its location is requested at startup.

    System-wide default paths are used if this option is not specified.

    Example usage:

    keyring_path = /opt/myrouter/data/keyring
    master_key_path = /opt/myrouter/mysqlrouter.key
    Note

    This option was added in MySQL Router 2.1.

  • user (system)

    Permitted Values Type string

    Run mysqlrouter as the user having the name user_name or the numeric user ID user_id. User in this context refers to a system login account, not a MySQL user listed in the grant tables. This can also be assigned at runtime using the --user command line option.

    The purpose of this option is to run MySQL Router as a user with restricted system privileges. If the user does not exist on the system, or if an attempt to start Router as root is made, an error is emitted and Router exits.

    MySQL Router can be bootstrapped and executed under any Operating System user and does not require special privileges other than read and write access to its own files. The files it accesses include plugins (read/execute), configuration file, logs, UNIX domain socket files (if enabled), and more.

    By default, the configuration and log files are written to a system-wide location such as /etc and /var/log. Alternatively, Router can be bootstrapped to a self-contained directory of its own by using the --directory option. For example:

    shell> sudo mysqlrouter --bootstrap localhost:3310 --directory /a/path/myrouter --user snoopy

    In this example, Router creates /a/path/myrouter and adds all of the generated files and directories here, and these are only writable by the system user snoopy. Additionally, user is defined in the generated configuration file /a/path/myrouter/mysqlrouter.conf:

    [DEFAULT]
    user=snoopy
    Note

    An account created by the official MySQL Router packages does not have shell access and its home directory points to the directory where the default configuration file is stored.

    Note

    This is different from the user definition defined in the [metadata_cache] section, which is a MySQL user.

  • bind_address

    Permitted Values Type string
    Default 127.0.0.1

    Information related to the optional bind_address option:

    • Routing entries can be bound 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.

    • By default, --bootstrap sets bind_address=0.0.0.0 for each route in the generated Router configuration file. This value can be changed using --conf-bind-address.

    • Binding to a specific IPv4 or IPv6 address allows and ensures that MySQL Router is not starting and routing the service on an 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) binds all network interfaces (IPs) on the host. IPv6 addresses can also be used.

    Example usage:

    bind_address = 127.0.0.1:7001
    Note

    The bind_address cannot be listed in the destinations list.

  • bind_port

    Permitted Values Type integer

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

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

    [routing:example_1]
    bind_port = 7001
    [routing:example_2]
    bind_port = 7001
    bind_address = 127.0.0.1
    [routing:example_3]
    bind_address = 127.0.0.1:7001
  • socket

    Platform Specific Unix
    Permitted Values Type string

    Sockets are enabled using the socket option, which can be specified with or without the TCP bind_port and bind_address options. An example:

    [routing]
    socket = /tmp/mysqlrouter.sock
    destinations = a.example.com:3306,b.example.com:3307

    When launching MySQL Router, Router will refuse to run if either the socket file already exists or it cannot be written to.

    Relative paths are acceptable and based on the current working directory where Router is launched.

    Router can listen to both TCP sockets and Unix sockets simultaneously. For example, the following [routing] configuration example is valid and configures Router to listen for connections on both localhost:1234 and /tmp/mysqlrouter.sock:

    [routing:my_redirect]
    bind_address = localhost:1234
    socket = /tmp/mysqlrouter.sock
    mode = read-write
    destinations = localhost:57121, localhost:57122, localhost:57123
    Note

    A Unix domain socket length limit is platform-specific and should not exceed the system's allowed length.

  • protocol

    Permitted Values Type string
    Default classic
    Valid Values classic
    x

    Used by the routing plugin when connecting to the destination MySQL server, and can be set to either "classic" (default), or "x" (X Protocol).

    Example usage:

    [routing:basic_failover]
    bind_port = 7001
    mode = read-write
    destinations = 10.20.200.1:33060, 10.20.200.2:33060
    protocol = x

    The protocol option also affects the default port used by by each destination. If a destination port is not configured, then the default port is 3306 for "classic" (default), 33060 for "x" (X Protocol).

    Note

    The protocol option, and general X protocol support, was added in Router 2.1.

  • connect_timeout

    Permitted Values Type integer
    Default 1
    Minimum 1
    Maximum 65536

    Timeout value used by the MySQL Router 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 little higher to wait for the PRIMARY to become available. When using read-only mode for secondary connections, a lower value makes more sense because Router selects a new server during connection routing.

    Example usage:

    connect_timeout = 1
  • destinations

    Permitted Values Type string

    Provides a comma-separated list of destination addresses that should be used when establishing connections. Exact behavior depends on the mode option, and its associated strategy.

    Example usage:

    destinations = a.example.com,b.example.com,c.example.com
    Note

    If a destination's port is not explicitly set, then the default port is 3306 if protocol is set to "classic" or not set (default), or port 33060 if protocol is set to "x".

  • mode

    Permitted Values Type string
    Valid Values read-write
    read-only

    Setting this parameter is required, and each mode has different scheduling. Two modes are supported:

    • read-write: Typically used for routing to a master or primary MySQL instance.

      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, meaning this is forgotten after MySQL Router is restarted.

      [routing:example_strategy]
      bind_port = 7001
      destinations = primary1.example.com,primary2.example.com,primary3.example.com
      mode = read-write
    • read-only: Typically used for routing to a slave or secondary MySQL instance.

      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 circles 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 servers are quarantined. Their availability is checked, and when available they are put back onto the available destinations list. The destinations order is maintained.

      [routing:ro_route]
      bind_port = 7002
      destinations = secondary1.example.com,secondary2.example.com,secondary3.example.com
      mode = read-only
  • max_connections

    Permitted Values Type integer
    Default 512
    Minimum 1
    Maximum 65536

    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

    Known limitation: A MySQL Router instance can handle just over 500 concurrent connections. See also this related FAQ .

  • max_connect_errors

    Permitted Values Type integer
    Default 100
    Minimum 1
    Maximum 4294967296

    The default value is 100, and the valid range is between 1 and 2^32 (4294967296, 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 MySQL 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
  • client_connect_timeout

    Permitted Values Type integer
    Default 9
    Minimum 2
    Maximum 31536000

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

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

    client_connect_timeout = 9
  • router_id

    Permitted Values Type integer

    The MySQL Router ID.

  • ssl_mode

    Permitted Values Type string
    Default PREFERRED
    Valid Values PREFERRED
    DISABLED
    REQUIRED
    VERIFY_CA
    VERIFY_IDENTITY

    SSL mode for connecting to the MySQL metadata server. It defaults to PREFERRED if not set.

    When set to PREFERRED (the default), bootstrapping will warn when SSL is not used and connection to the metadata server is unencrypted.

    Available values are DISABLED, PREFERRED, REQUIRED, VERIFY_CA, and VERIFY_IDENTITY. As with the mysql client, this value is case-insensitive.

    There is also a runtime option for bootstrapping; see --ssl-mode.

  • bootstrap_server_addresses

    Permitted Values Type string

    Points to a list of MySQL servers with metadata that can be connected to. After the metadata has been accessed, the metadata cache switches to the servers that are present in the primary ReplicaSet to fetch the metadata. They are also known as bootstrap servers.

  • user (MySQL)

    Permitted Values Type string

    A generated MySQL user with privileges to access the MySQL server's metadata schema. This user's password is auto-generated and stored in an encrypted keyring. By default, the encryption key for this keyring is stored in a read protected master key store file, which is defined in the configuration file. Most commonly, this user and associated password are automatically generated during bootstrap. Related command line options are --force-password-validation and --password-retries. By default, the generated password passes the STRONG validate_password strength.

    The password is entirely managed by Router and never exposed, and is stored in a local keyring system using the operating system's account that MySQL Router is running as. It can then be used by Router to connect to InnoDB cluster and retrieve current topology information. Sessions between Router and metadata server are encrypted with SSL by default.

    Where the generated keyring files are stored depends on how bootstrap is configured. For self-contained installations (when --directory is used), it is stored under run/ in the self-contained directory. For system-wide installations, it is stored in the system-wide runtime state directory, and that path is platform specific. For additional information, see master_key_path and keyring_path

    This user is assigned (and requires) the following privileges:

    Privileges needed by the Router account:
    
    	On Metadata Server:
    
    		SELECT ON mysql_innodb_cluster_metadata.*
    
    	On Target Replica Sets:
    
    		SELECT ON performance_schema.replication_group_members
    		SELECT ON performance_schema.replication_group_member_stats

    The generated username follows this pattern: mysql_router_[0-9]{1,6}_[0-9a-z]{12}, where [0-9]{1,6} is the numeric router id and [0-9a-z]{12} is 12 random lowercase alphanumeric characters. The router id is reused if already present in mysqlrouter.conf and its length can not exceed 6 digits.

    Note

    This user is different from the user definition defined in the [DEFAULT] section, which is a system user.

  • metadata_cluster

    Permitted Values Type string

    Name of the InnoDB cluster.

    Note

    SQL query to list the MySQL InnoDB cluster names: SELECT * FROM mysql_innodb_cluster_metadata.clusters;

  • ttl

    Permitted Values Type integer
    Default 300

    Time to live (in seconds) of information in the metadata cache.

  • level

    Permitted Values Type string
    Default INFO
    Valid Values INFO
    DEBUG
    WARNING
    ERROR
    FATAL

    Use the logger plugin to log notices, errors, and debugging information. The available log levels are INFO (default), DEBUG, WARNING, ERROR, and FATAL. These values are case-insensitive.

    The INFO level displays all informational messages, warnings, and error messages. The DEBUG level displays additional diagnostic information from the Router code, including successful routes.

    [logger]
    level = DEBUG

    Output behavior depends on the logging_folder option. Setting logging_folder to a folder saves a log file named mysqlrouter.log to that folder. Setting logging_folder to an empty value, or not setting it, outputs the log to the console. It is set in the [DEFAULT] section.


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