How To Bootstrap MySQL Router For InnoDB Cluster

April 14, 2021
()

How To Bootstrap MySQL Router For InnoDB Cluster

Bootstrap MySQL Router against an InnoDB Cluster to automatically configure the routing of incoming client connections to mysql instances. To bootstrap MySQL Router at the command-line, pass in the –bootstrap option when you start the mysqlrouter command, and it retrieves the topology information from the metadata and configures routing connections to the server instances.

MySQL Router automatically redirects client connections to the instances based on the incoming port, for example, 6646 is used by default for read-write connections using classic MySQL protocol. In the event of a topology change, for example, due to an unexpected failure of an instance, MySQL Router detects the change and adjusts the routing to the remaining instances automatically. This removes the need for client applications to handle failover, or to be aware of the underlying topology.


In the previous blog, we saw How to Configure InnoDB Cluster Click here to read more. We will use the same configuration to install and bootstrap mysql router.


Step 1. Install mysql router: Use the yum system command to install mysql router. For production deployment, it recommends deploying one MySQL Router instance to each machine used to host applications. But for demo purpose, we will install its in the same machine of Member-1 (test-machine01) mysql instance.

[root@test-machine01 tmp]# yum install mysql-router -y
--> Running transaction check
---> Package mysql-router-community.x86_64 0:8.0.22-1.el7 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
==============================================================================================================================================================================================
 Package                                             Arch                                Version                                     Repository                                          Size
==============================================================================================================================================================================================
Installing:
 mysql-router-community                              x86_64                              8.0.22-1.el7                                mysql-tools-community                               31 M
 
Transaction Summary
==============================================================================================================================================================================================
Install  1 Package
Installed:
  mysql-router-community.x86_64 0:8.0.22-1.el7
 
Complete!


Step 2. Deploying MySQL Router: Use below command to bootstrap mysql router against Cluster : innodbclustertest. Once the configuration is done mysql router will provide a port for connection. Please note we are using –force option as the same router instance was used in the InnoDB ReplicaSet demo.

[root@test-machine01 ~]#
[root@test-machine01 ~]# mysqlrouter --bootstrap root@test-machine01:3306 --user mysqlrouter --force
Please enter MySQL password for root:
# Bootstrapping system MySQL Router instance...

Executing statements failed with: 'Error executing MySQL query "INSERT INTO mysql_innodb_cluster_metadata.v2_routers        (address, product_name, router_name) VALUES ('test-machine01', 'MySQL Router', 'system')": The MySQL server is running with the --super-read-only option so it cannot execute this statement (1290)' (1290), trying to connect to another node
Fetching Cluster Members
disconnecting from mysql-server
trying to connect to mysql-server at test-machine02:3306
- Creating account(s) (only those that are needed, if any)
- Using existing certificates from the '/var/lib/mysqlrouter' directory
- 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'

Existing dynamic state backed up to '/var/lib/mysqlrouter/state.json.bak'

# MySQL Router configured for the InnoDB Cluster 'innodbclustertest'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'innodbclustertest' 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:64460
- Read/Only Connections:  localhost:64470
[root@test-machine01 mysqlrouter]#


Step 3. Start Mysql Router: Use systemctl system command to start mysql router. You can monitor mysql router logfile in the directory: /var/log/mysqlrouter/mysqlrouter.log.

[root@test-machine01 mysqlrouter]#
[root@test-machine01 mysqlrouter]# systemctl status mysqlrouter
â mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@test-machine01 mysqlrouter]#
[root@test-machine01 mysqlrouter]# systemctl start mysqlrouter
[root@test-machine01 mysqlrouter]#
[root@test-machine01 mysqlrouter]# systemctl status mysqlrouter
â mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-04-12 08:13:06 +03; 4s ago
 Main PID: 13345 (mysqlrouter)
   CGroup: /system.slice/mysqlrouter.service
           ââ13345 /usr/bin/mysqlrouter

Apr 12 08:13:05 test-machine01 systemd[1]: Starting MySQL Router...
Apr 12 08:13:06 test-machine01 mysqlrouter[13345]: logging facility initialized, switching logging to loggers specified in configuration
Apr 12 08:13:06 test-machine01 systemd[1]: Started MySQL Router.
[root@test-machine01 mysqlrouter]#


Step 4. Verify Mysql Router metadata in InnoDB Cluster: Login to mysqlsh using root user and use the function listRouters() to retrieve mysql router information.

[root@test-machine01 ~]# mysqlsh root@localhost:3306
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 147
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > var cluster=dba.getCluster();
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > cluster.listRouters();
{
    "clusterName": "innodbclustertest",
    "routers": {
        "test-machine01::system": {
            "hostname": "test-machine01",
            "lastCheckIn": "2021-04-12 08:14:04",
            "roPort": 6447,
            "roXPort": 64470,
            "rwPort": 6446,
            "rwXPort": 64460,
            "version": "8.0.22"
        }
    }
}
 MySQL  localhost:3306 ssl  JS >


