MySQL InnoDB Cluster: MySQL Shell starter guide

on

 Earlier this week,  MySQL Shell 1.08 has been released. This is the first Release Candidate of this major piece of MySQL InnoDB Cluster.

Some commands have been changed and some new ones were added.

For example the following useful commands were added:

  • dba.checkInstanceConfiguration()
  • cluster.checkInstanceState()
  • dba.rebootClusterFromCompleteOutage()

So let’s have a look on how to use the new MySQL Shell to create a MySQL InnoDB Cluster.

Action Plan

We have 3 blank Linux servers: mysql1, mysql2 and mysql3 all running rpm based Linux version 7 (Oracle Linux 7, CentOS 7, …).

We will install the required MySQL yum repositories and install the needed packages

We will use MySQL Shell to setup our MySQL InnoDB Cluster.

Packages

To be able to install our cluster, we will first install the repository from the MySQL release package. For more information related to MySQL’s installation or if you are using another OS, please check our online documentation.

On all 3 servers, we do:

# rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
# yum install -y mysql-community-server

The commands above, will install the MySQL Community yum repositories and install MySQL Community Server 5.7.17, being the latest GA version at the date of this post.
Now we will have to install the Shell. As this tool is not yet GA, we need to use another repository that has been installed but not enabled: mysql-tool-preview

# yum install -y mysql-shell --enablerepo=mysql-tools-preview

We are done with the installation. Now let’s initialize MySQL and start it.

Starting MySQL

Before being able to start MySQL, we need to create all necessary folders and system tables. This process is called MySQL Initialization. Let’s proceed without generating a temporary root password as it will be easier and faster for the demonstration. However, I highly recommend you to use a strong root password.

When the initialization is done, we can start MySQL. So on all the future nodes, you can proceeds like this:

# mysqld --initialize-insecure -u mysql --datadir /var/lib/mysql/
# systemctl start mysqld
# systemctl status mysqld

MySQL InnoDB Cluster Instances Configuration

We have now everything we need to start working in the MySQL Shell to configure all the members of our InnoDB Cluster.

First, we will check the configuration of one of our MySQL server. Some changes are required, we will perform them using the Shell and we will restart mysqld:

# mysqlsh
mysql-js> dba.checkInstanceConfiguration('root@localhost:3306')
...
mysql-js> dba.configureLocalInstance()
... here please create a dedicated user and password to admin the cluster (option 2) ...
mysql-js> \q

# systemctl restart mysqld

Now MySQL has all the required mandatory settings to run Group Replication. We can verify the configuration again in the Shell with dba.checkInstanceConfiguration() function.

We have now to proceed the same way on all the other nodes, please use the same credentials when you create the user to manage your cluster,  I used ‘fred@%’  as example.  As you can’t configure remotely a MySQL Server, you will have to run the Shell locally on every node to be able to run dba.configureLocalInstance() and restart mysqld.

MySQL InnoDB Cluster Creation

Now that all the nodes have been restarted with the correct configuration, we can create the cluster. On one of the instances, we will connect and create the cluster using again the Shell, I did it on mysql1 and I used its ip as it’s name resolves also on the loopback interface:

# mysqlsh
mysql-js> var i1='fred@192.168.90.2:3306'
mysql-js> var i2='fred@mysql2:3306'
mysql-js> var i3='fred@mysql3:3306'
mysql-js> shell.connect(i1)
mysql-js> var cluster=dba.createCluster('mycluster')
mysql-js> cluster.status()
...

We can now validate that the dataset on the other instances is correct (no extra transactions executed). This is done by validating the GTIDs. This can be done remotely, so I will still use the MySQL Shell session I’ve open on mysql1:

mysql-js> cluster.checkInstanceState(i2)
mysql-js> cluster.checkInstanceState(i3)

When the validation is passed successfully, it’s time to add the two other nodes to our cluster:

mysql-js> cluster.addInstance(i2)
mysql-js> cluster.addInstance(i3)
mysql-js> cluster.status()

Perfect ! We used MySQL Shell to create this MySQL InnoDB Cluster.

Now let’s see this on video with all  the output of the commands:

In the next post, I will show you how to use the Shell to automate the creation of a MySQL InnoDB Cluster using Puppet.

