feat: support ProxySQL --bootstrap mode for InnoDB Cluster / Group Replication

[renecannao]

Summary

ProxySQL has a --bootstrap mode that mirrors MySQL Router's --bootstrap. Instead of generating a static proxysql.cnf with hardcoded hostgroups, ProxySQL can auto-discover the cluster topology, users, and configure itself automatically.

Current behavior

--with-proxysql generates a static config:

• Manually lists servers in mysql_servers with fixed hostgroups
• Manually adds msandbox to mysql_users
• Manually configures mysql_group_replication_hostgroups (for GR/InnoDB Cluster)

Proposed behavior

For InnoDB Cluster and Group Replication topologies, offer a --proxysql-bootstrap flag (or make it the default) that runs:

proxysql --bootstrap msandbox:msandbox@127.0.0.1:<primary_port> \
  --conf-bind-address 127.0.0.1 \
  --conf-base-port 6446 \
  -d <sandbox>/proxysql \
  --account-create if-not-exists

This auto-discovers:

• All cluster members from performance_schema.replication_group_members
• MySQL users from mysql.user (excluding system users)
• Server version, charset, SSL settings
• Creates a monitoring account (bt_proxysql_*)
• Configures port-based R/W split (6446 → writers, 6447 → readers)

Testing required BEFORE implementation

Test 1: Basic bootstrap against InnoDB Cluster

• Deploy InnoDB Cluster with dbdeployer
• Run proxysql --bootstrap msandbox:msandbox@127.0.0.1:<port> -d /tmp/proxysql-test
• Verify: servers discovered, users discovered, hostgroups configured

Test 2: User permissions

• Does msandbox (ALL privileges) have sufficient permissions for bootstrap?
• Does the monitoring account (bt_proxysql_*) get created successfully?
• Are sandbox users (msandbox, msandbox_rw, msandbox_ro) discovered?

Test 3: Connectivity after bootstrap

• Connect through 6446 (R/W) — write succeeds
• Connect through 6447 (R/O) — read succeeds, write fails or routes to writer
• Verify replication: write on 6446, read on 6447

Test 4: Failover

• Stop the primary node
• Verify ProxySQL detects the change and reroutes 6446 to new primary

Test 5: Re-bootstrap

• Run bootstrap again after customizing query rules
• Verify customizations are preserved

Test 6: Bootstrap against Group Replication (non-InnoDB Cluster)

• Deploy --topology=group
• Run bootstrap — does it work without InnoDB Cluster metadata?

Test 7: Comparison with static config

• Deploy same topology twice: once with static --with-proxysql, once with bootstrap
• Compare SELECT * FROM runtime_mysql_servers output
• Compare SELECT * FROM mysql_users output
• Compare failover behavior

Implementation plan (after testing)

1. Add --proxysql-bootstrap flag to replication command
2. When flag is set + topology is GR/InnoDB Cluster:
   • Deploy the cluster first (existing flow)
   • Start ProxySQL with --bootstrap against the primary (instead of generating static config)
   • Generate lifecycle scripts pointing to the bootstrapped ProxySQL instance
3. For standard replication topologies, keep the static config (bootstrap requires GR)
4. Update CI tests to cover bootstrap mode

[coderabbitai]

🔗 Related PRs

#62 - feat: add MySQL 8.4-specific replication and group replication templates (#60) [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!