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 userRPM
Using the RPM distribution keeps the installation standard. Recommended for ease of use on MySQL client machines (e.g. application servers)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)
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-*
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.html
(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)