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...
6446 - Read/Write connections
6447 - Read Only connections
64460 - Read/Write X Protocol (DocStore) connections
64470 - Read Only X Protocol (DocStore) connections
Name Resolution
All MySQL Nodes in the cluster should be reachable via DNS or /etc/hosts
Firewall
Linux (firewalld)
As 'root'...
firewall-cmd --zone=public --add-port=6446/tcp --permanent
firewall-cmd --zone=public --add-port=64460/tcp --permanent
firewall-cmd --zone=public --add-port=6647/tcp --permanent
firewall-cmd --zone=public --add-port=64470/tcp --permanent
systemctl restart firewalld
firewall-cmd --list-port
Windows
TODO
SELinux
Check current status using one/both of these commands...
sestatus
getenforce
If your server has SELinux enabled, you have two choices. Disable SELinux (not recommended, but might be right for a test system), or configure SELinux to allow our configuration...Â
Note: if your process is running as root, then the root login is generally mapped to the unconfined_u user in SELinux, which means the following config may not be necessary.Configure SELinux
semanage port -a -t mysqld_port_t -p tcp 6446
semanage port -a -t mysqld_port_t -p tcp 64460
semanage port -a -t mysqld_port_t -p tcp 6447
semanage port -a -t mysqld_port_t -p tcp 64470
Disable SELinux
Assuming SELinux is currently set to 'enforcing'...setenforce 0
getenforce
sed -i 's/SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config
Install (Linux)
TAR
Using the TAR distribution gives the most flexibility (e.g. if you install under /mysql using the TAR distribution your backout is easier... you just need to point the symbolic link back to the previous version) but there are more manual steps.As 'root'...
cd /mysql
unzip ${ZIPFILE}
tar xvf ${TARFILE}.tar.gz
ln -s ${TARFILE} mysql-router
mv README.txt mysql-router
rm -f ${TARFILE}.tar.gzÂ
rm -f ${TARFILE}.tar.gz.asc
rm -f ${TARFILE}.tar.gz.md5Â
rm -f ${ZIPFILE}
export PATH=/mysql/mysql-router/bin:$PATH
Add this to the .bash_profile for the root userOn the target client (as 'root')...
mkdir /mysql/myrouter
groupadd -g 28 -o -r mysqlrouter
useradd -M -N -g mysqlrouter -o -r -d /mysql/myrouter -s /bin/false -c "MySQL Router" -u 28 mysqlrouter
RPM
Using the RPM distribution keeps the installation standard. Recommended for ease of use on MySQL client machines (e.g. application servers) where YUM is not available.YUM
OEL/RHEL/CentOSAssumes commercial yum repository is available
yum install mysql-router-commercial.x86_64
Transaction Summary============================================================================================================================================================Install 1 Package
Total download size: 4.4 MInstalled size: 18 MIs this ok [y/N]: yDownloading Packages:mysql-router-commercial-8.0.40-1.1.el8.x86_64.rpm                                      19 MB/s | 4.4 MB   00:00------------------------------------------------------------------------------------------------------------------------------------------------------------Total                                                            19 MB/s | 4.4 MB   00:00Running transaction checkTransaction check succeeded.Running transaction testTransaction test succeeded.Running transaction  Preparing    :                                                                   1/1  Running scriptlet: mysql-router-commercial-8.0.40-1.1.el8.x86_64                                            1/1  Installing    : mysql-router-commercial-8.0.40-1.1.el8.x86_64                                            1/1  Running scriptlet: mysql-router-commercial-8.0.40-1.1.el8.x86_64                                            1/1  Verifying    : mysql-router-commercial-8.0.40-1.1.el8.x86_64                                            1/1
Installed:Â Â mysql-router-commercial-8.0.40-1.1.el8.x86_64
Complete!
Configure Router
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
mysqlrouter --bootstrap icadmin@node1:3306 --user mysqlrouter
The bootstrap process is a specific way of running MySQL Router, which does not start the usual routing and instead configures the mysqlrouter.conf file based on the metadata. (2)mysqlrouter --bootstrap icadmin@node1:3306 --user mysqlrouter --directory /mysql/myrouter
Use this if you are not using the default directory (i.e. you have installed using TAR rather than RPM or YUM)- Creating account(s) (only those that are needed, if any)- 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 /etc/mysqlrouter/mysqlrouter.conf
Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
# MySQL Router configured for the InnoDB Cluster 'ukoci_myic001'
After this MySQL Router has been started with the generated configuration
    $ /etc/init.d/mysqlrouter restartor    $ systemctl start mysqlrouteror    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
InnoDB Cluster 'ukoci_myic001' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446- Read/Only Connections:Â localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:6448- Read/Only Connections:Â localhost:6449
Configure systemd
NOTE: Skip this for RPM or YUM installs (it will be done automatically)touch /usr/lib/systemd/system/mysqlrouter.service
chmod 644 /usr/lib/systemd/system/mysqlrouter.service
Add the following lines to /usr/lib/systemd/system/mysqlrouter.service...
[Unit]
Description=MySQL Router
After=syslog.target
After=network.target
[Service]
Type=simple
User=mysqlrouter
Group=mysqlrouter
PIDFile=/mysql/myrouter/mysqlrouter.pid
ExecStart=/mysql/mysql-router/bin/mysqlrouter -c /mysql/myrouter/mysqlrouter.conf
Restart=on-failure
PrivateTmp=true
[Install]
WantedBy=multi-user.target
Test
systemctl enable mysqlrouter
systemctl start mysqlrouter
systemctl status mysqlrouter
systemctl restart mysqlrouter
For the RPM/YUM install the auto generated mysqlrouter.conf looks like this..
[Unit]Description=MySQL RouterAfter=network-online.targetWants=network-online.targetAfter=syslog.target
[Service]Type=notifyUser=mysqlrouterGroup=mysqlrouter
# Start main serviceExecStart=/usr/bin/mysqlrouter
# Sets open_files_limitLimitNOFILE = 10000
Restart=on-failure
PrivateTmp=true
[Install]WantedBy=multi-user.target
Configure logrotate
Paste following into /etc/logrotate.d/mysqlrouter...
/mysql/myrouter/log/*log {
# create 600 mysqlrouter mysqlrouter
notifempty
weekly
rotate 5
missingok
compress
}
For all files ending with log...
Create new files with 600 permissions
Don't rotate if log file is empty
Rotate weekly
Maximum of 5 versions of a file
If the logfile is missing, ignore and continue
Compress old versions with gzip
Install (Windows)
Check
mysqlsh
\c root@node1:3306
var cluster = dba.getCluster('myCluster');
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')
Start/Stop
With systemd...
systemctl start mysqlrouter
systemctl restart mysqlrouter
systemctl stop mysqlrouter
Without systemd...
/mysql/myrouter/start.sh
/mysql/myrouter/stop.sh
Bibliography & References
(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.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)