From MySQL Group Replication to MySQL InnoDB Cluster

I wanted to be brave and I installed MySQL Group Replication manually…. it was painful !

Then I realized that managing those servers and especially deal with MySQL Routers was even more painful !

What are my options now ? Is there a solution or do I need to restart from scratch ?

Asking the answer is already answering it… and once again MySQL Shell at the rescue.

MySQL Group Replication

I’ve configured everything manually. I also loaded group_replication and clone plugins and finally after having bootstrapped my Group here is what I have:

mysql> select member_host, member_port port, member_state state, 
       member_role role, member_version version 
       from performance_schema.replication_group_members;
+-------------+------+--------+-----------+---------+
| member_host | port | state  | role      | version |
+-------------+------+--------+-----------+---------+
| mysql2      | 3306 | ONLINE | SECONDARY |  8.0.21 |
| mysql3      | 3306 | ONLINE | SECONDARY |  8.0.21 |
| mysql1      | 3306 | ONLINE | PRIMARY   |  8.0.21 |
+-------------+------+--------+-----------+---------+

Let’s install MySQL Shell:

[root@mysql1 ~]# dnf install mysql-shell -y

MySQL InnoDB Cluster

Now we can start MySQL Shell and create our InnoDB Cluster importing our existing Group !

If we don’t have a dedicated user to manage the cluster and rootis only able to connect to localhost (which is always better), we need to create a user with the required privileges. This must be done on the Primary member:

CREATE USER 'clusteradmin' identified by 'MyPassw0rd';

GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SELECT, SUPER, REPLICATION SLAVE, 
      REPLICATION CLIENT, REPLICATION_APPLIER, CREATE USER, SYSTEM_VARIABLES_ADMIN, 
      PERSIST_RO_VARIABLES_ADMIN, BACKUP_ADMIN, CLONE_ADMIN, EXECUTE 
      ON . TO clusteradmin@'%' WITH GRANT OPTION;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, 
      CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES,
      SHOW VIEW, TRIGGER, UPDATE 
      ON mysql_innodb_cluster_metadata.* TO clusteradmin@'%';

GRANT INSERT, UPDATE, DELETE ON mysql.* TO clusteradmin@'%';

Now we can connect to our Primary member with the new credentials:

As you can see I connected to the name (or IP), mysql1, that will be used through the cluster and not localhost,

It’s time to migrate our Group into InnoDB Cluster:

The command is dba.createCluster('name of cluster', {adoptFromGR: true}).

The content of this option in the inline help (\h dba.createCluster) is:

adoptFromGR allows creating an InnoDB cluster from an existing unmanaged
Group Replication setup, enabling use of MySQL Router and the shell
AdminAPI for managing it.

Now we have a full working cluster ready to be used with MySQL Router and managed by MySQL Shell:

MySQL Router

And to bootstrap MySQL Router:

mysqlrouter --bootstrap clusteradmin@mysql1 -u mysqlrouter --conf-use-gr-notifications

Example:

And now that we are fully using MySQL InnoDB Cluster 8.0.21, we can also get the information related to the Routers:

Conclusion

You don’t have anymore excuses to not use MySQL InnoDB Cluster, and I highly recommend everybody using native Group Replication for a single cluster topology to migrate to MySQL InnoDB Cluster. This will make your life much easier !