41 thoughts on “MySQL InnoDB Cluster: MySQL Shell starter guide

  1. Hi Fred!

    AFAIK, a default installation of RHEL / OEL / CentOS 7 will include a “mariadb-libs-5.5” package, and the MySQL 5.7 packages will conflict with it.
    In your video, I’m missing the signs of this conflict as well as your actions to handle it.
    I know the conflict can be solved in a clean way, but I’d like to see your way of doing that.

    Regards,
    Jörg

    1. Hi Jörg,

      Yum sees the conflict and deals with it:

      [root@mysql1 ~]# yum install mysql-community-server
      Loaded plugins: fastestmirror
      mysql-connectors-community | 2.5 kB 00:00:00
      mysql-tools-community | 2.5 kB 00:00:00
      mysql57-community | 2.5 kB 00:00:00
      (1/3): mysql-tools-community/x86_64/primary_db | 32 kB 00:00:00
      (2/3): mysql-connectors-community/x86_64/primary_db | 13 kB 00:00:00
      (3/3): mysql57-community/x86_64/primary_db | 96 kB 00:00:00
      Loading mirror speeds from cached hostfile
      * base: centos.mirror.nucleus.be
      * epel: nl.mirror.babylon.network
      * extras: mirrors.ircam.fr
      * updates: mirrors.ircam.fr
      Resolving Dependencies
      --> Running transaction check
      ---> Package mysql-community-server.x86_64 0:5.7.17-1.el7 will be installed
      --> Processing Dependency: mysql-community-common(x86-64) = 5.7.17-1.el7 for package: mysql-community-server-5.7.17-1.el7.x86_64
      --> Processing Dependency: mysql-community-client(x86-64) >= 5.7.9 for package: mysql-community-server-5.7.17-1.el7.x86_64
      --> Running transaction check
      ---> Package mysql-community-client.x86_64 0:5.7.17-1.el7 will be installed
      --> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client-5.7.17-1.el7.x86_64
      ---> Package mysql-community-common.x86_64 0:5.7.17-1.el7 will be installed
      --> Running transaction check
      ---> Package mariadb-libs.x86_64 1:5.5.50-1.el7_2 will be obsoleted
      --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
      --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
      ---> Package mysql-community-libs.x86_64 0:5.7.17-1.el7 will be obsoleting
      --> Running transaction check
      ---> Package mysql-community-libs-compat.x86_64 0:5.7.17-1.el7 will be obsoleting
      --> Finished Dependency Resolution

      Dependencies Resolved

      ============================================================================================================================================================================
      Package Arch Version Repository Size
      ============================================================================================================================================================================
      Installing:
      mysql-community-libs x86_64 5.7.17-1.el7 mysql57-community 2.1 M
      replacing mariadb-libs.x86_64 1:5.5.50-1.el7_2
      mysql-community-libs-compat x86_64 5.7.17-1.el7 mysql57-community 2.0 M
      replacing mariadb-libs.x86_64 1:5.5.50-1.el7_2
      mysql-community-server x86_64 5.7.17-1.el7 mysql57-community 162 M
      Installing for dependencies:
      mysql-community-client x86_64 5.7.17-1.el7 mysql57-community 24 M
      mysql-community-common x86_64 5.7.17-1.el7 mysql57-community 271 k

      Transaction Summary
      ============================================================================================================================================================================
      Install 3 Packages (+2 Dependent packages)

      Total download size: 190 M
      Is this ok [y/d/N]:

      However, you can still use the swap command:

      [root@mysql1 ~]# yum swap mariadb-libs mysql-community-libs

      If you have am issue with the swap command of yum, you can still use yum’s shell to create a transaction like this:

      [root@mysql1 ~]# yum -q -q shell
      > remove mariadb-libs
      > install mysql-community-libs
      > transaction run

      I hope this helps,

      Cheers,
      Fred.

      1. Hi Fred,
        thanks for this command log – it helps.
        Obviously, I don’t use yum often enough: my memory still said that yum (like rpm) will only remove other packages when called to “upgrade”, not with “install”. So that has changed.
        Regards,
        Jörg

  2. Hi Lefred,

    Having some issues with the Cluster.

    I have a normal 3 Node Cluster and Ive defined a Cluster on my Seed Successfully.

    Issue: Error’s When Joining the 2x Nodes to the Cluster. See below CheckInstance result:

    mysql-js> cluster.checkInstanceState(‘root@165.233.206.43:3306’)
    Please provide the password for ‘root@165.233.206.43:3306’:
    Analyzing the instance replication state…

    The instance ‘165.233.206.43:3306’ is invalid for the cluster.
    The instance contains additional transactions in relation to the cluster.

    {
    “reason”: “diverged”,
    “state”: “error”
    }

    Ive Set this up 20x times in my Sandbox Environment but now this issue is

    1. Hi Lerato,

      This means that the data on 2nd node is not the same as on the seed. In fact this 2nd node has (at least one) more transaction (check the executed GTID). How did you provision the data on the nodes ? If those are fresh instances, did you add the credentials on each node individually ? If this is the case and the only trx you did in the second instance, you can run RESET MASTER on it and join it again.

      Thank you for testing MySQL Group Replication & InnoDB Cluster.

      Cheers,

  3. Hi,

    Yes Data was exactly the same as the 2 other Server’s are clones of the Seed Server. The Only difference was that I ran the Credentials individually on the other 2 nodes and thus the difference in GTID’s. I ran a RESET MASTER on the 2nd node and now the Node is ready to be added to cluster, Now I get a different Issue:

    mysql-js> cluster.checkInstanceState(‘root@165.233.206.40:3306’)
    Please provide the password for ‘root@165.233.206.40:3306’:
    Analyzing the instance replication state…

    The instance ‘165.233.206.40:3306’ is valid for the cluster.
    The instance is fully recoverable.

    {
    “reason”: “recoverable”,
    “state”: “ok”
    }

    Then Addin gthe Node to Cluster:

    mysql-js> cluster.addInstance(‘root@165.233.206.40: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@165.233.206.40:3306’:
    Adding instance to the cluster …

    Cluster.addInstance: ERROR:
    Group Replication join failed.
    ERROR: Group Replication plugin failed to start. Server error log contains the following errors:
    2017-09-14T10:24:46.749189Z 0 [ERROR] Plugin group_replication reported: ‘[GCS] Error connecting to the local group communication engine instance.’
    2017-09-14T10:24:47.774681Z 0 [ERROR] Plugin group_replication reported: ‘[GCS] The member was unable to join the group. Local port: 13306’
    2017-09-14T10:25:46.748722Z 37 [ERROR] Plugin group_replication reported: ‘Timeout on wait for view after joining group’
    2017-09-14T10:25:46.748968Z 37 [ERROR] Plugin group_replication reported: ‘[GCS] The member is leaving a group without being on one.’

    ERROR: Error joining instance to cluster: ‘165.233.206.40:3306’ – Query failed. 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)

    1. Hi Lerato,

      Could you check the error log on the 2nd node ? It might also try to add the credential he’s getting from node1 that you already added manually.

      If this is the case you should also peform the reset master on node 1.

      Next time, it’s preferable to add the credentials before the backup or using SET SQL_LOG_BIN=0 in the session you create those credentials (those dedicated for the cluster authentication).

      Cheers,

      1. Ohk Thanks, Added Same Credentials accross all 3 Nodes.

        The 2nd Node Error Log as it was being added to Cluster:

        2017-09-14T10:24:46.708780Z 37 [Note] Plugin group_replication reported: ‘Initialized group communication with configuration: group_replication_group_name: “9c99070e-9931-11e7-9744-005056830ccb”; group_replication_local_address: “165.233.206.40:13306”; group_replication_group_seeds: “165.233.206.43:13306”; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: “AUTOMATIC”‘
        2017-09-14T10:24:46.709380Z 39 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_applier’ executed’. Previous state master_host=”, master_port= 0, master_log_file=”, master_log_pos= 4, master_bind=”. New state master_host=”, master_port= 0, master_log_file=”, master_log_pos= 4, master_bind=”.
        2017-09-14T10:24:46.748556Z 42 [Note] Slave SQL thread for channel ‘group_replication_applier’ initialized, starting replication in log ‘FIRST’ at position 0, relay log ‘./host-db03-relay-bin-group_replication_applier.000003’ position: 4
        2017-09-14T10:24:46.748568Z 37 [Note] Plugin group_replication reported: ‘Group Replication applier module successfully initialized!’
        2017-09-14T10:24:46.748688Z 0 [Note] Plugin group_replication reported: ‘state 4257 action xa_init’
        2017-09-14T10:24:46.748744Z 0 [Note] Plugin group_replication reported: ‘Successfully bound to 0.0.0.0:13306 (socket=82).’
        2017-09-14T10:24:46.748771Z 0 [Note] Plugin group_replication reported: ‘Successfully set listen backlog to 32 (socket=82)!’
        2017-09-14T10:24:46.748780Z 0 [Note] Plugin group_replication reported: ‘Successfully unblocked socket (socket=82)!’
        2017-09-14T10:24:46.748961Z 0 [Note] Plugin group_replication reported: ‘connecting to 165.233.206.40 13306’
        2017-09-14T10:24:46.748985Z 0 [Note] Plugin group_replication reported: ‘Ready to accept incoming connections on 0.0.0.0:13306 (socket=82)!’
        2017-09-14T10:24:46.749074Z 0 [Note] Plugin group_replication reported: ‘client connected to 165.233.206.40 13306 fd 92’
        2017-09-14T10:24:46.749114Z 0 [Warning] Plugin group_replication reported: ‘[GCS] Connection attempt from IP address 165.233.206.40 refused. Address is not in the IP whitelist.’
        2017-09-14T10:24:46.749189Z 0 [ERROR] Plugin group_replication reported: ‘[GCS] Error connecting to the local group communication engine instance.’
        2017-09-14T10:24:46.749208Z 0 [Note] Plugin group_replication reported: ‘state 4257 action xa_exit’
        2017-09-14T10:24:46.749401Z 0 [Note] Plugin group_replication reported: ‘Exiting xcom thread’
        2017-09-14T10:24:47.774681Z 0 [ERROR] Plugin group_replication reported: ‘[GCS] The member was unable to join the group. Local port: 13306’
        2017-09-14T10:25:46.748722Z 37 [ERROR] Plugin group_replication reported: ‘Timeout on wait for view after joining group’
        2017-09-14T10:25:46.748925Z 37 [Note] Plugin group_replication reported: ‘Requesting to leave the group despite of not being a member’
        2017-09-14T10:25:46.748968Z 37 [ERROR] Plugin group_replication reported: ‘[GCS] The member is leaving a group without being on one.’
        2017-09-14T10:25:46.749450Z 42 [Note] Error reading relay log event for channel ‘group_replication_applier’: slave SQL thread was killed
        2017-09-14T10:25:46.750215Z 39 [Note] Plugin group_replication reported: ‘The group replication applier thread was killed’

        Unsure about the Whitelist Warning as I created the Cluster with the all 3 Nodes IP addresses Exclusively in the Whitelist:

        mysql> show variables like ‘group_replication_ip_whitelist’;
        +——————————–+————————————————+
        | Variable_name | Value |
        +——————————–+————————————————+
        | group_replication_ip_whitelist | 165.233.206.40, 165.233.206.41, 165.233.206.43 |
        +——————————–+————————————————+

  4. No IP tables.

    All 3 Nodes on same Vlan.

    Tested Manually(mysqlsh) From Seed –> Node 1 and that works 100%

    Kind Regards
    LT

      1. Hi Lefred,

        Yes I changed the Seed.

        I hadnt tested that as yet, and im using version 5.7.17.

        I had a breakthrough yesterday evening, Since I was seeing this warning before all the Error’s I decided to follow it into the Rabit hole:

        2017-09-14T10:24:46.749114Z 0 [Warning] Plugin group_replication reported: ‘[GCS] Connection attempt from IP address 165.233.206.40 refused. Address is not in the IP whitelist.’

        So on each of the Node’s I defined the following:

        SET GLOBAL group_replication_ip_whitelist = ‘165.233.206.40, 165.233.206.41, 165.233.206.43’;

        This solved the Issue as I was able to successfully add the 2 Nodes to the Cluster.

        I think it may have something to do with my cluster definition:

        var cluster = dba.createCluster(‘My_Cluster’, {ipWhitelist: “165.233.206.40, 165.233.206.41, 165.233.206.43”});

        Thank you for your help again, will let you know how it goes

        1. Hi Lefred,

          Cluster is alive and running perfectly, I just have one potential Issue, I see this one line in the Error log:

          [ERROR] Plugin group_replication reported: ‘Group contains 2 members which is greater than group_replication_auto_increment_increment value of 1. This can lead to an higher rate of transactional aborts.’

          Should I be concerened with this Error as The Cluster is Online and succesfully switching between nodes with no issues, This came up when I added the Second Node to the Cluster.

          Tx!

  5. Dear lefred

    I was able to add 3 instance after lot of researching your blog and mysql dev website. Now,

    I can add 3 instance but the state are showing missing. It means it tried to recovering after 10 attempt its showing the “Missing state”. Attached the error log for you reference.

    mysql-js> cluster.status()
    {
    “clusterName”: “mycluster”,
    “defaultReplicaSet”: {
    “name”: “default”,
    “primary”: “mysql1:3306”,
    “status”: “OK_NO_TOLERANCE”,
    “statusText”: “Cluster is NOT tolerant to any failures. 2 members are not active”,
    “topology”: {
    “162.219.27.252:3306”: {
    “address”: “162.219.27.252:3306”,
    “mode”: “R/O”,
    “readReplicas”: {},
    “role”: “HA”,
    “status”: “RECOVERING”
    },
    “162.219.27.253:3306”: {
    “address”: “162.219.27.253:3306”,
    “mode”: “R/O”,
    “readReplicas”: {},
    “role”: “HA”,
    “status”: “(MISSING)”
    },
    “mysql1:3306”: {
    “address”: “mysql1:3306”,
    “mode”: “R/W”,
    “readReplicas”: {},
    “role”: “HA”,
    “status”: “ONLINE”
    }
    }
    }

    2017-12-17T17:03:50.277582Z 21 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_recovery’ executed’. Previous state master_host=’162-219-27-251.alnitech.com’, master_port= 3306, master_log_file=”, master_log_pos= 4, master_bind=”. New state master_host=’162-219-27-251.alnitech.com’, master_port= 3306, master_log_file=”, master_log_pos= 4, master_bind=”.
    2017-12-17T17:03:50.282422Z 21 [Note] Plugin group_replication reported: ‘Establishing connection to a group replication recovery donor 3f16c9a9-e30a-11e7-a8c1-000c2910cdea at 162-219-27-251.alnitech.com port: 3306.’
    2017-12-17T17:03:50.282639Z 27 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.
    2017-12-17T17:03:50.300335Z 27 [ERROR] Slave I/O for channel ‘group_replication_recovery’: error connecting to master ‘mysql_innodb_cluster_rp447960878@162-219-27-251.alnitech.com:3306’ – retry-time: 60 retries: 1, Error_code: 2005
    2017-12-17T17:03:50.300353Z 27 [Note] Slave I/O thread for channel ‘group_replication_recovery’ killed while connecting to master
    2017-12-17T17:03:50.300358Z 27 [Note] Slave I/O thread exiting for channel ‘group_replication_recovery’, read up to log ‘FIRST’, position 4
    2017-12-17T17:03:50.300530Z 21 [ERROR] Plugin group_replication reported: ‘There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.’
    2017-12-17T17:03:50.300547Z 21 [ERROR] Plugin group_replication reported: ‘For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.’
    2017-12-17T17:03:50.300717Z 21 [Note] Plugin group_replication reported: ‘Retrying group recovery connection with another donor. Attempt 3/10’

    1. Hi Saravana,

      Could you also paste the error log of the server called 162-219-27-251.alnitech.com ?

      It was acting as donor of the server you pasted the error log.
      Additionally, what’s the MySQL version you are testing ?
      Cheers.

      1. Thanks for your reply.
        Mysql version is mysqld Ver 5.7.20 for Linux on x86_64 (MySQL Community Server (GPL))

        2017-12-18T06:02:02.984084Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 2e0493c8’
        2017-12-18T06:02:07.085957Z 0 [Note] Plugin group_replication reported: ‘Members joined the group: mysql2:3306’
        2017-12-18T06:02:07.086067Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to mysql2:3306, 162-219-27-251.alnitech.com:3306 on view 15135188824968611:18.’
        2017-12-18T06:05:50.069841Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 2e0493c8’
        2017-12-18T06:05:52.154997Z 0 [Note] Plugin group_replication reported: ‘Members joined the group: 162-219-27-253.alnitech.com:3306’
        2017-12-18T06:05:52.155133Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to mysql2:3306, 162-219-27-251.alnitech.com:3306, 162-219-27-253.alnitech.com:3306 on view 15135188824968611:19.’

        This is the log messages.

        1. Once all attempts are done. This is the final log

          2017-12-18T06:02:02.984084Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 2e0493c8’
          2017-12-18T06:02:07.085957Z 0 [Note] Plugin group_replication reported: ‘Members joined the group: mysql2:3306’
          2017-12-18T06:02:07.086067Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to mysql2:3306, 162-219-27-251.alnitech.com:3306 on view 15135188824968611:18.’
          2017-12-18T06:05:50.069841Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 2e0493c8’
          2017-12-18T06:05:52.154997Z 0 [Note] Plugin group_replication reported: ‘Members joined the group: 162-219-27-253.alnitech.com:3306’
          2017-12-18T06:05:52.155133Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to mysql2:3306, 162-219-27-251.alnitech.com:3306, 162-219-27-253.alnitech.com:3306 on view 15135188824968611:19.’
          2017-12-18T06:11:07.412593Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 2e0493c8’
          2017-12-18T06:11:08.064886Z 0 [Warning] Plugin group_replication reported: ‘Members removed from the group: mysql2:3306’
          2017-12-18T06:11:08.065000Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to 162-219-27-251.alnitech.com:3306, 162-219-27-253.alnitech.com:3306 on view 15135188824968611:20.’
          2017-12-18T06:14:52.444985Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 2e0493c8’
          2017-12-18T06:14:52.974000Z 0 [Warning] Plugin group_replication reported: ‘Members removed from the group: 162-219-27-253.alnitech.com:3306’
          2017-12-18T06:14:52.974189Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to 162-219-27-251.alnitech.com:3306 on view 15135188824968611:21.’

  6. Hi lefred/saravan,

    I am also facing the similar issue like saravana, below is my cluster status, entry of my.cnf, /etc/hosts file. Also the error logs from my 1st and 2nd server. Kindly let me know where i am wrong.

    Cluster status:
    {
    “clusterName”: “mycluster”,
    “defaultReplicaSet”: {
    “name”: “default”,
    “primary”: “mysql01:3306”,
    “status”: “OK_NO_TOLERANCE”,
    “statusText”: “Cluster is NOT tolerant to any failures. 2 members are not active”,
    “topology”: {
    “mysql01:3306”: {
    “address”: “mysql01:3306”,
    “mode”: “R/W”,
    “readReplicas”: {},
    “role”: “HA”,
    “status”: “ONLINE”
    },
    “mysql02:3306”: {
    “address”: “mysql02:3306”,
    “mode”: “R/O”,
    “readReplicas”: {},
    “role”: “HA”,
    “status”: “(MISSING)”
    },
    “mysql03:3306”: {
    “address”: “mysql03:3306”,
    “mode”: “R/O”,
    “readReplicas”: {},
    “role”: “HA”,
    “status”: “(MISSING)”
    }
    }
    }
    }
    entry of /etc/hosts

    ip2 mysql02
    ip3 mysql03

    entry of cnf
    report_host=mysql02,mysql03 // i have added this entry in cnf along the other entries

    log of mysql01

    2017-12-30T18:54:37.366444Z 57 [Note] Access denied for user ‘cluster’@’mysql01’ (using password: YES)
    2017-12-30T19:15:14.309242Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 5f14d225’
    2017-12-30T19:15:18.251396Z 0 [Note] Plugin group_replication reported: ‘Members joined the group: mysql02,mysql03:3306’
    2017-12-30T19:15:18.251613Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to mysql02,mysql03:3306,
    mysql02,mysql03:3306 on view 15146479517855109:6.’
    2017-12-30T19:16:53.197629Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 5f14d225’
    2017-12-30T19:16:56.197680Z 0 [Note] Plugin group_replication reported: ‘Members joined the group: mysql02,mysql02:3306’
    2017-12-30T19:16:56.197877Z 0 [Note] Plugin group_replication reported: ‘Group membership changed to mysql02,mysql03:3306,
    mysql02,mysql03:3306, mysql02,mysql02:3306 on view 15146479517855109:7.’
    2017-12-30T19:17:48.981659Z 59 [Note] Aborted connection 59 to db: ‘unconnected’ user: ‘cluster’ host:
    ‘mysql01’ (Got an error reading communication packets)
    2017-12-30T19:24:18.429576Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 5f14d225’
    2017-12-30T19:24:19.062117Z 0 [Warning] Plugin group_replication reported: ‘Members removed from the group: mysql02,mysql03:3306’
    2017-12-30T19:24:19.062348Z 0 [Note] Plugin group_replication reported:
    ‘Group membership changed to mysql02,mysql03:3306, mysql02,mysql02:3306 on view 15146479517855109:8.’
    2017-12-30T19:25:56.650022Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 5f14d225’
    2017-12-30T19:25:57.067077Z 0 [Warning] Plugin group_replication reported: ‘Members removed from the group: mysql02,mysql02:3306’
    2017-12-30T19:25:57.067276Z 0 [Note] Plugin group_replication reported:
    ‘Group membership changed to mysql02,mysql03:3306 on view 15146479517855109:9.’

    log of mysql 02

    2017-12-30T19:23:18.390087Z 52 [ERROR] Plugin group_replication reported: ‘There was an error when connecting to the donor server. Please check that gro$
    2017-12-30T19:23:18.390110Z 52 [ERROR] Plugin group_replication reported: ‘For details please check performance_schema.replication_connection_status tab$
    2017-12-30T19:23:18.390289Z 52 [Note] Plugin group_replication reported: ‘Retrying group recovery connection with another donor. Attempt 10/10’
    2017-12-30T19:24:18.390769Z 52 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_recovery’ executed’. Previous state master_host=’$
    2017-12-30T19:24:18.398428Z 52 [Note] Plugin group_replication reported: ‘Establishing connection to a group replication recovery donor a23e118d-ed73-11$
    2017-12-30T19:24:18.401836Z 68 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore no$
    2017-12-30T19:24:18.404662Z 68 [ERROR] Slave I/O for channel ‘group_replication_recovery’: error connecting to master ‘mysql_innodb_cluster_rp454634638@$
    2017-12-30T19:24:18.404683Z 68 [Note] Slave I/O thread for channel ‘group_replication_recovery’ killed while connecting to master
    2017-12-30T19:24:18.404690Z 68 [Note] Slave I/O thread exiting for channel ‘group_replication_recovery’, read up to log ‘FIRST’, position 4
    2017-12-30T19:24:18.404818Z 52 [ERROR] Plugin group_replication reported: ‘There was an error when connecting to the donor server. Please check that gro$
    2017-12-30T19:24:18.404838Z 52 [ERROR] Plugin group_replication reported: ‘For details please check performance_schema.replication_connection_status tab$
    2017-12-30T19:24:18.405025Z 52 [ERROR] Plugin group_replication reported: ‘Maximum number of retries when trying to connect to a donor reached. Aborting$
    2017-12-30T19:24:18.405038Z 52 [Note] Plugin group_replication reported: ‘Terminating existing group replication donor connection and purging the corres$
    2017-12-30T19:24:18.405090Z 55 [Note] Error reading relay log event for channel ‘group_replication_recovery’: slave SQL thread was killed
    2017-12-30T19:24:18.416880Z 52 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_recovery’ executed’. Previous state master_host=’$
    2017-12-30T19:24:18.428257Z 52 [ERROR] Plugin group_replication reported: ‘Fatal error during the Recovery process of Group Replication. The server will$
    2017-12-30T19:24:18.429619Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 5f14d225’
    2017-12-30T19:24:21.870550Z 0 [Note] Plugin group_replication reported: ‘state 4410 action xa_terminate’
    2017-12-30T19:24:21.871171Z 0 [Note] Plugin group_replication reported: ‘new state x_start’
    2017-12-30T19:24:21.871188Z 0 [Note] Plugin group_replication reported: ‘state 4337 action xa_exit’
    2017-12-30T19:24:21.871270Z 0 [Note] Plugin group_replication reported: ‘Exiting xcom thread’
    2017-12-30T19:24:21.871281Z 0 [Note] Plugin group_replication reported: ‘new state x_start’
    2017-12-30T19:24:21.871188Z 0 [Note] Plugin group_replication reported: ‘state 4337 action xa_exit’
    2017-12-30T19:24:21.871270Z 0 [Note] Plugin group_replication reported: ‘Exiting xcom thread’
    2017-12-30T19:24:21.871281Z 0 [Note] Plugin group_replication reported: ‘new state x_start’
    2017-12-30T19:24:21.911799Z 0 [Note] Plugin group_replication reported: ‘Group membership changed: This member has left the group.’

    1. Hi Karthick,

      Did you whitelist the server IP you have used in the cluster. Probably you need to run below command in all the server

      set global group_replication_ip_whitelist=”192.168.1.1,192.1681.4″;

      1. Hi Saravan,

        Thank you for your reply. I have added the whitelist as mentioned got the issue fixed.
        One think i just want to reconfirm

        I understand that it is a pre requesties in innodb Every table must have a primary key/relevent key.

        Is there any way/possibility that we can make our innodb cluster to have tables without primary key?

        1. Hello,
          No you can’t ! It’s mandatory and needed to perform the certification.
          Also not having PK in InnoDB can be very bad for performance, certainly if you have many tables without PK, they will all share the same hidden counter and use a mutex on it… something you don’t really want to experience.
          Cheers.

          1. Hi Lefred,

            Compliments of the New year.

            Since our talks, I have since went online with my 3 node innodb cluster, It worked for a while and all of a sudden stopped with the following behaviour:

            1. Apps report not being able to write to DB (MySQL Server went into Read Only Mode)

            mysql> show variables like ‘super%’;
            +—————–+——-+
            | Variable_name | Value |
            +—————–+——-+
            | super_read_only | ON |
            +—————–+——-+
            1 row in set (0.00 sec)

            mysql> show variables like ‘read_only’;
            +—————+——-+
            | Variable_name | Value |
            +—————+——-+
            | read_only | ON |
            +—————+——-+
            1 row in set (0.01 sec)

            2.Cluster Reports following Error:

            mysql-js> var cluster = dba.getCluster()
            WARNING: The session is on a Error instance.
            Write operations in the InnoDB cluster will not be allowed.
            The information retrieved with describe() and status() may be outdated.

            mysql-js> cluster.status()
            {
            “clusterName”: “Zabbix_Cluster”,
            “defaultReplicaSet”: {
            “name”: “default”,
            “status”: “OK_NO_TOLERANCE”,
            “statusText”: “Cluster is NOT tolerant to any failures. 3 members are not active”,
            “topology”: {
            “165.233.206.40:3306”: {
            “address”: “165.233.206.40:3306”,
            “mode”: “R/O”,
            “readReplicas”: {},
            “role”: “HA”,
            “status”: “(MISSING)”
            },
            “165.233.206.41:3306”: {
            “address”: “165.233.206.41:3306”,
            “mode”: “R/O”,
            “readReplicas”: {},
            “role”: “HA”,
            “status”: “(MISSING)”
            },
            “165.233.206.43:3306”: {
            “address”: “165.233.206.43:3306”,
            “mode”: “R/O”,
            “readReplicas”: {},
            “role”: “HA”,
            “status”: “ERROR”
            }
            }
            },
            “warning”: “The instance status may be inaccurate as it was generated from an instance in Error state”

            3. Error Log Show’s this:
            2018-01-19T08:20:34.138842Z 0 [ERROR] Plugin group_replication reported: ‘Member was expelled from the group due to network failures, changing member status to ERROR.’
            2018-01-19T08:20:34.146573Z 0 [Note] Plugin group_replication reported: ‘getstart group_id 20880e0b’
            2018-01-19T08:20:34.910687Z 0 [Warning] Plugin group_replication reported: ‘Due to a plugin error, some transactions can’t be certified and will now rollback.’
            2018-01-19T08:20:34.914135Z 4222153 [ERROR] Plugin group_replication reported: ‘Transaction cannot be executed while Group Replication is on ERROR state. Check for errors and restart the plugin’
            2018-01-19T08:20:34.914166Z 4222153 [ERROR] Run function ‘before_commit’ in plugin ‘group_replication’ failed
            2018-01-19T08:20:34.914303Z 4222147 [ERROR] Plugin group_replication reported: ‘Transaction cannot be executed while Group Replication is on ERROR state. Check for errors and restart the plugin’

  7. I am deploying InnoDB Cluster for production environment.

    while creating did’t get any issue, but while adding instance i am facing some issues.

    mysql-js> cluster.checkInstanceState(‘root@10.10.14.50:3306’)
    Please provide the password for ‘root@10.10.14.50:3306’:
    Analyzing the instance replication state…

    The instance ‘10.10.14.50:3306’ is valid for the cluster.
    The instance is fully recoverable.

    {
    “reason”: “recoverable”,
    “state”: “ok”
    }

    The error getting is:

    mysql-js>cluster.addInstance(‘root@10.10.14.51: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@10.10.14.51:3306’:
    Adding instance to the cluster …

    Cluster.addInstance: WARNING: The given ‘10.10.14.51:3306’ and the peer ‘ubuntu:3306’ have duplicated server_id 1
    ERROR: Error joining instance to cluster: The operation could not continue due to the following requirements not being met:
    The server_id 1 is already used by peer ‘ubuntu:3306’
    The server_id must be different from the ones in use by the members of the GR group. (RuntimeError)

    I am missing any configuration steps? Please help me solving this issue.

    Regards,
    Ankita

    1. Hi Ankita,
      Which version of MySQL and Shell are you using ?
      For the mysql-shell you should use (even with MySQL 5.7.2x) 8.0.11.
      The problem you are having is that in my.cnf you should have different server_id for each members (server_id=1, server_id=2, …)
      Regards,

      1. Hi,
        Mysql version : Ver 8.0.3-rc for Linux on x86_64 (MySQL Community Server (GPL))
        Mysql shell version: Ver 1.0.11 for Linux on x86_64 – for MySQL 5.7.20

        IN my.conf file i have not done any changes:

        # Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
        #
        # This program is free software; you can redistribute it and/or modify
        # it under the terms of the GNU General Public License as published by
        # the Free Software Foundation; version 2 of the License.
        #
        # This program is distributed in the hope that it will be useful,
        # but WITHOUT ANY WARRANTY; without even the implied warranty of
        # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
        # GNU General Public License for more details.
        #
        # You should have received a copy of the GNU General Public License
        # along with this program; if not, write to the Free Software
        # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

        #
        # The MySQL Server configuration file.
        #
        # For explanations see
        # http://dev.mysql.com/doc/mysql/en/server-system-variables.html

        # * IMPORTANT: Additional settings that can override those from this file!
        # The files must end with ‘.cnf’, otherwise they’ll be ignored.
        #

        !includedir /etc/mysql/conf.d/
        !includedir /etc/mysql/mysql.conf.d/

        Where I have to do these changes? I followed the above steps in that it is not mentioned. I am new to this, Can you please help me to solve this?

        Thanks and Regards,
        Ankita

        1. Hi,
          You should not use the Release Candidate but the Latest GA version of MySQL 8.0.11
          Same for the Shell : 8.0.11

          If you do so, it’s very easy, you don’t event need to modify the config file by yourself, the new shell will do it (but in case you want to know, you can do it in one of the file in those directories where [mysqld] section is referenced (I don’t know where ubuntu puts it).

          Check this post: http://lefred.be/content/mysql-shell-for-mysql-8-0-your-best-friends-in-the-cloud/

          Regards,

          1. Hi ,
            I have installed 5.7 since i am using ubuntu 14.04.
            mysql : Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
            mysqlsh : Ver 1.0.11 for Linux on x86_64 – for MySQL 5.7.20 (MySQL Community Server (GPL))

            I have created cluster without any issue. But when i am checking cluster status it is returned below message:

            mysql-js> cluster.status()
            {
            “clusterName”: “prodCluster”,
            “defaultReplicaSet”: {
            “name”: “default”,
            “primary”: “10.10.14.50:3306”,
            “status”: “OK_NO_TOLERANCE”,
            “statusText”: “Cluster is NOT tolerant to any failures. 2 members are not active”,
            “topology”: {
            “10.10.14.50:3306”: {
            “address”: “10.10.14.50:3306”,
            “mode”: “R/W”,
            “readReplicas”: {},
            “role”: “HA”,
            “status”: “ONLINE”
            },
            “10.10.14.51:3306”: {
            “address”: “10.10.14.51:3306”,
            “mode”: “R/O”,
            “readReplicas”: {},
            “role”: “HA”,
            “status”: “RECOVERING”
            },
            “10.10.14.52:3306”: {
            “address”: “10.10.14.52:3306”,
            “mode”: “R/O”,
            “readReplicas”: {},
            “role”: “HA”,
            “status”: “RECOVERING”
            }
            }
            }
            }

            What can be issue?

            Thanks and Regards,
            Ankita

  8. Hi Ankita,

    Even with mysql 5.7 it’s recommended to use mysql shell 8.0.11 😉

    Check in the error log of the nodes in “recovering” why group replication is not started, they might be multiple reasons.

    Regards.

  9. Hi,
    I have installed mysql 5.7.22,When I execute:var cluster=dba.createCluster(‘mycluster’),
    I get a Error:
    Dba.createCluster: ERROR: Error starting cluster: ‘10.16.44.138:3306’ – Query failed. MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log..

    The Details Log :
    2018-05-22T03:18:58.981520Z 67 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_recovery’ executed’. Previous state master_host=”, master_port= 3306, master_log_file=”, master_log_pos= 4, master_bind=”. New state master_host=”, master_port= 3306, master_log_file=”, master_log_pos= 4, master_bind=”.
    2018-05-22T03:18:59.044577Z 67 [Note] Plugin group_replication reported: ‘Group communication SSL configuration: group_replication_ssl_mode: “DISABLED”‘
    2018-05-22T03:18:59.044712Z 67 [Note] Plugin group_replication reported: ‘[GCS] Added automatically IP ranges 10.16.44.138/24,127.0.0.1/8 to the whitelist’
    2018-05-22T03:18:59.044828Z 67 [Warning] Plugin group_replication reported: ‘[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.’
    2018-05-22T03:18:59.044869Z 67 [Note] Plugin group_replication reported: ‘[GCS] SSL was not enabled’
    2018-05-22T03:18:59.044888Z 67 [Note] Plugin group_replication reported: ‘Initialized group communication with configuration: group_replication_group_name: “de28477e-5d6e-11e8-ab11-005056ac54ec”; group_replication_local_address: “10.16.44.138:33061”; group_replication_group_seeds: “”; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: “AUTOMATIC”‘
    2018-05-22T03:18:59.044913Z 67 [Note] Plugin group_replication reported: ‘[GCS] Configured number of attempts to join: 0’
    2018-05-22T03:18:59.044919Z 67 [Note] Plugin group_replication reported: ‘[GCS] Configured time between attempts to join: 5 seconds’
    2018-05-22T03:18:59.044936Z 67 [Note] Plugin group_replication reported: ‘Member configuration: member_id: 3378132610; member_uuid: “dd5fc29c-5d68-11e8-b6c4-005056ac54ec”; single-primary mode: “true”; group_replication_auto_increment_increment: 7; ‘
    2018-05-22T03:18:59.045327Z 69 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_applier’ executed’. Previous state master_host=”, master_port= 0, master_log_file=”, master_log_pos= 4, master_bind=”. New state master_host=”, master_port= 0, master_log_file=”, master_log_pos= 4, master_bind=”.
    2018-05-22T03:18:59.127266Z 72 [Note] Slave SQL thread for channel ‘group_replication_applier’ initialized, starting replication in log ‘FIRST’ at position 0, relay log ‘./mysql1-relay-bin-group_replication_applier.000001’ position: 4
    2018-05-22T03:18:59.127303Z 67 [Note] Plugin group_replication reported: ‘Group Replication applier module successfully initialized!’
    2018-05-22T03:18:59.129793Z 0 [Note] Plugin group_replication reported: ‘XCom protocol version: 3’
    2018-05-22T03:18:59.129818Z 0 [Note] Plugin group_replication reported: ‘XCom initialized and ready to accept incoming connections on port 33061’
    2018-05-22T03:19:09.129685Z 0 [ERROR] Plugin group_replication reported: ‘[GCS] Error connecting to the local group communication engine instance.’
    2018-05-22T03:19:09.154651Z 0 [ERROR] Plugin group_replication reported: ‘[GCS] The member was unable to join the group. Local port: 33061’
    2018-05-22T03:19:59.127485Z 67 [ERROR] Plugin group_replication reported: ‘Timeout on wait for view after joining group’
    2018-05-22T03:19:59.127588Z 67 [Note] Plugin group_replication reported: ‘Requesting to leave the group despite of not being a member’
    2018-05-22T03:19:59.127641Z 67 [ERROR] Plugin group_replication reported: ‘[GCS] The member is leaving a group without being on one.’
    2018-05-22T03:19:59.128279Z 72 [Note] Error reading relay log event for channel ‘group_replication_applier’: slave SQL thread was killed
    2018-05-22T03:19:59.241607Z 69 [Note] Plugin group_replication reported: ‘The group replication applier thread was killed’
    what’s wrong?

    1. You need to configure the member to be compatible with Group Replication. To do so, please use the new MySQL-Shell (8.0.11) even with MySQL 5.7.22 and try dba.ConfigureLocalInstance()

      Regards,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

recent

Last Tweets

Locations of visitors to this page
categories