Mysql InnoDB Cluster Setup On CentOS 7 X64

Installing Mysql 5.7 in CentOS 7 X64 bit operating system

Get the yum repository from here as per your OS config.
https://dev.mysql.com/downloads/repo/yum/
Run the below command
yum localinstall mysql57-community-release-el7-11.noarch.rpm

Grep the mysql 5.7 releases from our OS Repo

yum repolist enabled | grep “mysql.*-community.*”

yum install mysql-community-server

After Installation, Restart the mysql 5.7 server and grep the temporary password from mysql log files.

service mysqld restart

grep “password” /var/log/mysqld.log

Login into Mysql 5.7 server using temporary password and change the root password as per your wish.

mysql -uroot -p

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘<Password@123>’;

Mysql InnoDB Cluster Setup On CentOS 7 X64

Part 1 – InnoDB Cluster – Configuring Mysql 5.7

We have to enforce group replication configurations in the server. It is one of the mandatory pre-validation to initiate clustering setup.

Open /etc/my.cnf and paste the following lines

default-storage-engine=InnoDB
log_bin=mysql-bin
server-id=1
log_slave_updates=1

binlog_format=ROW
enforce_gtid_consistency=1
gtid_mode = ON
master_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
binlog_checksum=NONE
relay_log_info_repository=TABLE

So far you have finished the Mysql configuration requirement to start Cluster set up.

Installing Mysql shell and Mysql router

You need to install the following in all the instances you wish to connect with InnoDB cluster.

yum install mysql-shell

yum install mysql-router

To access the Mysql Admin Shell. Type the following

mysqlsh –log-level=DEBUG3

mysql-js> dba.verbose=2

The above options will help us to see extended log or error messages while configuring Cluster.

Let’s say you want to connect 3 Mysql server in 3 different machine. You need to configure local instance in all the machines.

Execute the following in all machines.

mysql-js> dba.configureLocalInsance()

mysql-js> dba.checkInstanceConfiguration(‘<user1>@<hostname>:3306’)

The above command will display the message something like this.

You are right, it will show error.

Now, restart the mysql server after you did the above steps in all the machines check the instance configuration and move the clustering process.

dba.checkInstanceConfiguration(“'<user1>@<hostname>:3306’”)

Deploying Cluster

From the primary node. Create the Innodb cluster and add our instances into it

var cluster=dba.createCluster(‘mycluster’,{ipWhitelist:”192.168.1.251, 192.168.1.252, 192.168.1.253″});

If you receive “The group replication applier thread was killed’” in mysqld.log messages

You can run the following query in mysql root

SET GLOBAL group_replication_ip_whitelist = ‘192.168.1.251,192.168.1.252,192.168.1.253’;

Note: Check the SELinux status, Firewalld and Iptables. Make sure it does not affect your mysql connection between nodes. Using the whitelist method you should not get any errors.

Add

cluster.checkInstanceState(“clusteruser@192.168.1.252:3306”)

cluster.checkInstanceState(“clusteruser@192.168.1.253:3306”)

You will get Status:OK output. Awesome!

cluster.addInstance(“clusteruser@192.168.1.252:3306”)

cluster.addInstance(“clusteruser@192.168.1.253:3306”)

If the output is START GROUP REPLICATION error you should whitelist the instance ip address in all the 3 servers.

Check the cluster status.

mysql-js> cluster.status()

{

“clusterName”: “mycluster”,
“defaultReplicaSet”: {

“name”: “default”,
“primary”: “192.168.1.251:3306”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {

“192.168.1.251:3306”: {

“address”: “192.168.1.251:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},

“192.168.1.252:3306”: {

“address”: “192.168.1.252:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},

“192.168.1.253:3306”: {
“address”: “192.168.1.253:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

Good work, You have created a InnoDB cluster network with 3 instances into it.

“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”

The above line states that If a server is offline the other 2 servers can manage and serve the data to the client or customers.