We collect InnoDB cluster from mysql 5.7 on centos 7

From the sandbox
Tutorial

Hello!

Once there was a task to work - there seems to be a configured test innoDB cluster on several virtuals 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 that are 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 new note with a sin in half (some of the commands did not come in handy, 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. As a result, I stopped mindless attempts to do everything on paper and began to tune it by typing. 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 virtual machines 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

I used mysql 5.7 for work, 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=0log-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
# Groupreplication 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 version5.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.

Because 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
> setGLOBAL 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
> \c127.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;
> SETGLOBAL 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 the 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.” Mode, we
add a third algorithm, in principle, it does not differ from adding a second one.

If you need to change the whitelist again, then you need to execute the commands on the r / w node, since on r / o nodes it does not seem to lead to anything. In this case, the r / o nodes will fall off and they will have 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 a single node, it will have to be started with the parameter loose-group_replication_bootstrap_group = ON in /etc/my.cnf
.

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

That's all like this, I hope someone my experience will come in handy.

Thanks for attention.

Tags:

We collect InnoDB cluster from mysql 5.7 on centos 7

Also popular now: