Mysql 8.x Group Replication (Master-Slave) with Docker Compose

This post is handling the following situation - how to setup up simple Mysql services with group replication being dockerized. In our case, we’ll take the latest Mysql (version 8.x.x)

FYI: all mentioned code (worked and tested manually) located here.

I will skip not interested steps like ‘what is Mysql, Docker and why we choose them, etc’. We want to set up possibly trouble proof DB. That’s our plan.

Let’s start!

1. Docker Compose file

docker-compose.yml

version: '3.5'

services:
	alpha:
  	image: mysql/mysql-server:8.0    
    container_name: alpha    
    hostname: alpha    
    ports:      
    	- '1441:3306'   
    restart: unless-stopped    
    # sets the network address which the member provides for connections from other members,
    # specified as a host:port formatted string.        
    command: [ "mysqld",      
    	"--loose-group-replication-local-address=alpha:6606" ]                   
    environment:      
      MYSQL_ROOT_HOST: '%'            
      MYSQL_USER: root            
      MYSQL_ROOT_PASSWORD: password      
      MYSQL_DATABASE: db_name      
    volumes:        
      - ./configs/my-cnf:/etc/mysql/my.cnf              
      - ./scripts:/docker-entrypoint-initdb.d          
    healthcheck:              
      test: "mysqladmin ping -u root -p$${MYSQL_ROOT_PASSWORD}"               
      interval: 2s             
      retries: 20
      
 ...  
   
   gamma:
  	image: mysql/mysql-server:8.0    
    container_name: gamma    
    hostname: gamma    
    ports:      
    	- '1443:3306'   
    restart: unless-stopped    
    # sets the network address which the member provides for connections from other members,
    # specified as a host:port formatted string.        
    command: [ "mysqld",      
    	"--loose-group-replication-local-address=gamma:6606" ]                   
    environment:      
      MYSQL_ROOT_HOST: '%'            
      MYSQL_USER: root            
      MYSQL_ROOT_PASSWORD: password      
      MYSQL_DATABASE: db_name      
    volumes:        
      - ./configs/my-cnf:/etc/mysql/my.cnf              
      - ./scripts:/docker-entrypoint-initdb.d          
    healthcheck:              
      test: "mysqladmin ping -u root -p$${MYSQL_ROOT_PASSWORD}"               
      interval: 2s             
      retries: 20

So, the main points you must understand since Mysql services have almost identical initialization:

• Different exposed ports ( alpha - 1441:3306, gamma - 1443:3306 )

• Different hostnames ( hostname: alpha, hostname: gamma )

• Different network address ( “--loose-group-replication-local-address={ alpha/beta/gamma/etc }:6606” )

2. Mysql config file

configs/my-cnf

[mysqld]

# To be able to conect to db via Mysql Workbench/SequelPro/other UI tools
# It is not recommended to use the mysql_native_password authentication plugin for new installations that require high password security.
default_authentication_plugin     = mysql_native_password

# Binary log file name (for data recovery after possible mysql server crash).
log_bin                           = mysql-bin-1.log

# The MySQL Server system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs).
enforce_gtid_consistency          = ON

# Controls whether GTID based logging is enabled and what type of transactions the logs can contain/# enforce_gtid_consistency must be true before you can set gtid_mode=ON.
gtid_mode                         = ON

# Whether updates received by a replica from a replication source server should be logged to the replica's own binary log.
log_slave_updates                 = ON

# When binlog_checksum is disabled (value NONE),
# the server verifies that it is writing only complete events to the binary log
# by writing and checking the event length (rather than a checksum) for each event.
binlog_checksum                   = NONE

# The setting of this variable determines whether the replica records source metadata, consisting of status and connection information,
# to an InnoDB table in the mysql system database, or to a file in the data directory.
# default value - TABLE
master_info_repository            = TABLE

