MySQL Router

注意：

此文档对应的 MySQL Router 版本为 8.0.32 社区版。

概述

MySQL Router 是一个 MySQL 代理，作为 InnoDB Cluster 的一部分，可在应用程序和后端 MySQL Server 之间提供透明路由，用于实现高可用性和负载均衡。它可以将客户端请求路由到一个或多个 MySQL Server，并提供故障转移和读写分离等功能。

安装

在官方网站下载最新版本的 MySQL Router，此处为 8.0.32。

上传到应用服务器进行安装：

[root@s1 ~]# yum localinstall mysql-router-community-8.0.32-1.el7.x86_64.rpm

部署

基于性能考虑，通常将 MySQL Router 安装在应用服务器，以便应用使用 UNIX 套接字连接到 MySQL Router。

引导

以下示例为通过引导，部署 MySQL Router 连接到 InnoDB Cluster。

$> mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter 
  --conf-use-sockets --account routerfriend --account-create always

Please enter MySQL password for root: 

# Bootstrapping MySQL Router instance at '/tmp/myrouter'...

Please enter MySQL password for routerfriend: 

- Creating account(s) 
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /tmp/myrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'myCluster'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /tmp/myrouter/mysqlrouter.conf

the cluster 'myCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446, /tmp/myrouter/mysql.sock
- Read/Only Connections:  localhost:6447, /tmp/myrouter/mysqlro.sock

## MySQL X protocol

- Read/Write Connections: localhost:6448, /tmp/myrouter/mysqlx.sock
- Read/Only Connections:  localhost:6449, /tmp/myrouter/mysqlxro.sock

其中：

• --bootstrap：执行引导，后面跟已存在的 InnoDB Cluster 中一个实例的 URI。如果提供的是只读实例的 URI，会自动重连到读写实例，以便注册 MySQL Router。

• --directory：指定 MySQL Router 实例目录

• --conf-use-sockets：启用 UNIX 套接字

• --account：指定 MySQL Router 用户

• --account-create：指定用户创建策略，有 3 种策略：

  • if-not-exists：默认策略，如果用户存在就使用，不存在就创建
  • always：只有在用户不存在时才进行引导，并创建用户
  • never：只有在用户存在的时候才进行引导

在实例目录下创建如下目录和文件：

$> ls -l /tmp/myrouter | awk '{print $9}'

data/
log/
mysqlrouter.conf
mysqlrouter.key
run/
start.sh
stop.sh

其中 MySQL Router 配置文件 mysqlrouter.conf 内容如下：

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=/tmp/myrouter/log
runtime_folder=/tmp/myrouter/run
data_folder=/tmp/myrouter/data
keyring_path=/tmp/myrouter/data/keyring
master_key_path=/tmp/myrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/tmp/myrouter/data/state.json

[logger]
level = INFO

[metadata_cache:myCluster]
cluster_type=gr
router_id=1
user=routerfriend
metadata_cluster=myCluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:myCluster_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/tmp/myrouter/mysql.sock
destinations=metadata-cache://myCluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:myCluster_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/tmp/myrouter/mysqlro.sock
destinations=metadata-cache://myCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:myCluster_x_rw]
bind_address=0.0.0.0
bind_port=6448
socket=/tmp/myrouter/mysqlx.sock
destinations=metadata-cache://myCluster/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:myCluster_x_ro]
bind_address=0.0.0.0
bind_port=6449
socket=/tmp/myrouter/mysqlx.sock
destinations=metadata-cache://myCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

可以看到为连接到名称为 myCluster 的 InnoDB Cluster 配置了 4 个端口和套接字。

使用客户端连接 6446 端口进行验证：

$> mysql -u root -h 127.0.0.1 -P 6446 -p

...

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3310 |
+--------+
1 row in set (0.00 sec)

配置

首选使用 --bootstrap 选项，通过引导来生成 MySQL Router 配置文件 mysqlrouter.conf。

语法

配置文件格式类似于传统的 INI 文件格式，包括 section 和 option，还包括有一些额外的扩展。

