MySQL Router Install (Linux/TAR)

Databases

MySQL

MySQL High Availability

MySQL Router

NOTE: The recommended way to install MySQL Router on Linux is to use a YUM/DNF, APT, or RPM install as they automatically create users and groups and configure systemd and logrotate. The TAR method below can be useful for Oracle Enterprise MySQL installs where MySQL components need to be installed on a separate filesystem.

MSR Install (Linux)

Oracle Enterprise MySQL

Bibliography & References

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=6447/tcp --permanent

firewall-cmd --zone=public --add-port=64470/tcp --permanent

firewall-cmd --zone=public --ad-port=6450/tcp --permanent

firewall-cmd --reload

firewall-cmd --list-port

Linux Firewall

6446 RW
6447 RO
64460 X RW
64470 X RO
6450 REST API

SELinux

Linux platforms only

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

semanage port -a -t mysqld_port_t -p tcp 6450

Disable SELinux

Assuming SELinux is currently set to 'enforcing'...

setenforce 0

getenforce

sed -i 's/SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config

Install

edelivery.oracle.com

dev.mysql.com/downloads

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 user

8.0.25 - V1009201-01.zip8.0.26 (OEL7)ZIPFILE=V1010813-01.zipTARFILE=mysql-router-commercial-8.0.26-el7-x86_64

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

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.

RPM OEL78.0.26 - V1010808-01.zip

edelivery.oracle.com

dev.mysql.com/downloads

YUM/DNF

OEL/RHEL/CentOS

MySQL YUM Repositories

Oracle Enterprise MySQL

Assumes commercial yum repository is available

yum install mysql-router-commercial.x86_64

Last metadata expiration check: 1:17:38 ago on Tue 15 Oct 2024 03:51:26 PM BST.Dependencies resolved.============================================================================================================================================================ Package Architecture Version Repository Size============================================================================================================================================================Installing: mysql-router-commercial x86_64 8.0.40-1.1.el8 mysql-80-Local 4.4 M
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!

Percona MySQL Router

dnf install percona-mysql-router --enablerepo=Percona-84-Local

Configure Router

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)

Please enter MySQL password for icadmin:# Bootstrapping system MySQL Router 8.0.40 (MySQL Enterprise - Commercial) instance...
- 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 '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 '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

For the RPM/YUM install the auto generated mysqlrouter.conf looks like this..

# Copyright (c) 2020, 2024, Oracle and/or its affiliates.##################### The lines above are intentionally left blank
[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 systemd

UNIX onlyNOTE: Skip this for RPM or YUM/DNF 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

systemctl stop mysqlrouter

Configure logrotate

Linux/UNIX only

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

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.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)