InnoDB Cluster Create

The same version of MySQL EE should be installed on all nodes that will be in the cluster.
All nodes need MySQL Shell installed.
Only one node should have any non-default databases installed before we start. We will refer to this as Node1
Ensure all nodes can talk to each other on port 3306 and 33061

Preparation

Additional Firewall Configuration

firewall-cmd --permanent --zone=public --add-port=33061/tcp
firewall-cmd --reload

Configure MySQL to be cluster ready...

Perform the following on all nodes that will form the cluster...

mysqlsh

\c root@localhost

dba.configureLocalInstance()

  • 2 (Create a new admin account for InnoDB cluster with minimal required grants)

  • icadmin (name of cluster admin)

  • Enter a password and confirm (the password must be the same on all servers)

  • perform the required configuration changes? yes

  • restart the instance after configuring it? yes

+----------------------------------------+---------------+----------------+--------------------------------------------------+

| Variable | Current Value | Required Value | Note |

+----------------------------------------+---------------+----------------+--------------------------------------------------+

| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |

| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |

| gtid_mode | OFF | ON | Update read-only variable and restart the server |

| server_id | 1 | <unique ID> | Update read-only variable and restart the server |

| slave_parallel_type | DATABASE | LOGICAL_CLOCK | Update the server variable |

| slave_preserve_commit_order | OFF | ON | Update the server variable |

+----------------------------------------+---------------+----------------+--------------------------------------------------+

Create Cluster

We need to know the IP addresses/CIDR blocks for each of our nodes.

In OCI with three Availability Groups, we could use the CIDR blocks we have allocated for each Availability Group. It is possible to restrict to individual IP addresses if necessary, but note that this causes extra steps if ever we want to add more nodes from an existing Availability Group.

On Node1...

mysqlsh

\c icadmin@node1

var cluster = dba.createCluster('myCluster',{ipAllowlist:"10.0.0.0/24,10.0.1.0/24,10.0.2.0/24"})

cluster.status()

cluster.addInstance('icadmin@node2:3306',{ipAllowlist:"10.0.0.0/24,10.0.1.0/24,10.0.2.0/24"})

  • (Clone)

cluster.addInstance('icadmin@node3:3306',{ipAllowlist:"10.0.0.0/24,10.0.1.0/24,10.0.2.0/24"})

  • (Clone)

cluster.status()

Test Cluster

mysqlsh

\c icadmin@node1

var cluster = dba.getCluster('myCluster');

cluster.status()

cluster.switchToMultiPrimaryMode()

cluster.status()

cluster.switchToSinglePrimaryMode()

cluster.status()

cluster.setPrimaryInstance('node3')

cluster.status()


Connect

Reset "cluster" variable

One node should be R/W others should be R/O

To switch to Multi-Mater mode (not recommended for prod)

All nodes should be R/W

Switch back to single R/W

One node should be R/W others should be R/O

Make node3 the Primary instance

Node3 should be R/W others should be R/O

Using mysqlsh or the standard mysql (or any other) client, check that all databases contain the same databases and the same data.

MySQL Router

Once you have an InnoDB Cluster, you should install MySQL Router on your application servers.