MySQL Router

Once you have an InnoDB Cluster, you should install MySQL Router on your application servers/clients.


This document assumes MySQL Enterprise Edition as this is slightly more complex. Community Edition will be similar.Linux steps on this page are tested on Oracle Linux 7.

For best performance, MySQL Router is typically installed on the same host as the application that uses it. Doing so can decrease network latency, allow a local UNIX domain socket connection to the application instead of TCP/IP, and typically application server's are easiest to scale. (1)


In order to configure/test your MySQL Router you should also install MySQL Shell on the target application servers/clients.


Ports

Connections via MySQL Router will be made, in the background, on the regular MySQL port (3306) but the application will need to talk to the local MySQL Router using a different port number as outlined below...

Name Resolution

All MySQL Nodes in the cluster should be reachable via DNS or /etc/hosts

Cluster Configuration

NOTE: This step only needs to be done once. 

It does not need to be repeated when adding additional Routers on new application servers that connect to the same innodb cluster.

On your Read/Write database server node (as 'root')...

Using your icadmin account is recommended...

mysqlsh

\c root@localhost

var cluster = dba.getCluster('myCluster');

If you want the Router to connect using an existing database user (icadmin in this example)...

cluster.setupRouterAccount('icadmin', {'update':1})

\q

If you can't remember the cluster name use this to find it...

dba.getCluster()

If you want to use a user other than root or icadmin...

GRANT create user TO myuser;

Other grants may be required (I have only properly tested with icadmin)

If you want to create a new user...

cluster.setupRouterAccount('myuser')

\q

Install

Start MySQL Router

UNIX

systemctl start mysqlrouter

With systemd...

systemctl start mysqlrouter

systemctl restart mysqlrouter

systemctl stop mysqlrouter

Without systemd...

/mysql/myrouter/start.sh

/mysql/myrouter/stop.sh

Windows

net start mysqlrouter

or

C:\"Program Files"\MySQL\"MySQL Router 8.0"\bin\mysqlrouter -c C:/"Program Files"/MySQL/"MySQL Router 8.0"/mysqlrouter.conf --service

Check

mysqlsh

\c icadmin@node1:3306

var cluster = dba.getCluster();

cluster.listRouters()

cluster.listRouters({'onlyUpgradeRequired':'true'})

Shows only the MySQL Router instances registered with the Cluster which require an upgrade of their metadata.

\q

On the target client...

mysqlsh

\c myuser@myhost:6446

\sql

SELECT @@hostname, @@port;

\q


cd /mysql/myrouter/log

tail -f mysqlrouter.log

With systemd...

systemctl status mysqlrouter

journalctl -u mysqlrouter

Update Router Metadata

Without upgrading the metadata you cannot use a later version of MySQL Shell to change the configuration of a cluster created with earlier versions.

mysqlsh

\c root@node1:3306

var cluster = dba.getCluster('myCluster')

cluster.listRouters({'onlyUpgradeRequired':'true'})

dba.upgradeMetadata()

\q

Remove Router

If you see an unwanted router in listRouters() 

cluster.listRouters()

cluster.removeRouterMetadata('RouterNameAsShownBy-listRouters')

Default file locations

Files locations can be identified using...

mysqlrouter --help

File info from --help output on a Windows server...

C:\"Program Files"\MySQL\"MySQL Router 8.0"\bin\mysqlrouter --helpMySQL Router  Ver 8.0.27-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial)Copyright (c) 2015, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Configuration read from the following files in the given order (enclosedin parentheses means not available for reading):  (C:/Program Files/MySQL/MySQL Router 8.0/bin/.././mysqlrouter.conf)  (C:/Program Files/MySQL/MySQL Router 8.0/bin/.././mysqlrouter.ini)  (C:\Users\admin_mpg\AppData\Roaming/mysqlrouter.conf)  (C:\Users\admin_mpg\AppData\Roaming/mysqlrouter.ini)Plugins Path:  C:/Program Files/MySQL/MySQL Router 8.0/lib
Default Log Directory:  C:/Program Files/MySQL/MySQL Router 8.0
Default Persistent Data Directory:  C:/Program Files/MySQL/MySQL Router 8.0/bin/../var/lib/mysqlrouter
Default Runtime State Directory:  C:/Program Files/MySQL/MySQL Router 8.0/bin/../run

mysqlrouter.conf

On Windows...

C:\ProgramData\MySQL\MySQL Router\mysqlrouter.conf

C:\Program File\MySQL\MySQL Router\mysqlrouter.conf

For a system-wide installation

C:\Users\username\AppData\Roaming\mysqlrouter.conf

For a user-specific installation
Note that ProgramData often won't be visible in Windows File Explorer but can be reached if you type the path in the address bar or if you change the File Explorer "View" to show Hidden Files.
You may also struggle to open the config file in a text editor (like Notepad or Notepad++) unless you add yourself appropriate permissions in the "Security" tab of mysqlrouter.conf "Properties" (accessed by right-clicking on the file).

