Setting up MySQL group replication in docker

mysql-group-replication-docker.sh

#!/bin/bash

#
# This will create and setup a MySQL group replication cluster with a given number of members.
# Set the number of members below, and then execute this file. You probably want at least 3 members.
#
# This will create volumes and containers named mysql-X, where X is the number of the member.
# If you currently have volumes with the same name, these might be destroyed!
#
MEMBERS=3


# define environement variables
MYSQL_REPLICATION_USER=replication
MYSQL_REPLICATION_PASSWORD="MyReplPass"

#
# Let's get started
#

# prep config template file
cat <<'EOF' > server.cnf
[mysqld]
server_id=${I} 
bind-address=0.0.0.0
gtid_mode=ON 
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
group_replication_single_primary_mode=OFF
loose-group_replication_group_name="00000000-0000-0000-0000-000000000001"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.77.${I}:33061"
loose-group_replication_group_seeds="192.168.77.1:33061, 192.168.77.2:33061, 192.168.77.3:33061"
loose-group_replication_bootstrap_group=OFF
report_host=192.168.77.${I}
EOF

#
# Prepare docker network, volumes and containers
# 
# Since the MySQL docker image does some initialisation the first time, we can't immediately inject the config file
# with the replication plugin. So we start the container with a blank volume, let it init, stop it and recreate it
# with the initialised volume mounted and with the config file.
#
# In a non-docker environment, the first step would basically be your apt-get install mysql-server, after which you 
# change the config file and restart.
#

echo "Preparing some docker things..."
docker network create mysql-group-replication --subnet 192.168.76.0/23
for I in $(seq 1 $MEMBERS)
do
    echo "Volume $I"
    docker volume create mysql-$I

    echo "Container $I"
    docker run -d --rm --network mysql-group-replication -e "MYSQL_ALLOW_EMPTY_PASSWORD=yes" \
    -v mysql-$I:/var/lib/mysql \
    --ip 192.168.77.$I \
    --name mysql-$I \
    mysql:8

done

for I in $(seq 1 $MEMBERS)
do
    echo "Stopping and recreating mysql-$I with config file"
    docker stop mysql-$I

    # prep config file through envsubst
    I=$I envsubst < server.cnf > server-$I.cnf

    # run container with newly create config file
    docker run --rm -d --network mysql-group-replication -e "MYSQL_ALLOW_EMPTY_PASSWORD=yes" \
    -v mysql-$I:/var/lib/mysql \
    -v $PWD/server-$I.cnf:/etc/mysql/conf.d/server.cnf \
    --ip 192.168.77.$I \
    --name mysql-$I \
    mysql:8

done

echo "Wait a bit to make sure all servers are up and running"
sleep 5

#
# Setup replication in servers
#
for I in $(seq 1 $MEMBERS)
do

    echo "Create replication user on server $I..."
    docker exec -ti mysql-$I mysql -uroot -e \
    "SET SQL_LOG_BIN=0;
    CREATE USER '${MYSQL_REPLICATION_USER}'@'%' IDENTIFIED WITH mysql_native_password BY '${MYSQL_REPLICATION_PASSWORD}';
    GRANT REPLICATION SLAVE ON *.* TO '${MYSQL_REPLICATION_USER}'@'%';
    SET SQL_LOG_BIN=1;
    CHANGE MASTER TO MASTER_USER='${MYSQL_REPLICATION_USER}', MASTER_PASSWORD='${MYSQL_REPLICATION_PASSWORD}' FOR CHANNEL 'group_replication_recovery';
    "

    #
    # The first server in the cluster need to bootstrap the cluster
    #
    if [ "$I" == "1" ]
    then
    
        #
        # Create a database, table and some records before cluster was created
        #
        echo "First server, create some content before setting up replication..."
        docker exec -ti mysql-$I mysql -uroot -e \
        "CREATE DATABASE mydatabase1;
        USE mydatabase1;
        CREATE TABLE mytable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname varchar(20), lastname varchar(20)) ENGINE=InnoDB;
        INSERT INTO mytable (firstname, lastname) VALUES('John before', 'Doe');
        INSERT INTO mytable (firstname, lastname) VALUES('Jane before', 'Doe');
        "

        #
        # Set bootstrap on and start replication
        #
        echo "Bootstrap cluster..."
        docker exec -ti mysql-$I mysql -uroot -e \
        "SET GLOBAL group_replication_bootstrap_group=ON;
        START GROUP_REPLICATION;
        SET GLOBAL group_replication_bootstrap_group=OFF;
        "
        
        #
        # Create a database, table and some records after cluster was created
        #
        echo "First server, create some content after cluster was created..."
        docker exec -ti mysql-$I mysql -uroot -e \
        "CREATE DATABASE mydatabase2;
        USE mydatabase2;
        CREATE TABLE mytable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname varchar(20), lastname varchar(20)) ENGINE=InnoDB;
        INSERT INTO mytable (firstname, lastname) VALUES('John after', 'Doe');
        INSERT INTO mytable (firstname, lastname) VALUES('Jane after', 'Doe');
        "

    else
        #
        # Cluster already bootstrapped, just join
        #
        echo "Joining cluster..."
        docker exec -ti mysql-$I mysql -uroot -e \
        "START GROUP_REPLICATION;"
    fi

done

echo "Wait a bit to give all members the opportunity to catch up"
sleep 5

echo "Checking cluster status"
docker exec -ti mysql-1 mysql -uroot -e \
"SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members;"


for I in $(seq 1 $MEMBERS)
do
    echo "Inserting some data on server $I"
    docker exec -ti mysql-$I mysql -uroot mydatabase1 -e \
    "INSERT INTO mytable (firstname, lastname) VALUES('Server $I before', 'Doe')"

    docker exec -ti mysql-$I mysql -uroot mydatabase2 -e \
    "INSERT INTO mytable (firstname, lastname) VALUES('Server $I after', 'Doe')"
done

for I in $(seq 1 $MEMBERS)
do
    echo "Checking mydatabase1.mytable on server $I"
    docker exec -ti mysql-$I mysql -uroot mydatabase1 -e \
    "SELECT * from mytable"
    echo "Checking mydatabase2.mytable on server $I"
    docker exec -ti mysql-$I mysql -uroot mydatabase2 -e \
    "SELECT * from mytable"
done

#
# Show cluster status again
#
echo "Checking cluster status"
docker exec -ti mysql-1 mysql -uroot -e \
"SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members;"

#
# Done, keep it running so you can manually inspect and test things if needed
#
echo "You should see the same data on each server. We'll keep the setup running so you can manually interact with it."
read -p "When done, press the enter key to stop and delete the containers and volumes."

echo "Shutting down environment..."
for I in $(seq 1 $MEMBERS)
do
    docker stop mysql-$I
    docker volume rm mysql-$I
done
docker network rm mysql-group-replication

echo "Done"