1.5 Application Considerations

MySQL Router usage does not require specific libraries or interfaces. Aside from managing the MySQL Router instance, write your application as if MySQL Router was a typical MySQL instance.

The only difference when using MySQL Router is how you make connections to the MySQL server. Applications using a single MySQL connection at startup that does not test for connection errors must be updated. This is because MySQL Router redirects connections when the connection is attempted and does not read packets or perform an analysis. If a MySQL server fails, Router returns the connection error to the application.

For these reasons, the application should be written to test for connection errors and, if encountered, retry the connection. If this technique or one similar is employed in your application then using MySQL Router will not require any extra effort.

The following gives a better sense of why you may want to use MySQL Router and looks into how it is used from an application's point of view.

Scenarios

There are several possible scenarios for MySQL Router, including:

• As a developer, I want my application to connect to a service so it gets a connection to, by default, the current primary of a group replication cluster.

• As an administrator, I want to set up multiple services so MySQL Router listens on a different port for each highly available replica set.

• As an administrator, I want to be able to run a connection routing service on port 3306 so it is more transparent to a user or application.

• As an administrator, I want to configure a mode for each connection routing service so I can specify whether a primary or secondary is returned.

Workflow with MySQL Router

The workflow for using MySQL Router is as follows:

1. MySQL Client or Connector connects to MySQL Router to, for example, port 6446.

2. Router checks for an available MySQL server.

3. Router opens a connection to a suitable MySQL server.

4. Router forwards packets back and forth, between the application and the MySQL server

5. Router disconnects the application if the connected MySQL server fails. The application can then retry connecting to Router, and Router then chooses a different and available MySQL server.

Connections using MySQL Router

An application connects to MySQL Router, and Router connects the application to an available MySQL server.

This example demonstrates that a connection transparently connects to one of the InnoDB Cluster instances. Because this example uses a sandboxed InnoDB Cluster where all instances run on the same host, we check the port status variable to see which MySQL instance is connected.

Make a connection to MySQL Router using the MySQL client, for example:

$> mysql -u root -h 127.0.0.1 -P 6446 -p

These port numbers depend on your configuration, but compare ports in this example:

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3310 |
+--------+
1 row in set (0.00 sec)

To summarize, the client (application) connected to port 6446 but is connected to a MySQL instance on port 3310.

Recommendations

The following are recommendations for using MySQL Router.

• Install and run MySQL Router on the same host as the application. For a list of reasons, see Chapter 3, Deploying MySQL Router.

• Bind Router to localhost using bind_port = 127.0.0.1:<port> in the configuration file. Alternatively, on Linux, disable TCP connections (see --conf-skip-tcp) and limit this to only using Unix socket connections (see --conf-use-sockets).