Creating HA using MySQL Router - Example (added in v6.2.0)

important

This section provides an example of deploying MySQL Router with Boomi Cloud API Management - Local Edition. This example is for illustrative purposes only.

Configuration, tuning, and support for MySQL Router, Group Replication, or InnoDB Cluster fall outside the scope of Boomi support. Refer to the official MySQL Community Edition documentation for more information.

MySQL Router

A MySQL Router acts as a MySQL proxy between the client and the MySQL Cluster to manage failover and load balancing among multiple database nodes. The router is aware of your cluster’s topology. Using this information, it can easily switch to the new primary node when there’s a failover event. These changes are transparent to the client application. The client application is always connected to the router instance.

High availability (HA) for MySQL instances can be achieved by setting up an InnoDB Cluster. Internally, your cluster can utilize MySQL’s Group Replication to synchronize data between primary and secondary nodes. If a client connects directly to the primary node, then failover does not happen seamlessly for all types of clients. This behavior mainly depends on the driver used by the client. A driver should support Group Replication (GR). If it does not, you need to look for external solutions. The solution should handle failover seamlessly and be transparent to the client application. This way, the application can continue to work without reconfiguration or restarts.

Creating a Database Cluster

Consider the following example for creating a three-node database cluster and adding the router to which your client application can connect.

Procedure

Create a cluster. Run the following command to set up a cluster.

The following Docker Compose file will create a three-node MySQL cluster and the MySQL router.

services:
  mysql-node1:
    image: mysql:8.4.2
    container_name: mysql-node1
    environment:
      MYSQL_ROOT_PASSWORD: changeme
      MYSQL_DATABASE: masherysolar
      MYSQL_USER: masheryonprem
      MYSQL_PASSWORD: changeme
    ports:
      - "3306:3306"
      - "33061:33061"
    volumes:
      - mysql1_data:/var/lib/mysql
      - ./mysql-config/node1.cnf:/etc/mysql/conf.d/mysql.cnf
    networks:
      - mysql-cluster

  mysql-node2:
    image: mysql:8.4.2
    container_name: mysql-node2
    environment:
      MYSQL_ROOT_PASSWORD: changeme
      MYSQL_DATABASE: masherysolar
      MYSQL_USER: masheryonprem
      MYSQL_PASSWORD: changeme
    ports:
      - "3307:3306"
      - "33062:33061"
    volumes:
      - mysql2_data:/var/lib/mysql
      - ./mysql-config/node2.cnf:/etc/mysql/conf.d/mysql.cnf
    networks:
      - mysql-cluster
    depends_on:
      - mysql-node1

  mysql-node3:
    image: mysql:8.4.2
    container_name: mysql-node3
    environment:
      MYSQL_ROOT_PASSWORD: changeme
      MYSQL_DATABASE: masherysolar
      MYSQL_USER: masheryonprem
      MYSQL_PASSWORD: changeme
    ports:
      - "3308:3306"
      - "33063:33061"
    volumes:
      - mysql3_data:/var/lib/mysql
      - ./mysql-config/node3.cnf:/etc/mysql/conf.d/mysql.cnf
    networks:
      - mysql-cluster
    depends_on:
      - mysql-node1

  mysql-router:
    image: container-registry.oracle.com/mysql/community-router:8.4
    container_name: mysql-router
    environment:
      MYSQL_HOST: mysql-node1
      MYSQL_PORT: 3306
      MYSQL_USER: root
      MYSQL_PASSWORD: changeme

    ports:
      - "6446:6446"  # Primary with automatic failover
    volumes:
      - mysql_router_data:/var/lib/mysqlrouter  # Persist router state and config
    networks:
      - mysql-cluster
    depends_on:
      - mysql-node1
      - mysql-node2
      - mysql-node3
    restart: unless-stopped


volumes:
  mysql1_data:
  mysql2_data:
  mysql3_data:
  mysql_router_data:  # Router state persistence

networks:
  mysql-cluster:
    driver: bridge

Ensure the MySQL configuration file (mysql.cnf) contains the correct values to enable group replication.

# contents of node1.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
gtid-mode=ON
enforce-gtid-consistency=ON
binlog-checksum=NONE
log-slave-updates=ON
mysql_native_password=ON

Initialize Group Replication. Run the following command on each node to enable Group Replication.

These will create a replication user with appropriate privileges and enable the Group Replication plugin. The following is a sample example for initializing Group Replication on node1. After adjusting the local address, the same steps can be followed for other nodes.

RESET BINARY LOGS AND GTIDS;

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION_SLAVE_ADMIN ON *.* TO 'repl'@'%';
GRANT CONNECTION_ADMIN ON *.* TO 'repl'@'%';
GRANT BACKUP_ADMIN ON *.* TO 'repl'@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO 'repl'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' ;

FLUSH PRIVILEGES;

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

SET GLOBAL group_replication_group_name='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET GLOBAL group_replication_local_address='mysql-node1:33061';
SET GLOBAL group_replication_group_seeds='mysql-node1:33061,mysql-node2:33061,mysql-node3:33061';
SET GLOBAL group_replication_single_primary_mode=ON;
SET GLOBAL group_replication_enforce_update_everywhere_checks=OFF;

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

Initialize InnoDB cluster. Run the following command to initialize the InnoDB cluster:

docker exec mysql-node1 mysqlsh --uri root:changeme@localhost:3306 --js -e "var cluster = dba.createCluster('myDockerCluster', {adoptFromGR: true}); cluster.status();"

important
MySQL Router works with an InnoDB cluster. Although Group Replication was set up in the previous step, the cluster needs to be converted to an InnoDB Cluster so the router can query the topology.
The router instance will fail to bootstrap unless the InnoDB Cluster is initialized.

Create a MySQL Router user so that it can query the performance schema.

CREATE USER IF NOT EXISTS 'mysqlrouter'@'%' IDENTIFIED WITH mysql_native_password BY 'routerpass';
GRANT SELECT ON performance_schema.replication_group_members TO 'mysqlrouter'@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO 'mysqlrouter'@'%';
GRANT SELECT ON performance_schema.global_variables TO 'mysqlrouter'@'%';
GRANT SELECT ON mysql.user TO 'mysqlrouter'@'%';
FLUSH PRIVILEGES;

After setting up the user, run the following command to restart the router instance:

docker compose -f docker-compose-mysql-gr.yml restart mysql-router

Query the router instance to verify the status of your cluster. Run the following command:

docker exec mysql-router  mysql -uroot -pchangeme -e "SELECT * FROM performance_schema.replication_group_members;"
mysql: [Warning] Using a password on the command line interface can be insecure.
CHANNEL_NAME    MEMBER_ID       MEMBER_HOST     MEMBER_PORT     MEMBER_STATE    MEMBER_ROLE     MEMBER_VERSION  MEMBER_COMMUNICATION_STACK
group_replication_applier       4bd86d2d-7e8c-11f0-8e68-f2491d511189    225ab2476e6a    3306    ONLINE  PRIMARY 8.4.2   XCom
group_replication_applier       4be76590-7e8c-11f0-b8e4-2eebadb7cb70    0fe53f2d7df8    3306    ONLINE  SECONDARY       8.4.2   XCom
group_replication_applier       4be78207-7e8c-11f0-911c-c25bd746671f    5ef2af52da45    3306    ONLINE  SECONDARY       8.4.2   XCom