格式如下：

[section name]
option = value
option = value
option = value

[section name:optional section key]

option = value
option = value
option = value

位置

在 Linux，MySQL Router 默认读取以下位置的配置文件：

• /etc/mysqlrouter/mysqlrouter.conf
• $HOME/.mysqlrouter.conf

使用以下选项使用指定配置文件：

• --config 或者 -c：使用指定的配置文件，不使用默认配置文件
• --extra-config 或者 -a：在使用默认配置文件或者指定的配置文件外，需要读取的额外配置文件

例子：指定配置文件，先加载 --config 指定的 router.conf，再加载 --extra-config 指定的 config.conf

$> mysqlrouter --config /custom/path/to/router.conf --extra-config /another/config.conf

例子：指定配置文件，先加载 --config 指定的 b.conf，再依次加载 --extra-config 指定的 a.conf 和 c.conf

$> mysqlrouter --extra-config a.conf --config b.conf --extra-config c.conf

选项

可以在运行时指定和覆盖配置文件中的选项：

• 在生成配置文件时，使用 mysqlrouter --help 列出的选项

$> mysqlrouter --bootstrap foo@bar.com --connect-timeout=20

• 在启动时使用 --section[:section_key].option_name=option_value ，不会修改配置文件

$> mysqlrouter -c mysqrouter.conf --logger.level=debug

• 在生成配置文件时，使用 --conf-set-option=section[:section_key].option_name=option_value 选项，具有最高优先级

$> mysqlrouter --bootstrap foo@bar.com \
  --conf-set-option=logger.level=debug \
  --conf-set-option=DEFAULT.unknown_config_option=warning \
  --conf-set-option=DEFAULT.connect_timeout=20 \
  --connect-timeout=10

此时 --conf-set-option=DEFAULT.connect_timeout=20 的优先级比 --connect-timeout=10 高，故 connect_timeout 为 20。

命令行选项

General Options

Option Name	Description	Introduced
--conf-set-option	Sets a value for a generated configuration option during bootstrap	8.0.28
--config	Read configuration options from the provided file
--extra-config	Read this file after configuration files are read from either default locations or from files specified by the --config option
--help	Display help text and exit
--pid-file	Location to store the PID file	8.0.20
--user	Run mysqlrouter as the user having the defined user name or numeric user id
--version	Display version information and exit

Bootstrapping Options

Option Name	Description	Introduced
--account	The MySQL user account used by Router after bootstrapping	8.0.19
--account-create	Bootstrapped account creation behavior	8.0.19
--account-host	The host pattern used for bootstrapped accounts	8.0.12
--bootstrap	Bootstrap and configure Router for operation with a MySQL InnoDB cluster
--bootstrap-socket	Connect to the MySQL metadata server through a Unix domain socket, used in conjunction with --bootstrap
--conf-base-port	Base port to use for listening Router ports
--conf-bind-address	IP address of the interface to which router's listening sockets should bind
--conf-skip-tcp	Whether to disable binding of a TCP port for incoming connections
--conf-target-cluster	Sets the target_cluster metadata option to a cluster type	8.0.27
--conf-target-cluster-by-name	Sets the target_cluster metadata option to a specific cluster name	8.0.27
--conf-use-gr-notifications	Enables Group Replication notifications	8.0.17
--conf-use-sockets	Whether to use Unix domain sockets
--connect-timeout	Number of seconds before connection attempts to a metadata server are considered timed out
--directory	Creates a self-contained directory for a new instance of the Router
--disable-rest	Disables generation of REST API configuration details into the generated mysqlrouter.conf file	8.0.22
--force	Force reconfiguration of a possibly existing instance of the router
--force-password-validation	When creating a user account automatically, do not skip the validate_password mechanism
--https-port	MySQL Router REST API HTTP server port	8.0.22
--master-key-reader	Script that returns the master key to STDOUT	8.0.12
--master-key-writer	Script that reads the master key from STDIN	8.0.12
--name	Gives a symbolic name for the router instance
--password-retries	The number of retries to use for generating the Router's user password
--read-timeout	Number of seconds before read operations to a metadata server are considered timed out
--report-host	Router's hostname; overrides auto-detection	8.0.12
--strict	Enables bootstrap strict mode	8.0.19

