wagnerbianchi.com

MySQL InnoDB Cluster, now with remote nodes!

setembro 25th, 2016 Bianchi Posted in MySQL Tuning | No Comments »

screen-shot-2016-09-25-at-10-48-55-amIn this post I’m going to extend the tests I made with MySQL InnoDB Cluster on the previous post, creating a group of instances with separate servers, that is, I’m going to test how to create a new cluster with three different machines considering that, if you create a cluster using one giant server, maybe it may be considered a big single point of failure in case this giant server crashes and all cluster’s members crashes altogether.

In this case, we know that, to prevent that situation is something that is part of any project using a database which principle is to scale-out in order to attend more and more data requests. This is a subject for another blog in which we can discuss the main strategies to slave writes and reads and go beyond of the scope of this current post.

I’m going to concentrate here in creating the cluster with 3 machines, I’m using vagrant to create them and the following is the script that will create the virtual machines:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# -*- mode: ruby -*-
# vi: set ft=ruby :
 
VAGRANTFILE_API_VERSION = "2"
 
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.define "box01" do |box01|
	box01.vm.hostname="box01"
	box01.vm.box = "centos7.0_x86_64"
	box01.vm.network "private_network", ip: "192.168.50.11", virtualbox__intnet: "mysql_innodb_cluster"
  end
 
  config.vm.define "box02" do |box02|
	box02.vm.hostname="box02"
        box02.vm.box = "centos7.0_x86_64"
        box02.vm.network "private_network", ip: "192.168.50.12", virtualbox__intnet: "mysql_innodb_cluster"
  end
 
  config.vm.define "box03" do |box03|
        box03.vm.hostname="box03"
        box03.vm.box = "centos7.0_x86_64"
        box03.vm.network "private_network", ip: "192.168.50.13", virtualbox__intnet: "mysql_innodb_cluster"
  end
end

I’m considering the you have added a CentOS 7 image to your local vagrant boxes library and that you’re using the VirtualBox hypervisor driver to create virtual machines. If there is something different than this on your setup, maybe the above script won’t work as expected. Below, machines are running:

wagnerbianchi01-3:mysql_innodb_cluster01 root# vagrant status
Current machine states:
box01                     running (virtualbox)
box02                     running (virtualbox)
box03                     running (virtualbox)

With that, we can start configuring the servers in order to create the cluster. Basically, the steps are like below:

1. Setup all packages on all three servers

On the first server, install all packages including the router one as we are going to bootstrap it on that node. You don’t need to install MySQL Router package on the other two nodes as it’s not needed there. MySQL Shell should be installed on all three nodes. So, below I show you what packages I installed on each of the nodes:

#: box01
  mysql-community-client.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-common.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-devel.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-libs.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-libs-compat.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-server.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-router.x86_64 0:2.1.0-0.1.labs.el7
  mysql-router-debuginfo.x86_64 0:2.1.0-0.1.labs.el7
  mysql-shell.x86_64 0:1.0.5-0.1.labs.el7
  mysql-shell-debuginfo.x86_64 0:1.0.5-0.1.labs.el7
 
#: box02
  mysql-community-client.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-common.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-devel.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-libs.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-libs-compat.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-server.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-shell.x86_64 0:1.0.5-0.1.labs.el7
  mysql-shell-debuginfo.x86_64 0:1.0.5-0.1.labs.el7
 
#: box03
  mysql-community-client.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-common.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-devel.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-libs.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-libs-compat.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-community-server.x86_64 0:5.7.15-1.labs_gr090.el7
  mysql-shell.x86_64 0:1.0.5-0.1.labs.el7
  mysql-shell-debuginfo.x86_64 0:1.0.5-0.1.labs.el7