Example mysqlrouter.conf file from a Windows installation of MySQL Router...

# File automatically generated during MySQL Router bootstrap[DEFAULT]logging_folder=C:/ProgramData/MySQL/MySQL Router/logruntime_folder=C:/ProgramData/MySQL/MySQL Router/rundata_folder=C:/ProgramData/MySQL/MySQL Router/datakeyring_path=C:/ProgramData/MySQL/MySQL Router/data/keyringmaster_key_path=C:/ProgramData/MySQL/MySQL Router/mysqlrouter.keyconnect_timeout=15read_timeout=30dynamic_state=C:/ProgramData/MySQL/MySQL Router/data/state.jsonclient_ssl_cert=C:/ProgramData/MySQL/MySQL Router/data/router-cert.pemclient_ssl_key=C:/ProgramData/MySQL/MySQL Router/data/router-key.pemclient_ssl_mode=PREFERREDserver_ssl_mode=AS_CLIENTserver_ssl_verify=DISABLED
[logger]level = INFO
[metadata_cache:myic002]cluster_type=grrouter_id=9user=mysql_router9_x978u2nlpcdrmetadata_cluster=myic002ttl=0.5auth_cache_ttl=-1auth_cache_refresh_interval=2use_gr_notifications=0
[routing:myic002_rw]bind_address=0.0.0.0bind_port=6446destinations=metadata-cache://myic002/?role=PRIMARYrouting_strategy=first-availableprotocol=classic
[routing:myic002_ro]bind_address=0.0.0.0bind_port=6447destinations=metadata-cache://myic002/?role=SECONDARYrouting_strategy=round-robin-with-fallbackprotocol=classic
[routing:myic002_x_rw]bind_address=0.0.0.0bind_port=6448destinations=metadata-cache://myic002/?role=PRIMARYrouting_strategy=first-availableprotocol=x
[routing:myic002_x_ro]bind_address=0.0.0.0bind_port=6449destinations=metadata-cache://myic002/?role=SECONDARYrouting_strategy=round-robin-with-fallbackprotocol=x
[http_server]port=8443ssl=1ssl_cert=C:/ProgramData/MySQL/MySQL Router/data/router-cert.pemssl_key=C:/ProgramData/MySQL/MySQL Router/data/router-key.pem
[http_auth_realm:default_auth_realm]backend=default_auth_backendmethod=basicname=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

mysqlrouter.log

Example mysqlrouter.log file from a Windows installation of MySQL Router...

2025-01-08 10:36:25 main SYSTEM [46f4] Starting 'MySQL Router', version: 8.0.40 (MySQL Community - GPL)2025-01-08 10:36:25 io INFO [46f4] starting 4 io-threads, using backend 'poll'2025-01-08 10:36:25 http_server INFO [46f4] listening on 0.0.0.0:84432025-01-08 10:36:25 metadata_cache_plugin INFO [7d54] Starting Metadata Cache2025-01-08 10:36:25 metadata_cache INFO [7d54] Connections using ssl_mode 'PREFERRED'2025-01-08 10:36:25 routing INFO [6db4] [routing:bootstrap_rw] started: routing strategy = first-available2025-01-08 10:36:25 routing INFO [6db4] Start accepting connections for routing routing:bootstrap_rw listening on '0.0.0.0:6446'2025-01-08 10:36:25 routing INFO [7dd4] [routing:bootstrap_x_ro] started: routing strategy = round-robin-with-fallback2025-01-08 10:36:25 routing INFO [7dd4] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'2025-01-08 10:36:25 metadata_cache INFO [7060] Starting metadata cache refresh thread2025-01-08 10:36:25 routing INFO [7cc8] [routing:bootstrap_x_rw] started: routing strategy = first-available2025-01-08 10:36:25 routing INFO [7cc8] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'2025-01-08 10:36:25 routing INFO [5cd8] [routing:bootstrap_ro] started: routing strategy = round-robin-with-fallback2025-01-08 10:36:25 routing INFO [5cd8] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'2025-01-08 10:36:25 metadata_cache INFO [7060] Connected with metadata server running on mynode004:33062025-01-08 10:36:25 metadata_cache INFO [7060] Potential changes detected in cluster after metadata refresh (view_id=0)2025-01-08 10:36:25 metadata_cache INFO [7060] Metadata for cluster 'myic002' has 3 member(s), single-primary: 2025-01-08 10:36:25 metadata_cache INFO [7060]     mynode004:3306 / 33060 - mode=RW 2025-01-08 10:36:25 metadata_cache INFO [7060]     mynode006:3306 / 33060 - mode=RO 2025-01-08 10:36:25 metadata_cache INFO [7060]     mynode005:3306 / 33060 - mode=RO 2025-01-08 10:57:28 routing INFO [7ea0] [routing:bootstrap_rw] incrementing error counter for host of 127.0.0.1:56646 (now 1)2025-01-08 10:57:29 routing INFO [6284] [routing:bootstrap_rw] resetting error counter for 127.0.0.1:56663 (was 1)

Bibliography & References