MySQL Replica + ProxySQL POC

Local proof of concept for MySQL master/replica replication with ProxySQL in front doing read/write split. A small Flask UI writes and reads through ProxySQL so you can see which backend handled each query.

TL;DR:

The repository is available here. No .env needed for the demo — credentials are hardcoded on purpose. Just run:

docker compose up --build

Then open http://localhost:8080.

Technical details

Stack: MySQL 8 (GTID replication) + ProxySQL + Flask (Python).

• Master (`server-id=1`) — writes / binlog source
• Replica (`server-id=2`, `read_only=ON`) — reads
• ProxySQL — hostgroup 10 for writes, hostgroup 20 for reads
• Web UI on `:8080` — inserts and lists messages via ProxySQL `:6033`

Architecture

Apps never talk to MySQL directly. Everything goes through ProxySQL, which routes `SELECT` to the replica and everything else to the master.

                 ┌─────────────┐
                 │  web :8080  │  INSERT / SELECT
                 └──────┬──────┘
                        │ :6033
                 ┌──────▼──────┐
                 │  ProxySQL   │
                 └───┬─────┬───┘
           writes    │     │    reads
         (hg 10)     │     │   (hg 20)
              ┌──────▼─┐ ┌─▼──────────┐
              │ master │ │  replica   │
              │ :3306  │ │   :3307    │
              └────────┘ └────────────┘
                    replication (GTID)

Folder structure

The repository is organized as follows:

poc-proxysql/
|-- docker-compose.yml
|-- master/
|   |-- Dockerfile
|   |-- init.sql
|   |-- conf.d/mysqld.cnf
|-- replica/
|   |-- Dockerfile
|   |-- setup-replication.sh
|   |-- conf.d/mysqld.cnf
|-- proxysql/
|   |-- proxysql.cnf
|-- apps/
|   |-- web.py
|   |-- db.py
|   |-- templates/
|   |-- static/
|   |-- requirements.txt
|   |-- Dockerfile
|-- README.md

Demo credentials

These are only for local learning. Do not reuse them anywhere real.

root / rootpass          # MySQL admin
repl_user / repl_pass    # replication
monitor / monitor        # ProxySQL healthchecks
app_user / app_pass      # app (DB demo)
admin / admin            # ProxySQL admin (:6032)

How to up and running

Clone this repository and run:

docker compose up --build

First boot takes a bit: master initializes the schema, the replica auto-configures itself with GTID (`SOURCE_AUTO_POSITION=1`), ProxySQL starts, then the web UI comes up.

How to use it

Open the UI at http://localhost:8080:

• Write a message → should land on the master (`server_id=1`)
• The list refreshes by itself → should read from the replica (`server_id=2`)

If you see that split, the read/write routing is working:

[WRITE] id=3 en mysql-master (server_id=1): ...
[READ]  desde mysql-replica (server_id=2) — últimos mensajes

Useful commands

Check replica status (`Replica_IO_Running`, `Replica_SQL_Running`, `Seconds_Behind_Source`):

docker exec -it mysql-replica mysql -uroot -prootpass -e "SHOW REPLICA STATUS\G"

Backends ProxySQL currently sees:

docker exec -it proxysql mysql -h127.0.0.1 -P6032 -uadmin -padmin \
  -e "SELECT hostgroup_id, hostname, status, comment FROM runtime_mysql_servers;"

Query rules (what goes to which hostgroup):

docker exec -it proxysql mysql -h127.0.0.1 -P6032 -uadmin -padmin \
  -e "SELECT rule_id, match_digest, destination_hostgroup, apply FROM runtime_mysql_query_rules;"

Reset from scratch

The replica setup script only runs when the datadir is empty. If something got stuck:

docker compose down -v
docker compose up --build

Limitations (on purpose)

• Single replica
• Plaintext demo credentials
• No SSL, no real failover, no tuning
• ProxySQL split is simple SQL regex — odd queries are out of scope