To grab all these packages for your testes, click here (http://downloads.mysql.com/snapshots/pb/mysql-innodb-cluster-5.7.15-preview/mysql-innodb-cluster-labs201609-el7-x86_64.rpm.tar.gz)

2. Add the correct configs/setting to mysql configuration file aka my.cnf:

[root@box01 mysql]# cat /etc/my.cnf
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
#: innodb cluster configs
server_id=1
binlog_checksum=none
enforce_gtid_consistency=on
gtid_mode=on
log_bin
log_slave_updates
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

Make sure you restart mysqld in case you add new configs after having it initialized to have above variables in effect.

3. Initialize mysqld (using the –initialize-insecure and restart service):

[root@box01 ~]# mysqld --initialize-insecure
[root@box01 mysql]# ls -lh
insgesamt 109M
-rw-r----- 1 mysql mysql   56 24. Sep 16:23 auto.cnf
-rw-r----- 1 mysql mysql  169 24. Sep 16:23 box01-bin.000001
-rw-r----- 1 mysql mysql   19 24. Sep 16:23 box01-bin.index
-rw-r----- 1 mysql mysql  413 24. Sep 16:23 ib_buffer_pool
-rw-r----- 1 mysql mysql  12M 24. Sep 16:23 ibdata1
-rw-r----- 1 mysql mysql  48M 24. Sep 16:23 ib_logfile0
-rw-r----- 1 mysql mysql  48M 24. Sep 16:23 ib_logfile1
drwxr-x--- 2 mysql mysql 4,0K 24. Sep 16:23 mysql
drwxr-x--- 2 mysql mysql 8,0K 24. Sep 16:23 performance_schema
drwxr-x--- 2 mysql mysql 8,0K 24. Sep 16:23 sys
[root@box01 mysql]# systemctl restart mysqld.service
[root@box01 mysql]# systemctl status mysqld.service
mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
   Active: active (running) since Sa 2016-09-24 16:25:13 CEST; 6s ago
  Process: 17112 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 17095 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 17116 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─17116 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 
Sep 24 16:25:12 box01 systemd[1]: Starting MySQL Server...
Sep 24 16:25:13 box01 systemd[1]: Started MySQL Server.

4. Configure the password for root@‘%’ giving the GRANT OPTIONS for this user:

In this step you need to work on giving the right privileges for the root@‘%’ and configure a password for this user which will be used soon to complete the setup. In the next steps which is the verify and validate the instance, you will be prompted this root@‘%’ password, so, follow the below steps on all three nodes:

#: create and configure the root@‘%'
mysql> grant all on *.* to root@'%' identified by 'bianchi' with grant option;
Query OK, 0 rows affected, 1 warning (0,00 sec) -- don’t worry about this warning
 
#: configure the password for root@localhost
mysql> set password='bianchi';
Query OK, 0 rows affected (0,00 sec)
 
#: in any case, flush grants tables
mysql> flush privileges;
Query OK, 0 rows affected (0,00 sec)

5. Validate instances, this is done accessing the MySQL Shell on all the three nodes and run the below command:

mysql-js> dba.validateInstance('root@localhost:3306')
Please provide a password for 'root@localhost:3306':
Validating instance...
 
Running check command.
Checking Group Replication prerequisites.
* Comparing options compatibility with Group Replication... PASS
Server configuration is compliant with the requirements.
* Checking server version... PASS
Server is 5.7.15
 
* Checking that server_id is unique... PASS
The server_id is valid.
 
* Checking compliance of existing tables... PASS
 
The instance: localhost:3306 is valid for Cluster usage

At this point in which we’re going to start accessing instances all around, make sure you configure iptables appropriately or even, just flush all the configured chains on that in order to avoid the below message when accessing remote nodes:

[root@box01 mysql]# mysql -u root -p -h box02
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'box02' (113)
 
[root@box02 ~]# iptables -F
[root@box02 ~]# systemctl firewalld stop
 
[root@box01 mysql]# mysql -u root -p -h box02
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.15-labs-gr090-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> \q
Bye

6. At this point, we need to create a cluster:

Let’s use box01 as the server in which we will create the cluster and bootstrap it, creating all the cluster’s metadata.

#: create the cluster on box01
[root@box01 mysql]# mysqlsh
Welcome to MySQL Shell 1.0.5-labs Development Preview
 
Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.
 
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \c root@localhost:3306
Creating a Session to 'root@localhost:3306'
Enter password:
Classic Session successfully established. No default schema selected.
 
mysql-js> cluster = dba.createCluster('wbCluster001')
A new InnoDB cluster will be created on instance 'root@localhost:3306'.
 
When setting up a new InnoDB cluster it is required to define an administrative
MASTER key for the cluster. This MASTER key needs to be re-entered when making
changes to the cluster later on, e.g.adding new MySQL instances or configuring
MySQL Routers. Losing this MASTER key will require the configuration of all
InnoDB cluster entities to be changed.
 
Please specify an administrative MASTER key for the cluster 'wbCluster001':
Creating InnoDB cluster 'wbCluster001' on 'root@localhost:3306'...
Adding Seed Instance...
 
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
 
mysql-js>

Now we can use the the value we stored on the variable cluster to exhibit the status of the just created cluster:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mysql-js> cluster.status()
{
    "clusterName": "wbCluster001",
    "defaultReplicaSet": {
        "status": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "localhost:3306": {
                "address": "localhost:3306",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {}
            }
        }
    }
}

