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
- How To Bootstrap MySQL Router For InnoDB Cluster
- Changing InnoDB Cluster Topology in MySQL
- How to Setup MySql InnoDB Cluster
- Adopting a Group Replication Deployment into InnoDB Cluster
- Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle
