Hey folks, today I would like to show how easy is to do a HA cluster with MySQL InnoDB Cluster.

First a few things we must know: You’ll need at least 3 servers (for database) to do the job. We’ll need MySQL Shell on every server. Every node needs a two-way connection with each node (he need to be able to connect to the other node, and other nodes needs to be able to connect to the one). You will need a fourth server to work as a router. I’ll assume you already have a well configured MySQL Server on all three servers. Today I’ll be using version 8.

To be easier, every server will have an entry to the others on the hosts file. So let’s begin creating a common user on every server and give privileges enough to do the JOB. Be aware that I’m no focusing on security issues, like allowing the user to access the database from any IP, the best solution would be set the servers IPs.

CREATE USER 'syncron'@'%' IDENTIFIED BY 'Abacaxi22';
GRANT ALL PRIVILEGES ON *.* TO 'syncron'@'%' WITH GRANT OPTION;

1) Creating the cluster

Now let’s fire MySQL Shell on every node and prepare the server to join/create a InnoDB Cluster. If you never used this Shell, give it a try, it’s an awesome tool and it’s really easy to use and learn. We’ll basically use the dba commando for all of our needs today. Run dba.configureInstance() on every node and then, on the “main” server run the following.

You can run the Shell with mysqlch --uri=syncron@mysql1:3306 and voila. You’re on the server 1.

cluster = dba.createCluster('tasks') --Cluster name, take note
dba.getCluster('tasks').status()

After each command, you should see the following results. Look that I’m actually running the command on a server named mysql1.

Look at the status result, and see that after creating the cluster, we already have a node running and he’s a R/W node. And look at the ‘statusText’.. The cluster still not fault tolerant.

2) Adding a node to the cluster

Now let’s connect to the second server. On the second server, instead of connecting to itself, we’ll connect to the mysql1 server, so use the same command as before to spin the Shell. After that, let’s add the second instance to the cluster.

cluster = dba.getCluster('tasks') -- the cluster name, remember?
cluster.addInstance('syncron@mysql2:3306') -- now we use the second server uri

IF everything works as expected you’ll see the following as a result.

You can run the status on the cluster again to see how it’s now. But it still not fault tolerant. Do the same job on the third server (mysql3 on my environment) and THEN you can get the status and see the magic (or at least the status page of it).

Be aware that, the server now IS fault tolerant BUT, can tolerate just one. But take a closer look and see that we have just one node with R/W permission, the other two are R/O… and that’s how it works (in this case at least). How we handle the connections now that we have three different servers running? Easy… use the Mysql Router… Now to the fourth server.

3) The Router

That’s the easy step… just run .\mysqlrouter.exe --bootstrap syncron@mysql1:3306 and you’ll get the config file generated. That’s just the default configuration, take a look at the manual to achieve better results. The router will give you two port to handle the connections, one with R/W permission and the other one with R/O. It’ll handle the “reconnect” for your application if needed.

Easy huh? Go ahead and shut down one instance and look at the cluster status again. Now two extra tips..

• If you get the following when trying to configure or join a cluster, you need to check the grant of the user, here we used the most of it, but you can manage the permissions to achieve a better (and more secure) login.

Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation (MySQL Error 3630)

• If you plan to upgrade your server, do it with caution. You MUST do the R/W node at the END. Before shut down the server run set persist group_replication_start_on_boot=0; and set to true again after the upgrade and then restart it again.

Cheers!