Step 5. Verify mysql router Port at OS Level: Use OS command netstat to verify mysqlrouter process is listening on all ports.

[root@test-machine01 mysqlrouter]#  netstat -ntlpue |grep router
tcp        0      0 0.0.0.0:64460           0.0.0.0:*               LISTEN      995        1354977    13345/mysqlrouter
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      995        1354975    13345/mysqlrouter
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      995        1354974    13345/mysqlrouter
tcp        0      0 0.0.0.0:64470           0.0.0.0:*               LISTEN      995        1354976    13345/mysqlrouter
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      995        1354966    13345/mysqlrouter
[root@test-machine01 mysqlrouter]#


Step 6. Lets Test Mysql Router Automatic Routing: We will use mysql workbench as a client application for testing purposes. Connect to mysql router (test-machine01) to port 6446 for read/write & 6447 for read-only.

Step 7. Open Port on Server: Above issue occur as port 6446 was not opened. We are using Oracle Linux 7.9.

[root@test-machine01 mysqlrouter]#
[root@test-machine01 mysqlrouter]# firewall-cmd --permanent --add-port=6446/tcp
success
[root@test-machine01 mysqlrouter]#
[root@test-machine01 mysqlrouter]# firewall-cmd --permanent --add-port=6447/tcp
success
[root@test-machine01 mysqlrouter]# firewall-cmd --reload
success
[root@test-machine01 mysqlrouter]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens33
  sources:
  services: dhcpv6-client mysql ssh
  ports: 3307/tcp 3306/tcp 33061/tcp 24901/tcp 33071/tcp 6446/tcp 6447/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

[root@test-machine01 mysqlrouter]#


Try to connect Workbench again and it will connect to mysql instance test-machine01:3306 as this is our Primary instance with Read/Write.


Step 8. Let Stop mysql instance test-machine01:3306: Once test-machine01:3306 mysql instance stopped InnoDB Cluster will choose another Primary Instance from the available member. In our case, InnoDB Cluster chooses test-machine02:3306 as a new Primary.

[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl stop mysqld@inst1

[root@test-machine01 ~]#


<<<<<<<<<<<<<<<<<<<<<<<<<<< Snippet from mysqllog >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<< Snippet from mysqllog >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2021-04-12T08:21:34.140402Z 0 [Warning] [MY-011494] [Repl] Plugin group_replication reported: 'Member with address test-machine01:3306 is reachable again.'
2021-04-12T08:21:34.140511Z 0 [Warning] [MY-011494] [Repl] Plugin group_replication reported: 'Member with address test-machine01:3307 is reachable again.'
2021-04-12T08:21:34.140615Z 0 [Warning] [MY-011498] [Repl] Plugin group_replication reported: 'The member has resumed contact with a majority of the members in the group. Regular operation is restored and transactions are unblocked.'
2021-04-12T08:22:13.249963Z 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: test-machine01:3306'
2021-04-12T08:22:13.250002Z 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address test-machine01:3306 left the group. Electing new Primary.'
2021-04-12T08:22:14.251312Z 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address test-machine02:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
2021-04-12T08:22:14.251558Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to test-machine02:3306, test-machine01:3307 on view 16181457796426935:12.'
2021-04-12T08:22:14.252469Z 106 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2021-04-12T08:22:14.265484Z 106 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

<<<<<<<<<<<<<<<<<<<<<<<<<<< Snippet from mysqllog >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<< Snippet from mysqllog >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

[root@test-machine01 ~]# mysqlsh root@localhost:3307
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3307'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 786
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3307 ssl  JS >
 MySQL  localhost:3307 ssl  JS > var cluster=dba.getCluster();
 MySQL  localhost:3307 ssl  JS > cluster.status();
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-machine02:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "mode": "n/a",
                "readReplicas": {},
                "role": "HA",
                "shellConnectError": "MySQL Error 2003 (HY000): Can't connect to MySQL server on 'test-machine01' (111)",
                "status": "(MISSING)"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "test-machine02:3306"
}
 MySQL  localhost:3307 ssl  JS >


Step 9. Connect to Workbench: The existing connection will reset. After you login again to mysql workbench it will connect to test-machine02:3306 automatically without any client-side connection properties change.


Step 10 . Let start stopped instance test-machine01:3306: Once test-machine01:3306 is available it will be added back to InnoDB cluster.

[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl start mysqld@inst1
[root@test-machine01 ~]#

 MySQL  localhost:3307 ssl  JS > cluster.status();
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-machine02:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "test-machine02:3306"
}
 MySQL  localhost:3307 ssl  JS >


Reference : Bootstrapping MySQL Router: https://dev.mysql.com/doc/mysql-shell/8.0/en/admin-api-bootstrapping-router.html

 

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

Related Articles

, ,

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *