MySQL InnoDB Cluster Tutorial 2 ( Integrating with MySQL router )

From my last blog , I have explained the details about the configuration of InnoDB Cluster ( Group Replication + MySQL shell ) . You can find the link below .

MySQL InnoDB Cluster Tutorial 1 ( Group Replication + MySQL Shell )

In this blog, I am going to explain How to integrate the MySQL router with the existing cluster setup .

As I explained in Tutorial 1 , I already have configured the cluster setup with MySQL shell and group replication,

MySQL 192.168.33.11:3306 ssl JS > cluster.getName();
first_InnoDB_cluster

MySQL 192.168.33.11:3306 ssl JS > \sql
Switching to SQL mode… Commands end with ;

MySQL 192.168.33.11:3306 ssl SQL > select channel_name,member_host,member_state,member_role,member_version from performance_schema.replication_group_members\G
* 1. row *
channel_name: group_replication_applier
member_host: sakthilabs11
member_state: ONLINE
member_role: PRIMARY
member_version: 8.0.18
* 2. row *
channel_name: group_replication_applier
member_host: sakthilabs12
member_state: ONLINE
member_role: SECONDARY
member_version: 8.0.18
* 3. row *
channel_name: group_replication_applier
member_host: sakthilabs13
member_state: ONLINE
member_role: SECONDARY
member_version: 8.0.18
3 rows in set (0.0070 sec)

Let’s jump into the topic. First step, need to install the MySQL router ,

yum install mysql-router-community.x86_64

I have installed MySQL router community edition .

# yum list installed | grep -i router
mysql-router-community.x86_64 8.0.18-1.el7 @mysql-tools-community

Second step, need to create the dedicated directory for the MySQL router operation . After this , need to run the MySQL router with bootstrap option .

# mkdir -p /root/mysqlrouter

• –bootstrap : bootstrap option will helps to automatically configure the router operation with the MySQL InnoDB cluster

The below files will be created after bootstrap the router .

pwd : /root/mysqlrouterdrwx——. 2 root root 6 Dec 30 15:07 run
-rw——-. 1 root root 88 Dec 30 15:07 mysqlrouter.key
drwx——. 2 root root 29 Dec 30 15:07 log
-rwx——. 1 root root 277 Dec 30 15:07 start.sh
-rw——-. 1 root root 1.4K Dec 30 15:07 mysqlrouter.conf
drwx——. 2 root root 39 Dec 30 15:07 data
-rwx——. 1 root root 161 Dec 30 15:07 stop.sh

mysqlrouter.conf will contains the Configuration options , By triggering the start.sh script we can start the MySQL router daemon .

# ./start.sh
PID 14791 written to ‘/root/mysqlrouter/mysqlrouter.pid’
logging facility initialized, switching logging to loggers specified in configuration

# ps -ef | grep -i mysqlrou
root 14791 1 21 15:22 pts/0 00:00:04 /bin/mysqlrouter -c /root/mysqlrouter/mysqlrouter.conf
root 14801 14636 0 15:23 pts/0 00:00:00 grep –color=auto -i mysqlrou

# netstat -tulnp | grep -i mysqlrouter
tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 14791/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 14791/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 14791/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 14791/mysqlrouter

Alright, we have integrated the MySQL router with Cluster . Now we can test this with read and read/write connections .

For read / write connections , ( port : 6446 )

# mysql -P6446 -uInnotest -p’xxxxxxxxxx’ -h127.0.0.1 -e “create database test_write”

# mysql -P6446 -uInnotest -p’xxxxxxxxxxx’ -h127.0.0.1 -e “use sakthi ; select database()”
mysql: [Warning] Using a password on the command line interface can be insecure.
+————+
| database() |
+————+
| sakthi |
+————+

I can perform, both read and writes with pot 6446 .

For only read connections , ( port : 6447 )

# mysql -P6447 -uInnotest -p’xxxxxxxx’ -h127.0.0.1 -e “use sakthi ; select database()”
+————+
| database() |
+————+
| sakthi |
+————+

# mysql -P6447 -uInnotest -p’xxxxxxxxxx’ -h127.0.0.1 -e “create database test_write”
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1290 (HY000) at line 1: The MySQL server is running with the –super-read-only option so it cannot execute this statement

We can perform only reads with port 6447 . It illustrate the port 6447 only connects the reader nodes not master .

I hope this blog will helps someone who has started to learn the MySQL InnoDB cluster .

Thanks !!