配置文件选项

General Options

Option Name	Description	Type
config_folder	Path to configuration files	String
connect_timeout	Number of seconds before connection attempts to a metadata server are considered timed out; set in the [DEFAULT] section.	Integer
core-file	Write core file on Router crashes	Boolean
event_source_name	Microsoft Windows platforms only. Defines the service name used by MySQL Router when it is run as a service on Microsoft Windows.	String
keyring_path	Path to keyring file	String
logging_folder	Path to router logs	String
master_key_path	Path to master keyring file	String
master-key-reader	Script that returns the master key to STDOUT	String
master-key-writer	Script that reads the master key from STDIN	String
max_total_connections	Total maximum number of allowed client connections from the router	Integer
pid_file	Location to store the PID file	String
plugin_folder	Path to router plugins	String
runtime_folder	Path to runtime files	String
sinks	Logging method(s) to receive configured log data	String
thread_stack_size	Size in KB of memory allocated to each thread stack	Integer
unknown_config_option	Error type sent if an unknown configuration option is encountered	String
user	System user that router is run as	String

Routing Options

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
client_ssl_cipher	Which ciphers are allowed between client and MySQL Router, defaults to a secure list of SSL ciphers	String
client_ssl_curves	Which curves are allowed between the client and MySQL Router, defaults to a secure list of SSL curves	String
client_ssl_dh_params	Filename of the DH parameter file. Not set by default	String
client_ssl_mode	Controls if connections from the client to MySQL Router must be encrypted, defaults to PREFERRED if not set	String
connect_timeout	Number of seconds before connection attempts to a MySQL server are considered timed out; set in the [routing] section.	Integer
connection_sharing	Whether to enable connection sharing.	Integer
connection_sharing_delay	Seconds to wait before moving an idle connection to the connection pool.	Numeric
destinations	Routing destinations as either a comma-separated list of MySQL servers, or a metadata-cache definition	String
dynamic_state	Path to generated JSON file used to track and store active MySQL InnoDB Cluster Metadata server addresses	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
net_buffer_length	Set net_buffer_length	Integer
protocol	Protocol for connecting to MySQL Server	String
read_timeout	Number of seconds before read operations to a metadata server are considered timed out	Integer
routing_strategy	Routing strategy (optional), how router chooses destination MySQL servers	String
server_ssl_cipher	SSL Cipher for Server	String
server_ssl_curves	SSL Curves for Server	String
server_ssl_mode	SSL Mode for Server	String
server_ssl_verify	SSL Verify for Server	String
socket	Path to Unix domain socket file	String
unreachable_destination_refresh_interval	How often, in seconds, unreachable destination candidates are probed for availability.	Integer

Destination Status Options

Option Name	Description	Type
error_quarantine_interval	Defines the interval, in seconds, between checks on quarantined destination connectivity. If a connection is possible, the destination is moved out of quarantine and made available for connections.	Integer
error_quarantine_threshold	Defines the threshold of consecutive, failed attempts to connect to a routing destination before MySQL Router adds the destination to quarantine and stops using it as a destination until it is cleared by the quarantine mechanism. For example, if set to 5, the destination is quarantined after 5 consecutive, failed attempts to connect to it.	Integer

Table 4.10 [connection_pool]

Option Name	Description	Type
idle_timeout	Seconds to keep the idling connection in the collection pool before closing it	Numeric
max_idle_server_connections	Connections to keep open after the client disconnects	Numeric

Metadata Cache Options

