Embedded Files
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
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...
6446 - Read/Write connections
6447 - Read Only connections
64460 - Read/Write X Protocol (DocStore) connections
64470 - Read Only X Protocol (DocStore) connections
Name Resolution
Name Resolution
All MySQL Nodes in the cluster should be reachable via DNS or /etc/hosts
Cluster Configuration
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
Install
Start MySQL Router
Start MySQL Router
UNIX
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
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
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
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
Remove Router
If you see an unwanted router in listRouters()
cluster.listRouters()
cluster.removeRouterMetadata('RouterNameAsShownBy-listRouters')
Default file locations
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
mysqlrouter.conf
On Windows...
C:\ProgramData\MySQL\MySQL Router\mysqlrouter.conf
C:\Program File\MySQL\MySQL Router\mysqlrouter.conf
For a system-wide installationC:\Users\username\AppData\Roaming\mysqlrouter.conf
For a user-specific installationNote 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).
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
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
Bibliography & References
https://dev.mysql.com/downloads/router/https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-deploying-sandbox.htmlhttps://docs.oracle.com/cd/E17952_01/mysql-shell-8.0-en/admin-api-using-router.htmlhttps://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-conf-options.html#option_mysqlrouter_user_defaulthttps://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-server-starting.htmlhttps://dev.mysql.com/doc/mysql-shell/8.0/en/admin-api-bootstrapping-router.htmlhttps://docs.oracle.com/cd/E17952_01/mysql-router-8.0-en/mysqlrouter.htmlhttps://dev.mysql.com/doc/mysql-router/9.1/en/mysqlrouter.html#option_mysqlrouter_config(1) https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-faq.html(2) https://docs.oracle.com/cd/E17952_01/mysql-shell-8.0-en/admin-api-bootstrapping-router.htmlhttps://dev.mysql.com/doc/mysql-router/8.4/en/mysqlrouter.html
https://support.oracle.comHow to Make MySQL Router Automatically Restart on Systemd (Doc ID 2684939.1)MySQL Router Deployment Recommendations (Doc ID 2277385.1)MySQL Router User when Bootstrapping (Doc ID 2449015.1)Primary Note For MySQL Router (Doc ID 2265731.1)How To Create a Local Yum Repository for MySQL Enterprise Packages (Doc ID 1947384.1)
https://support.oracle.comHow to Make MySQL Router Automatically Restart on Systemd (Doc ID 2684939.1)MySQL Router Deployment Recommendations (Doc ID 2277385.1)MySQL Router User when Bootstrapping (Doc ID 2449015.1)Primary Note For MySQL Router (Doc ID 2265731.1)How To Create a Local Yum Repository for MySQL Enterprise Packages (Doc ID 1947384.1)
Page updated
Google Sites
Report abuse