Vagrant — ProxySQL Full Stack Lab

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

  MASTER_IP   = "192.168.56.11"
  REPLICA1_IP = "192.168.56.12"
  REPLICA2_IP = "192.168.56.13"
  PROXY_IP    = "192.168.56.10"

  # MySQL Master
  config.vm.define "master", primary: true do |node|
    node.vm.hostname = "mysql-master"
    node.vm.network "private_network",  ip: MASTER_IP
    node.vm.network "forwarded_port",   guest: 3306, host: 13306
    node.vm.provider "virtualbox" do |vb|
      vb.name="MySQL-Master"; vb.memory=2048; vb.cpus=2
    end
    node.vm.provider "parallels" do |prl|
      prl.name="MySQL-Master"; prl.memory=2048; prl.cpus=2
      prl.update_guest_tools=false
    end
    node.vm.provision "shell", path:"scripts/mysql_replication.sh",
      args: ["master", "1", MASTER_IP, MASTER_IP]
  end

  # Replicas
  [[REPLICA1_IP,"replica1",2,13307],[REPLICA2_IP,"replica2",3,13308]].each do |ip,name,id,port|
    config.vm.define name do |node|
      node.vm.hostname = "mysql-#{name}"
      node.vm.network "private_network",  ip: ip
      node.vm.network "forwarded_port",   guest: 3306, host: port
      node.vm.provider "virtualbox" do |vb|
        vb.name="MySQL-#{name.capitalize}"; vb.memory=2048; vb.cpus=2
      end
      node.vm.provider "parallels" do |prl|
        prl.name="MySQL-#{name.capitalize}"; prl.memory=2048; prl.cpus=2
        prl.update_guest_tools=false
      end
      node.vm.provision "shell", path:"scripts/mysql_replication.sh",
        args: ["replica", id.to_s, ip, MASTER_IP]
    end
  end

  # ProxySQL
  config.vm.define "proxysql" do |node|
    node.vm.hostname = "proxysql"
    node.vm.network "private_network",  ip: PROXY_IP
    node.vm.network "forwarded_port",   guest: 6033, host: 16033
    node.vm.network "forwarded_port",   guest: 6032, host: 16032
    node.vm.provider "virtualbox" do |vb|
      vb.name="ProxySQL"; vb.memory=1024; vb.cpus=1
    end
    node.vm.provider "parallels" do |prl|
      prl.name="ProxySQL"; prl.memory=1024; prl.cpus=1
      prl.update_guest_tools=false
    end
    node.vm.provision "shell", path:"scripts/install_proxysql.sh",
      args: [MASTER_IP, REPLICA1_IP, REPLICA2_IP]
  end
end

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

MASTER_IP=${1:-192.168.56.11}
REPLICA1_IP=${2:-192.168.56.12}
REPLICA2_IP=${3:-192.168.56.13}

echo "=== Installing ProxySQL ==="

# Add ProxySQL repo
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql_repo]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.x.x/centos/8
gpgcheck=0
EOF

dnf install -y proxysql
systemctl enable --now proxysql

echo "=== Configuring ProxySQL ==="

# Wait for ProxySQL to start
sleep 3

# Configure ProxySQL via admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032 << EOF

-- Monitor credentials
UPDATE global_variables SET variable_value='monitor'        WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor@123!'   WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000'           WHERE variable_name='mysql-monitor_connect_interval';
UPDATE global_variables SET variable_value='1000'           WHERE variable_name='mysql-monitor_ping_interval';
UPDATE global_variables SET variable_value='1500'           WHERE variable_name='mysql-monitor_read_only_interval';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Add backend servers
INSERT INTO mysql_servers (hostgroup_id,hostname,port,status,weight,max_connections,comment)
VALUES (10,'${MASTER_IP}',3306,'ONLINE',1,500,'MySQL Master');
INSERT INTO mysql_servers (hostgroup_id,hostname,port,status,weight,max_connections,comment)
VALUES (10,'${REPLICA1_IP}',3306,'ONLINE',1,500,'MySQL Replica 1');
INSERT INTO mysql_servers (hostgroup_id,hostname,port,status,weight,max_connections,comment)
VALUES (10,'${REPLICA2_IP}',3306,'ONLINE',1,500,'MySQL Replica 2');

-- Replication hostgroups
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment)
VALUES (10,20,'read_only','Master-Replica R/W Split');

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- App user
INSERT INTO mysql_users (username,password,default_hostgroup,active,transaction_persistent)
VALUES ('root','Root@123!',10,1,1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- Query rules
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply,comment)
VALUES (1,1,'^SELECT .* FOR UPDATE',10,1,'Locking reads to master');
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply,comment)
VALUES (2,1,'^SELECT',20,1,'All SELECTs to replicas');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

EOF

echo "=== ProxySQL Configured ==="
echo "Connect: mysql -u root -pRoot@123! -h 127.0.0.1 -P 16033"

# Start in order (master first, then replicas, then proxysql)
vagrant up --no-parallel

# Test: connect through ProxySQL
mysql -u root -pRoot@123! -h 127.0.0.1 -P 16033 -e "SELECT @@hostname;"

# Check routing — SELECTs go to replicas
mysql -u root -pRoot@123! -h 127.0.0.1 -P 16033 -e "SELECT @@hostname, @@read_only;"

# Check ProxySQL admin
mysql -u admin -padmin -h 127.0.0.1 -P 16032   -e "SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;"

# Check query routing stats
mysql -u admin -padmin -h 127.0.0.1 -P 16032   -e "SELECT hostgroup, digest_text, count_star FROM stats.stats_mysql_query_digest ORDER BY count_star DESC LIMIT 5;"