Option Name	Description	Type
auth_cache_refresh_interval	Time between auth-cache refresh attempts	Numeric
auth_cache_ttl	Time until the cache becomes invalid if not refreshed	Numeric
bootstrap_server_addresses	MySQL servers with metadata, as a comma-separated list	String
cluster_type	Object Router was bootstrapped against	String
metadata_cluster	InnoDB Cluster name	String
router_id	Router ID	Integer
ssl_ca	SSL CA file to verify server's certificate against	String
ssl_capath	Directory containing SSL CA files to verify server's certificate against	String
ssl_crl	SSL CRL file to verify server's certificate against	String
ssl_crlpath	Directory containing SSL CRL files to verify server's certificate against	String
ssl_mode	SSL connection mode for connecting to the metadata server, defaults to PREFERRED if not set	String
tls_version	Comma-separated list of TLS versions to request, if SSL is enabled	String
ttl	Time To Live, in seconds	Integer
use_gr_notifications	Group Replication notifications behavior	Integer
user	MySQL user that accesses the MySQL Server's metadata schema	String

Logging Options

Option Name	Description	Type
destination	Name of device to log to; optionally used with [consolelog]	String
filename	Log file name; optionally used with [logger] and [filelog]	String
level	Logging level	String
timestamp_precision	Logger timestamp precision	String

HTTP Server Options

Option Name	Description	Type
bind_address	IP address bound to the HTTP port	String
port	HTTP server TCP port	Integer
require_realm	[http_auth_realm] name	String
ssl_cert	SSL certification file name	String
ssl_cipher	Approved SSL ciphers	String
ssl_dh_param	DH parameter file name	String
ssl	Enables TLSv1.2 or later support	Integer
ssl_key	SSL key filename	String
static_folder	Directory for HTTP server static file requests	String

http_auth_realm

Option Name	Description	Type
backend	Name of the [http_auth_backend] section	String
method	The HTTP authentication method	String
name	Realm name for authenticated user	String
require	Require authentication validation	String

http_auth_backend

Option Name	Description	Type
backend	Backend type	String
filename	Backend storage file name	String

io

Option Name	Description	Type
backend	The IO backend	String
threads	The IO thread count	Numeric

示例

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=/tmp/router/log
runtime_folder=/tmp/router/run
data_folder=/tmp/router/data
keyring_path=/tmp/router/data/keyring
master_key_path=/tmp/router/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/tmp/router/data/state.json
client_ssl_cert=/tmp/router/data/router-cert.pem
client_ssl_key=/tmp/router/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED

[logger]
level = INFO

[metadata_cache:myCluster]
cluster_type=gr
router_id=1
user=mysql_router1_x9v4uk10nbcd
metadata_cluster=myCluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:myCluster_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://myCluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:myCluster_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://myCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:myCluster_x_rw]
bind_address=0.0.0.0
bind_port=6448
destinations=metadata-cache://myCluster/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:myCluster_x_ro]
bind_address=0.0.0.0
bind_port=6449
destinations=metadata-cache://myCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

[http_server]
port=8443
ssl=1
ssl_cert=/tmp/router/data/router-cert.pem
ssl_key=/tmp/router/data/router-key.pem

[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm

[rest_router]
require_realm=default_auth_realm

[rest_api]

[http_auth_backend:default_auth_backend]
backend=metadata_cache

[rest_routing]
require_realm=default_auth_realm

[rest_metadata_cache]
require_realm=default_auth_realm

启动

使用默认配置文件启动 MySQL Router 并配置开机自动启动。

$> sudo systemctl start mysqlrouter.service
$> sudo systemctl enable mysqlrouter.service

日志

在配置文件中，在 [DEFAULT] 部分指定日志目录，在 [logger] 部分指定日志级别。

[DEFAULT]
# Logs are sent to /path/to/folder/mysqlrouter.log
logging_folder = /path/to/folder

[logger]
level = INFO

有 2 种日志级别：

• INFO：默认级别，基本信息
• DEBUG：用于诊断

日志轮转

/var/log/mysqlrouter/mysqlrouter.log {
    rotate 9
    size 10M
    create 0755 mysqlrouter mysqlrouter
    postrotate
    kill -HUP $(pidof mysqlrouter)
    endscript
}