Cluster status at this point shows that it’s not fault tolerant due to don’t have any other node as part of the cluster wbCluster001. Another thing I verified here and it was present on the scenario of the previous post as well, is that the metadata is created on some tables on the database schema called mysql_innodb_cluster_metadata, added to the instance used to create the cluster and that will be the instance to manage the cluster.

#: box01, the instance used as the cluster’s seed
mysql> use mysql_innodb_cluster_metadata
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| clusters                                |
| hosts                                   |
| instances                               |
| replicasets                             |
| schema_version                          |
+-----------------------------------------+
5 rows in set (0,00 sec)
 
mysql> select cluster_id,cluster_name from mysql_innodb_cluster_metadata.clusters\G
*************************** 1. row ***************************
  cluster_id: 1
cluster_name: wbCluster001
1 row in set (0,00 sec)

7. Adding instances to the cluster:

By now, what we need to do is to start adding the instances we setup on our existing cluster and to do that, in case you don’t have the cluster’s name on cluster variable anymore, you can use mysqlsh, connect to the instance running on box01:3306 and user the dba.getCluster(‘wbCluster001’) again. After doing that, you can move forward an execute the below addInstances() methods to add instances box02,box03 to the existing cluster.

mysql-js> \c root@192.168.50.11:3306
Creating a Session to 'root@192.168.50.11:3306'
Enter password:
Classic Session successfully established. No default schema selected.
mysql-js> cluster = dba.getCluster('wbCluster001')
When the InnoDB cluster was setup, a MASTER key was defined in order to enable
performing administrative tasks on the cluster.
 
Please specify the administrative MASTER key for the cluster 'wbCluster001':
<Cluster:wbCluster001>
 
#: adding box02
mysql-js> cluster.addInstance('root@192.168.50.12:3306')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
 
Please provide the password for 'root@192.168.50.12:3306':
Adding instance to the cluster ...
 
The instance 'root@192.168.50.12:3306' was successfully added to the cluster.
 
#: adding box03
mysql-js> cluster.addInstance('root@192.168.50.13:3306')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
 
Please provide the password for 'root@192.168.50.13:3306':
Adding instance to the cluster ...
 
The instance 'root@192.168.50.13:3306' was successfully added to the cluster.

At this point, configuring exactly the way you’re reading above, I saw the error logs on both joiner nodes, box02 and box03, the following messages:

2016-09-25T00:34:11.285509Z 61 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'mysql_innodb_cluster_rpl_user@box01:3306' - retry-time: 60  retries: 1, Error_code: 2005
2016-09-25T00:34:11.285535Z 61 [Note] Slave I/O thread for channel 'group_replication_recovery' killed while connecting to master
2016-09-25T00:34:11.285539Z 61 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2016-09-25T00:34:11.285963Z 48 [ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Check group replication recovery's connection credentials.'
2016-09-25T00:34:11.286204Z 48 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 8/10’

While more and more errors due to connection between joiner and donor were added to the error log, I added to all boxes some entries on /etc/hosts and than, the issue was fixed. So, this is very important to consider the configuration below added to the machines’ hosts file to server as a DNS resolver. If you don’t do that, when you check the cluster.status(), it’s going to report that the joiner db node is in RECOVERY MODE as box03 or 192.168.50.13:3306 below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

mysql-js> cluster.status()
{
    "clusterName": "wbCluster001",
    "defaultReplicaSet": {
        "status": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "192.168.50.11:3306": {
                "address": "192.168.50.11:3306",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "192.168.50.12:3306": {
                        "address": "192.168.50.12:3306",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "192.168.50.13:3306": {
                        "address": "192.168.50.13:3306",
                        "status": "RECOVERING”,
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}

As many attempts were done while I was fixing the problem related to the hosts file, I had to do a cluster.rejoinInstance for box03, as you can see below:

mysql-js> cluster.rejoinInstance('root@192.168.50.13:3306')
Please provide the password for 'root@192.168.50.13:3306':
The instance will try rejoining the InnoDB cluster. Depending on the original
problem that made the instance unavailable the rejoin, operation might not be
successful and further manual steps will be needed to fix the underlying
problem.
 
Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.
Enter the password for server (root@192.168.50.13:3306):
Enter the password for replication_user (mysql_innodb_cluster_rpl_user):
Enter the password for peer_server (root@192.168.50.12:3306):
 
Running join command on '192.168.50.13@3306'.
 
Running health command on '192.168.50.13@3306'.
Group Replication members:
  - Host: box03
    Port: 3306
    State: ONLINE
  - Host: box02
    Port: 3306
    State: ONLINE
  - Host: box01
    Port: 3306
    State: ONLINE

So, at this point, the cluster is OK, all three nodes running well and fine:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

#: describe cluster
mysql-js> cluster.describe()
{
    "clusterName": "wbCluster001",
    "adminType": "local",
    "defaultReplicaSet": {
        "name": "default",
        "instances": [
            {
                "name": "192.168.50.11:3306",
                "host": "192.168.50.11:3306",
                "role": "HA"
            },
            {
                "name": "192.168.50.12:3306",
                "host": "192.168.50.12:3306",
                "role": "HA"
            },
            {
                "name": "192.168.50.13:3306",
                "host": "192.168.50.13:3306",
                "role": "HA"
            }
        ]
    }
}
#: cluster status
 
mysql-js> cluster.status()
{
    "clusterName": "wbCluster001",
    "defaultReplicaSet": {
        "status": "Cluster is tolerant to 2 failures.",
        "topology": {
            "192.168.50.11:3306": {
                "address": "192.168.50.11:3306",
                "status": "ONLINE",
                "role": "HA",
                "mode": "R/W",
                "leaves": {
                    "192.168.50.12:3306": {
                        "address": "192.168.50.12:3306",
                        "status": "ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    },
                    "192.168.50.13:3306": {
                        "address": "192.168.50.13:3306",
                        "status": “ONLINE",
                        "role": "HA",
                        "mode": "R/O",
                        "leaves": {}
                    }
                }
            }
        }
    }
}

After solving the issues above mentioned, I saw the following events added to the error log on box02 and box03:

#: box02
2016-09-26T14:07:02.432632Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
 
#: box03
2016-09-26T14:14:52.432632Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

At the end, you can check that the MySQL Group Replication is the underlying feature that empower MySQL InnoDB Cluster. On box01, or, 192.168.50.11:3306:

mysql-sql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | b0b1603f-83ef-11e6-85a6-080027de0e0e | box01       |        3306 | ONLINE       |
| group_replication_applier | bb29750c-83ef-11e6-8b4f-080027de0e0e | box02       |        3306 | ONLINE       |
| group_replication_applier | bbu3761b-83ef-11e6-894c-080027de0t0e | box03       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

Next time, I’m going to bootstrap the router to show some tests related to the routing connections out of failed nodes. The final considerations over this new way to provide HA to an environment using InnoDB are, there is no documentation enough yet regrading the exiting methods to manipulate instances within the cluster, in case you need to take one off, restart it or even get to know why they are OFFLINE, I haven’t found yet a way to better manipulate nodes but add them to the cluster. This is not GA, the feature was just released, to me it’s very promising and will make it easier to add clusters and I expect to see more and more about this. Once again, great job Oracle MySQL Team, let’s move on!!

You can find more resources on below links:

– http://mysqlserverteam.com/introducing-mysql-innodb-cluster-a-hands-on-tutorial/
– http://mysqlserverteam.com/introducing-mysql-innodb-cluster-mysql-ha-out-of-box-easy-to-use-high-availability/

Arrivederci!!


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply

«
»