We collect InnoDB cluster from mysql 5.7 on centos 7

Hello!

Once there was a task to work - there seems to be a configured test innoDB cluster on several virtual machines with centos 7.5, you need to study it and add a couple more nodes. You can break and mock as you like. It sounds tempting.

Before that, I had no experience with such a cluster, well, Google’s help.
With a few exceptions, all the links in it, that in Yandex led either to dev.mysql.com, or to this article on Habré . It seems like a cluster of two nodes was configured on it.

Well, I read the article, I was somewhat surprised at the complexity of adding nodes and the absence of many details, but oh well. I added a sin in half with a new node (some of the commands were not useful, some of them broke everything), and then began experimenting with restarting the nodes, etc.
')
After several approaches and countless deaths of nerve cells, the cluster could not stand it. One node didn’t want to be added under any circumstances, another hovered while trying to access the database, and the third pretended that everything was in order. I had to shoot and start from scratch.

When creating a new cluster, unfortunately, no less a lot of problems and inconsistencies arose. Maybe it's in the versions of the programs, I tried mysql 5.7. Perhaps in the distribution. In the end, I stopped the mindless attempts to do everything on paper and began to tune it at random. And google.

A couple of pleasant evenings and nights and the cluster gathered and even refused to crumble.
At the same time, the method of its creation was noticeably different from previous attempts, and I wanted to share it, because On the Internet, I did not find other relevant, detailed, clear instructions for configuring inndoDB cluster.

So, we have three identical virtuals with freshly installed Centos 7.5 minimal 1804 and disabled selinux and firewalld:

1.1.1.1
1.1.1.2
1.1.1.3

At work, I used mysql 5.7, so we use it. Let's start with 1.1.1.1:

1. Install the mysql-community repository:

rpm -i https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm 

Turn off the repository for 8, turn on for 5.7 and check - if everything is ok, then install mysql:

 yum install yum-utils yum-config-manager --disable mysql80-community yum-config-manager --enable mysql57-community yum repolist yum install mysql-community-server mysql-shell 

2. We give /etc/my.cnf to this form:

 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid bind-address=0.0.0.0 port=3301 # Replication part server_id=1 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW plugin-load = group_replication.so # Group replication part transaction_write_set_extraction=XXHASH64 loose-group_replication_start_on_boot = OFF loose-group_replication_local_address = 1.1.1.1:33011 loose-group_replication_bootstrap_group = OFF report_port = 3301 report_host = 1.1.1.1 

Here 3301 is the port on which mysql will listen, and 33011 is the port on which the nodes communicate with each other.

3. Run mysql and pre-configure:

 systemctl start mysqld grep 'password' /var/log/mysqld.log mysql_secure_installation 

4. Well, create a cluster, as well as an individual user to manage it. If you know in advance the ip-addresses of the nodes, then you can immediately list them in ipWhitelist. Pretend that we do not yet know about 1.1.1.2. and 1.1.1.3:

 mysqlsh > \c 127.0.0.1:3301 > dba.configureLocalInstance("127.0.0.1:3301", {mycnfPath: "/etc/my.cnf", clusterAdmin: "cladmin", clusterAdminPassword: "SomePassword!123"}) > \c cladmin@1.1.1.1:3301 > dba.checkInstanceConfiguration() > cl=dba.createCluster('TestCluster', {ipWhitelist: '1.1.1.1'}) > dba.configureLocalInstance() > cl.status() 

Done! cl.status should output something like this:

 { "clusterName": "TestCluster", "defaultReplicaSet": { "name": "default", "primary": "1.1.1.1:3301", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "1.1.1.1:3301": { "address": "1.1.1.1:3301", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://cladmin@1.1.1.1:3301" } 

When a cluster is changed, it will be necessary to execute the dba.configureLocalInstance () locally command on all nodes to save the changes:

 WARNING: On instance '1.1.1.1:3301' membership change cannot be persisted since MySQL version 5.7.23 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the <Dba>.configureLocalInstance command locally to persist the changes. 

Since we are going to add a couple more nodes, then we do not close the connection with the server 1.1.1.1, it will still be useful to us.

Now we will try to add node 1.1.1.2 to the cluster. To do this, we execute all the same commands on it up to 3 steps inclusive, not forgetting to change server_id, loose-group_replication_local_address and report_host.

4. Perform on 1.1.1.2:

 mysql -p > set GLOBAL group_replication_allow_local_disjoint_gtids_join=ON; 

I tried to set this variable through mysqlsh, switching to sql mode, but the actions there did not affect it in mysql. Further:

 mysqlsh > \c 127.0.0.1:3301 > dba.configureLocalInstance("127.0.0.1:3301", {mycnfPath: "/etc/my.cnf", clusterAdmin: "cladmin", clusterAdminPassword: "SomePassword!123"}) > \c cladmin@1.1.1.2:3301 > dba.checkInstanceConfiguration() 

5. Go back to the first node 1.1.1.1. If you have closed the connection, you can quickly connect to the cluster as follows:

 mysqlsh --uri cladmin@1.1.1.1:3301 --cluster > \sql > STOP GROUP_REPLICATION; > SET GLOBAL group_replication_ip_whitelist="1.1.1.1,1.1.1.2"; > START GROUP_REPLICATION; > \js > cluster.addInstance('cladmin@1.1.1.2:3301', {ipWhitelist: '1.1.1.1,1.1.1.2'}) > cluster.status() 

For some reason, when adding a node without the ipWhitelist option, it is not automatically transmitted to it, so we specify it manually.
If your whitelist is initially configured for all nodes or subnets, then you can skip commands in sql mode.

Do not forget to run dba.configureLocalInstance () on all nodes to save the configuration.

It turned out a cluster of two nodes:

 { "clusterName": "TestCluster", "defaultReplicaSet": { "name": "default", "primary": "1.1.1.1:3301", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "1.1.1.1:3301": { "address": "1.1.1.1:3301", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "1.1.1.2:3301": { "address": "1.1.1.2:3301", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://cladmin@1.1.1.1:3301" } 

Well, there is a cluster of two nodes, but in the "Cluster is NOT tolerant to any failures."
Add a third algorithm in principle does not differ from adding a second one.

If you need to change whitelist again, then you need to execute commands on the r / w node, since on r / o nodes, this does not seem to lead to anything. At the same time, the r / o nodes will fall off and they will need to be reconnected, simultaneously reporting a new whitelist.
In our case:

 > cluster.rejoinInstance('cladmin@1.1.1.2:3301', {ipWhitelist: '1.1.1.1,1.1.1.2,1.1.1.3'}) 

Well, again, do not forget to run dba.configureLocalInstance () on all the nodes to save the configuration.

A cluster of three nodes looks like this:

 { "clusterName": "TestCluster", "defaultReplicaSet": { "name": "default", "primary": "1.1.1.1:3301", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "1.1.1.1:3301": { "address": "1.1.1.1:3301", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "1.1.1.2:3301": { "address": "1.1.1.2:3301", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "1.1.1.3:3301": { "address": "1.1.1.3:3301", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://cladmin@1.1.1.1:3301" } 

If the cluster has collapsed to the state of one node, it will have to be started with the parameter loose-group_replication_bootstrap_group = ON in /etc/my.cnf
After starting the parameter, it will be necessary to turn it off back, otherwise this node will always be separated from the cluster and maintain its own.

The installation of mysql-router is well described here , so I don’t see a duplicate of meaning.

Everything seems to be on this, I hope someone my experience will come in handy.

Thanks for attention.