My SQL Innodb Cluster Setup

Server List

clota-dbl05 

@@version 8.0.32-commercial

192.168.0.156

My SQL root password Secure@123

-------------------------------------------------------------------

ota-dbl06 <cota-dbl06>

@@version 8.0.33

192.168.0.197

My SQL root password Secure@123

-------------------------------------------------------------------

clota-dbl07

192.168.0.195

@@version @@version 8.0.33

My SQL root password Secure@321

-------------------------------------------------------------------

Step 1: 

Modify /etc/hosts with IP and server_name  on all the nodes...

Like below 

vim /etc/hosts

192.168.0.156   clota-dbl05

192.168.0.197      ota-dbl06

192.168.0.195   clota-dbl07

Open DB Port on all the nodes...

firewall-cmd --add-port={3306}/tcp --permanent
firewall-cmd --reload

Step 2: 

Create a DB user allow remote connection on all the 3 nodes.

create user 'i3'@'%' IDENTIFIED BY 'Secure@123';

grant all privileges on *.* to i3;

create user 'i3'@'%' IDENTIFIED BY 'Secure@123';
grant all privileges on *.* to i3;

GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'i3'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'i3'@'%' 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 'i3'@'%' 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_bkp.* TO 'i3'@'%' 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_previous.* TO 'i3'@'%' WITH GRANT OPTION;

flush privileges;

Test Connection:

mysql -u i3 -h DB_Server --socket=/var/lib/mysql/mysql.sock -p

Step 3: 

Bind IP Address and update the port

Update my.cnf with below parameter...

bind-address=0.0.0.0

port=3306

Step 4: 

MySQL Shell Installation 

# yum install mysql-shell

On clota-dbl05 

dba.configureInstance('i3@192.168.0.196:3306');

On ota-dbl06 

dba.configureInstance('i3@192.168.0.197:3306');

On clota-dbl07 

dba.configureInstance('i3@192.168.0.195:3306');

Step 5: 

Create Cluster

shell.connect('i3@192.168.0.196:3306');

var cluster =dba.createCluster('ProdCluster')

Cluster.addInstance('i3@192.168.0.197:3306')

Cluster.addInstance('i3@192.168.0.195:3306')

cluster.status()

cluster.describe()

select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_COMMUNICATION_STACK,MEMBER_VERSION from replication_group_members;

Connect to Cluster Using Shell

# mysqlsh

MySQL  JS > shell.connect('i3@clota-dbl05:3306')

var cluster = dba.getCluster();

cluster.getName()

cluster.status();

Failover DB:

JS> shell.connect('clusteradmin@db1:3306');

JS> var cluster = dba.getCluster('ProdCluster')

JS> cluster.setPrimaryInstance('db1:3306');