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)

See later for notes on alternative approaches.

TAR

Using the TAR distribution gives the most flexibility (e.g. I like to install under /mysql rather than the default location).

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 user
8.0.25 - V1009201-01.zip8.0.26 (OEL7)ZIPFILE=V1010813-01.zipTARFILE=mysql-router-commercial-8.0.26-el7-x86_64

RPM

Using the RPM distribution keeps the installation standard. Recommended for ease of use on MySQL client machines (e.g. application servers)
RPM OEL78.0.26 - V1010808-01.zip

Configure Router

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

On 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

mysqlrouter --bootstrap icadmin@node1:3306 --user mysqlrouter --directory /mysql/myrouter

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)

Please enter MySQL password for icadmin:

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


- 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 /mysql/myrouter/mysqlrouter.conf


# MySQL Router configured for the InnoDB Cluster 'myCluster'


After this MySQL Router has been started with the generated configuration


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


the cluster 'myCluster' 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

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

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)

MSI Installer8.0.25 - V1009210.01.zip8.0.27 - V1018010-01.zip

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

Alternative Install

YUM - OEL/RHEL/CentOS

Assumes commercial yum repository is available

yum install mysql-router-commercial-*