Wednesday, July 31, 2019

Using MySQL Router on Windows

Last time we used the New MySQL Shell to set up a sandbox instance of InnoDB Cluster in Six Steps. Now to make that cluster function we need to start up MySQL Router to proxy connections. And since we do not show how to do things with Windows, we will configure Router on that Operating System.

Cleaning Up From Last Time

If you followed the instructions from last time you may find you InnoDB Cluster non functional.  If you have rebooted then you will find that the component pieces of the cluster have not been started.  To clean things up you will have to do the following:


  • dba.killSandboxInstance(3310)
  • dba.deleteSandboxInstance(3310)
  • dba.deleteSandboxInstance(3320)
  • dba.deleteSandboxInstance(3310)

Then recreate the sandbox instance using the instructions from the last post.

Lets Get Ready To Route


With Windows, we will  use the MySQL Installer to configure Router.  If Router is not installed you can also use the MySQL Installer to add the program. Click on 'Reconfigure' to set up MySQL Router.

MySQL Installer - Note that Router is installed on this system. Click on 'Reconfigure' to set up MySQL Router

We are now ready to explore the configuration options for MySQL Router.

We need to specify the host which, in the case of our sandbox, is 'localhost', and provide the account and password to test the connection.

The host needs to be specified and in our case it is the localhost. You will want to test the connection after supplying the account with password and port before selecting the 'Test' button. And remember we set the port when we established the sandbox instance with the
dba.deploySandboxInstance(3310) command.

We need to supply the host, port, account & password before testing the connection


If you want to change the proxy ports at this time you certainly can.

We can also change the ports for the proxy at this time too!

 Born To Run

So we have MySQL Router configured and it is time to get it running.  Time to select the 'Execute' button in the lower right corner.


When you are ready to start MySQL Router select 'Execute'


The MySQL Router program will start.

MySQL Router starts up and since we are on Windows, it registers as a Windows Service,
Log filed are available for those who want the details.

The first part of the log details the settings of MySQL Router

And the second half of the log shows us the proxy ports and that MySQL Router is a Windows Service

Special Account


Did you see it? The special account that appears like a hidden Easter Egg?  It was in the first part of the log. Router set up an account  mysql_router1_5vbo3umtvi6y@'%' for cluster management. It also created a configuration file for later user.

More on this in a future post.


But Is It Working?!?



Start another MySQL shell and connect to the read only proxy port.

 \c root@localhost:6447
Creating a session to 'root@localhost:6447'
Please provide the password for 'root@localhost:6447': ******
Save password for 'root@localhost:6447'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 24
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
JS > \sql
Switching to SQL mode... Commands end with ;
SQL > select @@port;
+--------+
| @@port |
+--------+
|   3330 |
+--------+
1 row in set (0.0002 sec)

So we are talking to the instance at port 3330!  Woo-hoo.  Without a load connecting again will probably not switch us to the other server at 3320 but we will try.  And so we start another shell.  i will confess that I kept seeing good ol' 3330 after several attempts and got luck when I tried the read only port for the X Protocol.

SQL > select @@port;
+--------+
| @@port |
+--------+
|   3330 |
+--------+
1 row in set (0.0003 sec)
SQL > \c root@localhost:6449
Creating a session to 'root@localhost:6449'
Please provide the password for 'root@localhost:6449': ******
Save password for 'root@localhost:6449'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 39 (X protocol)
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 SQL > select @@port;
+--------+
| @@port |
+--------+
|   3320 |
+--------+
1 row in set (0.0004 sec)

Conclusion

From the last blog we know we can set up an sandbox InnoDB cluster with six commands. And this time we were able to set up MySQL Router in about three minutes.   So ten minutes to a highly available MySQL InnoDB Cluster.  This many not impress your but for someone like me who started with the basic asynchronous replication with early version of MySQL this is amazing. 



7 comments: