Vagrant — MySQL Group Replication Lab

Vagrant.configure("2") do |config|
  config.vm.box              = "generic/oracle8"
  config.vm.box_check_update = false

  MGR_NODES = [
    { name: "mgr1", ip: "192.168.56.51", id: 1, port: 13311 },
    { name: "mgr2", ip: "192.168.56.52", id: 2, port: 13312 },
    { name: "mgr3", ip: "192.168.56.53", id: 3, port: 13313 },
  ]

  SEEDS = MGR_NODES.map{|n| "#{n[:ip]}:33061"}.join(",")

  MGR_NODES.each do |node|
    config.vm.define node[:name] do |vm|
      vm.vm.hostname = node[:name]
      vm.vm.network "private_network",  ip: node[:ip]
      vm.vm.network "forwarded_port",   guest: 3306, host: node[:port]

      vm.vm.provider "virtualbox" do |vb|
        vb.name   = "MySQL-MGR-#{node[:name]}"
        vb.memory = 2048
        vb.cpus   = 2
      end

      vm.vm.provider "parallels" do |prl|
        prl.name   = "MySQL-MGR-#{node[:name]}"
        prl.memory = 2048
        prl.cpus   = 2
        prl.update_guest_tools = false
      end

      vm.vm.provision "shell",
        path: "scripts/mgr_setup.sh",
        args: [node[:name], node[:ip], node[:id].to_s, SEEDS]
    end
  end
end

#!/bin/bash
# scripts/mgr_setup.sh
set -e

NODE_NAME=${1:-mgr1}
MY_IP=${2:-192.168.56.51}
SERVER_ID=${3:-1}
GROUP_SEEDS=${4:-"192.168.56.51:33061,192.168.56.52:33061,192.168.56.53:33061"}
ROOT_PASS="Root@123!"
MGR_PASS="MGRpass@123!"

# Fixed Group Replication UUID (same for all nodes)
GROUP_NAME="aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb"

echo "=== Setting up MySQL Group Replication on ${NODE_NAME} ==="

# Install MySQL 8
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm 2>/dev/null
dnf module disable -y mysql 2>/dev/null
dnf install -y mysql-community-server

systemctl enable --now mysqld
TEMP_PASS=$(grep "temporary password" /var/log/mysqld.log | tail -1 | awk "{print $NF}")

# Change root password
mysql --connect-expired-password -u root -p"$TEMP_PASS"   -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '${ROOT_PASS}';"

# Write MGR configuration
cat > /etc/my.cnf.d/mgr.cnf << EOF
[mysqld]
server-id              = ${SERVER_ID}
bind-address           = 0.0.0.0
gtid_mode              = ON
enforce_gtid_consistency = ON
log_bin                = /var/log/mysql/mysql-bin.log
binlog_format          = ROW
log_slave_updates      = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64

# Group Replication settings
plugin-load-add        = group_replication.so
group_replication_group_name          = "${GROUP_NAME}"
group_replication_start_on_boot       = OFF
group_replication_local_address       = "${MY_IP}:33061"
group_replication_group_seeds         = "${GROUP_SEEDS}"
group_replication_bootstrap_group     = OFF
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF
EOF

systemctl restart mysqld

# Setup users
mysql -u root -p"$ROOT_PASS" << EOF
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '${ROOT_PASS}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
CREATE USER IF NOT EXISTS 'mgr_user'@'%' IDENTIFIED BY '${MGR_PASS}';
GRANT REPLICATION SLAVE ON *.* TO 'mgr_user'@'%';
CREATE USER IF NOT EXISTS 'monitor'@'%' IDENTIFIED BY 'Monitor@123!';
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

-- Set replication channel for group replication
CHANGE MASTER TO MASTER_USER='mgr_user', MASTER_PASSWORD='${MGR_PASS}'
  FOR CHANNEL 'group_replication_recovery';
EOF

# Bootstrap group on first node only
if [ "$NODE_NAME" = "mgr1" ]; then
    mysql -u root -p"$ROOT_PASS" << EOF
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
EOF
    echo "=== mgr1: Group bootstrapped ==="
else
    # Wait for mgr1 to be ready then join
    echo "Waiting for mgr1 to bootstrap group..."
    sleep 20
    mysql -u root -p"$ROOT_PASS" -e "START GROUP_REPLICATION;"
    echo "=== ${NODE_NAME}: Joined group ==="
fi

# Show group members
mysql -u root -p"$ROOT_PASS"   -e "SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;"

echo "=== ${NODE_NAME} Group Replication setup complete ==="

# Start lab (no-parallel)
vagrant up --no-parallel

# Check group members on any node
vagrant ssh mgr1 -c "mysql -u root -pRoot@123! -e   'SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE
   FROM performance_schema.replication_group_members;'"

# Test write on primary, verify on secondaries
vagrant ssh mgr1 -c "mysql -u root -pRoot@123! -e   'CREATE DATABASE IF NOT EXISTS testdb;
   USE testdb;
   CREATE TABLE IF NOT EXISTS t1 (id INT, val VARCHAR(50));
   INSERT INTO t1 VALUES (1, "from_mgr1");'"

# Verify on secondary
vagrant ssh mgr2 -c "mysql -u root -pRoot@123! -e   'SELECT * FROM testdb.t1;'"

# Test failover — stop primary
vagrant ssh mgr1 -c "sudo systemctl stop mysqld"

# MGR auto-elects new primary
vagrant ssh mgr2 -c "mysql -u root -pRoot@123! -e   'SELECT MEMBER_HOST, MEMBER_ROLE
   FROM performance_schema.replication_group_members;'"