# The setting of this variable determines whether the replica server logs its position in the relay logs
# to an InnoDB table in the mysql system database, or to a file in the data directory.
# default value - TABLE
relay_log_info_repository         = TABLE

# This option tells the server to load the named plugins at startup.
# In our case this is group_replication plugin
plugin-load-add                   = group_replication.so

# Load this plugin in case when server bin logs are too far away / removed.
# So group replication makes a clone of the current master (dump) and continue replication as default.
plugin-load-add                   = mysql_clone.so

# If enabled, this variable enables automatic relay log recovery immediately following server startup.
# The recovery process creates a new relay log file, initializes the SQL thread position to this new relay log,
# and initializes the I/O thread to the SQL thread position.
relay_log_recovery                = ON

# For Group Replication, transaction_write_set_extraction must be set to XXHASH64.
# The process of extracting the writes from a transaction is used in Group Replication
# for conflict detection and certification on all group members.
transaction_write_set_extraction  = XXHASH64

# Whether the server should start Group Replication or not during server start.
loose_group_replication_start_on_boot                    = OFF

# The name of the group which this server instance belongs to.# Must be a valid and _uniq_ UUID.
# This UUID is used internally when setting GTIDs for Group Replication events in the binary log.
loose_group_replication_group_name                       = aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

# A list of group members to which a joining member can connect to obtain details of all the current group members.
# The joining member uses these details to select and connect to a group member to obtain the data needed for synchrony with the group.
# The list consists of the seed member's network addresses specified as a comma separated list, such as host1:port1,host2:port2.
loose_group_replication_group_seeds                      = alpha:6606,beta:6606,gamma:6606

# Instructs the group to automatically pick a single server to be the one that handles read/write workload.
# This server is the PRIMARY and all others are SECONDARIES.
loose_group_replication_single_primary_mode              = ON

# Enables or disables strict consistency checks for multi-primary update everywhere.
# The default is that checks are disabled. In single-primary mode, this option must be disabled on all group members.
loose_group_replication_enforce_update_everywhere_checks = OFF

Mysql config file will be the same for all new instances. Every new Mysql service is not ‘slave’ neither ‘master’.

3. Setting up Master instance

I made procedures for setting a slave/master for easier usage.

CALL set_as_master;

Which call next commands:

SET @@GLOBAL.group_replication_bootstrap_group=1;
create user IF NOT EXISTS 'repl'@'%';
GRANT REPLICATION SLAVE ON . TO repl@'%';
flush privileges;
change master to master_user='root' for channel 'group_replication_recovery';
START GROUP REPLICATION;

and the next command

SELECT * FROM performance_schema.replication_group_members;

should return the next results:

4. Setting up Slave instances

CALL set_as_slave;

Which call next commands:

change master to master_user='repl' for channel 'group_replication_recovery';
START GROUP_REPLICATION;
# There should be no possibility to change anything in slaves
# since any inconsistencies will cause an errors with the next replication
SET @@global.read_only=1;

and the next command

SELECT * FROM performance_schema.replication_group_members;

should return the next results:

In this implementation works auto-failover. So if master will go down or become unreachable, some of the slaves will become the next master.

That’s it, you can easily make any changes (create a new table, change rows) in master node and see that all slaves also have the latest data.

Possible disasters

Please mind it is crucial to have mysql_clone plugin (already added in config) available since (potentially new) server might be too far off the master and unable to rewind to the actual binlog position. Having mysql_clone plugin enabled will make sure that server downloads a backup first if needed, thus leaving a comfortable gap for replication lag to catch up.

Mysql Router

For backend side you don’t need to know any addresses of Mysql services inside the replication group. Just address of load balancer (Mysql Router).

Typically, a highly available MySQL setup consists of a single primary and multiple replicas and it is up to the application to handle failover, in case the MySQL primary becomes unavailable. Using MySQL Router, application connections will be transparently routed based on load balancing policy, without implementing custom application code.

https://hub.docker.com/r/mysql/mysql-router