Created
September 24, 2021 06:39
-
-
Save TomCan/aeaf9a5e9f92836282dd80261e9c56df to your computer and use it in GitHub Desktop.
Setting up MySQL group replication in docker
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment