In previous previous article we did MySQL InnoDB Cluster Setup. In this article, We are going to perform configuration of MySQL Router 8.0 on Ubuntu 18.04/16.04 and any other cloud platform like EC2, Azure VM , Compute Engine,..etc.

Introduction

MySQL Router is tiny middleware that provides routing between application and back-end MySQL Servers. It provides high availability and scalability by routing database traffic to connected back-end MySQL Servers.

MySQL Router 8.0 is strongly recommended for use with MySQL 5.7 and 8.0.

Features of MySQL Router 8.0

• The first-available, next-available , round-robin, and  round-robin-with-fallback values added in  routing_strategy configuration.
• The –ssl-key  and –ssl-cert  optional bootstrap command-line options are added.
• The  connect_timeout and read_timeout metadata configuration file options are added in [DEFAULT] namespace.
• We can add more the one writable primary members while configuring bootstraping.
• Maximum number of concurrent client connections are added from 500 to 5000.
• For more information please go through this link,

Step 1: Install MySQL Router

On ubuntu, we can download and install .deb package or using APT package manager, use below command to download .deb package. To download manually use this link.

sudo wget https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

Install above downloaded apt repository,

sudo dpkg -i  mysql-apt-config_0.8.13-1_all.deb  

update your APT repository and install MySQL Router.

sudo apt-get update

sudo apt-get install mysql-router 

In RPM and DEBIAN system default configuration file location is /etc/mysqlrouter/mysqlrouter.conf .

Step 2: Deploying /Bootstrapping MySQL Router

MySQL Router can be deployed using bootstrapping on application instance using below command,

sudo mysqlrouter --bootstrap admin@MYSQL-IDC-01:3306 --user=fossadmin

then we have to start MySQL Router using below command,

mysqlrouter &

To check it’s started and listening / accepting connections on port 6446.

netstat -an | grep 6446

Output:

tcp        0      0 0.0.0.0:64460           0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN

Steps 3: Connect to Database Cluster

After successfully configuration of MySQL Router , below command is connect to MySQL Servers/Clusters from application instance ,

mysql -h 127.0.0.1 --port 6446 -u fosstechnix -p

Step 4: Start, Restart and Enable MySQL Router Service

Below are commands to start,restart,enable MySQL Router using command line.

sudo systemctl start mysqlrouter.service

sudo systemctl restart mysqlrouter.service

sudo systemctl enable mysqlrouter.service 

Step 5: How to uninstall MySQL Router completely

To uninstall debian package , use below command.

sudo dpkg -r mysql-router

use below command to remove data directory file,

sudo dpkg --purge mysql-router

To remove apt package use below commands,

sudo apt-get remove mysql-router

sudo apt-get purge mysql-router

Above commands does not remove configuration files, to remove them use below commands,

sudo rm -rf /etc/init.d/mysqlrouter 

sudo rm -rf /etc/mysqlrouter/mysqlrouter.conf 

sudo rm -rf /etc/apparmor.d/usr.sbin.mysqlrouter 

sudo rm -rf /var/lib/mysqlrouter/

sudo rm -rf /usr/bin/mysqlrouter

sudo rm -rf /etc/apparmor.d/usr.bin.mysqlrouter

sudo rm -rf /etc/apparmor.d/disable/usr.bin.mysqlrouter

sudo rm -rf /etc/mysqlrouter

sudo apt autoremove

Step 6: Troubleshooting

Error: Keyring file (/var/lib/mysqlrouter/keyring) has file permissions that are not strict enough

(only RW for file’s owner is allowed).

Error: Decryption failed

If you are getting above errors while re-configuring , Remove the below config files and bootstrap again.

sudo rm -rf /var/lib/mysqlrouter/

sudo rm -rf /etc/apparmor.d/local/usr.bin.mysqlrouter

sudo rm -rf /etc/apparmor.d/usr.bin.mysqlrouter

sudo rm -rf /etc/apparmor.d/usr.bin.mysqlrouter

once removed above config file and reboot the system.

sudo reboot

Bootstrap MySQL Router again.

sudo mysqlrouter --bootstrap fosstechnix@MYSQL-IDC-01:3306 --user=fossadmin 

mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

OR

mysqlrouter &

Output:

Loading all plugins.

   plugin 'logger:' loading

   plugin 'metadata_cache:FOSSTCluster' loading

   plugin 'routing: FOSSTCluster_default_ro' loading

   plugin 'routing: FOSSTCluster_default_rw' loading

   plugin 'routing: FOSSTCluster_default_x_ro' loading

   plugin 'routing: FOSSTCluster_default_x_rw' loading

 Initializing all plugins.

   plugin 'logger' initializing

 logging facility initialized, switching logging to loggers specified in configuration

After entering above commands and you are able to see like above output, hit Enter key and check if mysql router is listening using port 6446.

netstat -an | grep 6446

Output:

tcp        0      0 0.0.0.0:64460           0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN

Conslusion:

If you liked this post about How to Install MySQL Router on Ubuntu 18.04/16.04 LTS, please share it with your friends on the social networks using the buttons below or simply leave a comment in the comments section . Thanks.

Related Articles

How To Install MySQL 8.0 on Ubuntu 18.04