Multi-Primary sandbox deploy
with MySQL InnoDB Cluster
MySQL InnoDB cluster is an architecture that provides a complete High Availability solution for MySQL.
An InnoDB cluster is composed by several nodes hosting a MySQL database
aligned with Group Replication and accessed
through MySQL Router acting as a proxy.
InnoDB Cluster default configuration is Single Primary:
the primary member can perform both read and write while other cluster
members allow read operations only.
In the Multi Primary configuration all members can perform both read and write operations.
Even if Single Primary is the suggested configuration there are
certain cases in which the Multi Primary
configuration is worth considering.
In this page we show both Single-Primary and Multi-Primary InnoDB Cluster
configuration using sandbox MySQL Instances.
Sandbox instances are NOT for Production use.
This page presents the scripts used to configure a Test
environment in a timely fashion.
Single Primary Configuration
Single Primary setup is straightforward...
and well documented in the
official site.
Use this script with MySQL Shell (command: mysqlsh) to deploy a Single-Primary InnoDB Cluster
on Sandbox instances:
dba.deploySandboxInstance(3310, {password: 'xxx'});
dba.deploySandboxInstance(3320, {password: 'xxx'});
dba.deploySandboxInstance(3330, {password: 'xxx'});
shell.connect('root@localhost:3310', 'xxx');
var cluster = dba.createCluster("SPClu");
cluster.addInstance({user: "root", host: "localhost", port: 3320, password: 'xxx'});
cluster.addInstance({user: "root", host: "localhost", port: 3330, password: 'xxx'});
For Read-Write connections You must connect to the primary member.
Use this query to find out who is the primary member:
SELECT member_id, member_host, member_port
FROM performance_schema.global_status
JOIN performance_schema.replication_group_members
WHERE VARIABLE_NAME= 'group_replication_primary_member'
AND member_id=variable_value;
MySQL Router bootstrap command automatically configures port 6446
for Read-Write connections and port 6447 for Read-Only connections.
Multi Primary Configuration
Multi Primary setup is not the suggested one.
Can be slower with DML transactions and has more
limitations
(eg. cascading constraints and DDL)
than Single Primary configuration.
But it's cool since all Members are Equal ;-)
Here is the script to deploy a Sandbox Multi-Primary InnoDB Cluster:
dba.deploySandboxInstance(3410, {password: 'xxx'});
dba.deploySandboxInstance(3420, {password: 'xxx'});
dba.deploySandboxInstance(3430, {password: 'xxx'});
\connect root:xxx@localhost:3410;
var cluster = dba.createCluster('MPClu', {multiMaster: true, force: true})
cluster.addInstance({user: "root", host: "localhost", port: 3420, password: 'xxx'});
cluster.addInstance({user: "root", host: "localhost", port: 3430, password: 'xxx'});
For Read-Write connections You can connect to any cluster member.
Use this query to find out who are the available members:
SELECT member_id, member_host, member_port
FROM performance_schema.replication_group_members
WHERE member_state='ONLINE';
MySQL Router bootstrap command automatically configures port 6446
for all connections.
It is also possible create a complex Group Replication configuration
manually and then import it into the InnoDB Cluster:
dba.deploySandboxInstance(3510, {password: 'xxx'});
...
\connect root:xxx@localhost:3510;
\sql
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
set global group_replication_single_primary_mode=off;
set global group_replication_enforce_update_everywhere_checks=ON;
\js
var cluster = dba.createCluster('AlienClu', {adoptFromGR: true})
...
/etc
Enjoy!