How to change MySQL InnoDB cluster from single primary mode to multi primary mode?

Question

My goal is to setup, in a lab environment, a MySQL InnoDB cluster in multi-primary mode.

I have read much, but I cannot get this setup to work.

Ubuntu 16.04.3 server LTS x 3 SQL0 - SQL2

Ubuntu 16.04.3 server LTS x 2 REP0 - REP1

All server instances are in Virtual machine except REP0 is on bare metal and REP1 is off line.

Installed MySQL server 8.0 from Oracle APT repo using mysql-apt-config_0.8.10-1_all.deb.

These servers have been up and down with many config changes trying to find a setup that works.

Set MySQL server 8.0 with Legacy Authentication Method on all boxes.

My current /etc/mysql/my.cnf is based on Justin Ellingwood @ DigitalOcean.

This is /etc/mysql/my.cnf from SQL0. This machine was at one time the primary in a single-primary setup that did not work correctly.

The other /etc/mysql/my.cnf file are very similar to this one with the # Host specific replication configuration section changed.

The error I am trying to resolve now is from /var/log/mysql/error.log.

[ERROR] Plugin group_replication reported: 'It is not allowed to run single primary mode with 'enforce_update_everywhere_checks' enabled.'

Yes I do have these set in my.cnf because I want multi-primary mode. I do not want to run in single-primary mode.

loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

Where, How, What else do I need to change for these to be in multi-primary mode and not error out?

Answer 1

Any reason why you're not using MySQL Shell and its AdminAPI to set up and manage your InnoDB cluster?

You can create a multi-primary cluster very easily using the AdminAPI. Example:

var cluster = dba.createCluster("myCluster", {multiPrimary: true})

Also, if you already have a running Group Replication group that you'd like to manage using the Shell you can simply "adopt" it:

var cluster = dba.createCluster("myCluster", {adoptFromGR: true})

And since 8.0.14, you can change a running InnoDB cluster from single-primary mode to multi-primary mode "live". The AdminAPI provides a function for that operation:

<Cluster.>switchToMultiPrimaryMode()>

Also, it is possible to switch back a running cluster to single-primary mode:

<Cluster.>switchToSinglePrimaryMode([instance])

And even to elect a member of the cluster to be the new primary:

<Cluster.>setPrimaryInstance(instance)

Please take a look at the blog post that announced those new features:

https://mysqlserverteam.com/mysql-innodb-cluster-changing-cluster-topology-modes-live/

And for further information check the official userguide.

Cheers,

Miguel

Answer 2

This is a HACK to get multi-master cluster to work.

This is NOT a good answer, but I have a working cluster...

First I deleted the 3 VM and re-created them. Used the instructions from Justin Ellingwood @ DigitalOcean.

...because these were fresh installs the test database read/write on all instances.

On REP0 the bare metal machine.

sudo apt-get --purge remove sql*

yes to remove tables

sudo apt-get update

do not see Oracle repos...

readd repos

sudo dpkg -i /opt/mysql_InnoDB_cluster/mysql-apt-config_0.8.10-1_all.deb

sudo apt-get update

repos exist now.

sudo apt-get install mysql-server mysql-shell

SELECT: Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)

At this point used the instructions from Justin Ellingwood @ DigitalOcean.

REP0 is now a read/write member.

This does NOT answer the question.

What tables need to be emptied, deleted, or recreated to be able to change from single-master to multi-master cluster?