MySQL Router 8.0-MySQL Router Configuration on Ubuntu 18.04/16.04

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 [email protected]:3306 --user=fossadmin

Here, [email protected]:3306 MySQL InnoDB Cluster Master Node

         –user=fossadmin username of application VM where we are installing MySQL Router

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
MySQL Router 8.0-MySQL Router Configuration on Ubuntu 18.04/16.04 1

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

Error: Could not open /var/run/mysqlrouter/state.json.tmp for writing: Permission denied

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 [email protected]: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

if you are seeing output like above, then you reconfigured successfully.

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

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

We promise not to spam you, and we don't usually send more than one email a week.

 

You have Successfully Subscribed!

36 Shares
Share via
Copy link