Body
MySQL HA Lab MySQL HA Lab · MySQL · Docker

MySQL HA Lab, Lesson 1: Environment, Credentials and SQL Helpers

Set up the MySQL HA lab: one .env for every credential, shell helpers that run SQL through throwaway containers, the timeouts that keep tests from hanging, and least-privilege users.

MySQL HA Lab Lesson 1: Environment, Credentials and SQL Helpers
📘
This post is part of the MySQL HA Lab series: a hands-on MySQL 8.4 InnoDB Cluster on Docker. Code: mytechbytes/mysql-ha-lab. Previous: Series introduction.

Everything in the lessons runs as plain SQL through the mysql client, with users and passwords taken from .env. One command per terminal sets it up: source scripts/helpers.sh.


.env: all names and credentials

Docker Compose reads .env automatically and substitutes ${VARIABLE} in docker-compose.yml. Create it from the template:

cp .env.example .env      # then set real passwords; .env is git-ignored
VariableUsed byPurpose
MYSQL_ROOT_PASSWORDMySQL image, cluster-init, monitoring-init, mrootPassword of root@'%' on every node. Set only when the data volume is first created; changing it later does not change the existing password.
CLUSTER_ADMINcluster-init, routers, mysqlshName of the cluster administration account that dba.configureInstance() creates
CLUSTER_ADMIN_PASSWORDsameIts password
CLUSTER_NAMEcluster-initName passed to dba.createCluster()
NETWORK_CLUSTER_NAMECompose network, helpersReal Docker network name, so docker run --network ... can join it
APP_USER / APP_PASSWORDm, w, txLeast-privilege account for test data in app.*
EXPORTER_USER / EXPORTER_PASSWORDmonitoring-init, exportersRead-only account for metrics (Lesson 4)
GRAFANA_ADMIN_PASSWORDGrafanaPassword of the Grafana admin login
LABAPP_SPRINGBOOT_IMAGE / LABAPP_GO_IMAGECompose labapp-* servicesImage names for the lab apps (Lesson 8)
LABAPP_SPRINGBOOT_DB_USER / _PASSWORDlabapp-springbootIts own account, only on schema labapp_springboot
LABAPP_GO_DB_USER / _PASSWORDlabapp-goIts own account, only on schema labapp_go

scripts/helpers.sh: one command per terminal

All tests use a few shell functions. They live in scripts/helpers.sh; load it in every new terminal (bash or zsh):

source scripts/helpers.sh
helpers: client.cnf generated from .env
helpers: loaded (user=app, network=mySqlClusterNetwork, timeout=15s). Run 'mhelp' for the list.

What it does when loaded:

StepDetail
Finds the project folderFrom the script's own location, so it works from any directory
Loads .envset -a; . .env; set +a exports every variable ($APP_USER, $NETWORK_CLUSTER_NAME, ...) to your shell
Generates client.cnfOnly if it's missing or .env is newer, so a changed password is picked up automatically
zshTurns on interactivecomments, so pasted blocks with # comments work
Defines the functionsTable below

The functions

FunctionWhat it doesExample
m ...mysql as the app user through the load balancer (mysql-router:6446)m -e "SELECT 1"
mroot ...mysql as root; add -h mysqlN -P 3306 to talk to one node directly, bypassing the routersmroot -h mysql2 -P 3306 -e "SELECT @@server_id"
q [port]Hostname of the node that answers: 6446 = primary, 6447 = a secondaryq 6447
w "SQL"Runs SQL on the primary as the app userw "SELECT COUNT(*) FROM app.t"
tx NOne test insert; prints OK with the committing node, or FAIL with the errortx 1
txloopEndless tx loop, appended to tx.log (Ctrl+C to stop)txloop
members [node]Group membership as one node sees it (default mysql1)members mysql2
mstatus [node]Full cluster status from MySQL Shellmstatus mysql2
mcleanKills helper containers that are still running (stuck calls)mclean
mhelpPrints this list

How a call works

flowchart LR
    env[".env"] -->|"source scripts/helpers.sh"| sh["Your shell<br/>variables + functions"]
    env --> cnf["client.cnf<br/>generated, git-ignored"]
    sh -->|"m / w / tx"| ctr["Throwaway mysql:8.4 container<br/>--rm · label mysql-ha.client=1"]
    cnf -. "mounted read-only" .-> ctr
    ctr -->|"connect-timeout 5 s · timeout 15 s"| lb["mysql-router:6446<br/>HAProxy"]
    lb --> rt["MySQL Router"]
    rt --> pri[("PRIMARY")]

How one helper call reaches the database: credentials come from .env, the call runs in a throwaway container with two timeouts.

Every m/mroot call runs a throwaway mysql:8.4 container on the cluster network:

docker run --rm --label mysql-ha.client=1 --network "$NETWORK_CLUSTER_NAME" \
  -v "$MH_DIR/client.cnf:/client.cnf:ro" mysql:8.4 \
  timeout "$MH_TIMEOUT" mysql --defaults-extra-file=/client.cnf --connect-timeout=5 ...
PartWhy
--rmContainer is deleted when the command ends
--label mysql-ha.client=1Marks helper containers, so mclean can find stuck ones without touching the cluster's own containers
--networkJoins the cluster network, so mysql-router and mysql1..3 resolve. Works even when a node is down.
-v client.cnfThe login file, read-only
--defaults-extra-fileMust be the first mysql option; --defaults-group-suffix=_root (in mroot) right after it
--connect-timeout=5Gives up if a connection can't be opened in 5s
timeout $MH_TIMEOUTKills the call after 15s (default) even if it hangs after connecting

Why the timeouts matter. docker kill removes a node without closing its connections. A call that starts during a failover can be handed a connection to the dead primary and wait for an answer that never comes: the mysql client has no time limit by default, and HAProxy keeps idle connections for an hour (timeout client/server 1h). Without a timeout, one stuck tx freezes the whole loop even though the cluster has long failed over. Real applications avoid this the same way: connection pools set connect and socket timeouts. Change the limit with MH_TIMEOUT=30 before calls that run longer.

client.cnf

Generated by helpers.sh (git-ignored). Never put passwords on a command line: mysql -p<password> prints "Using a password on the command line interface can be insecure".

[client]                 # default login: app user, through the load balancer
user=app
password=...
host=mysql-router
port=6446

[client_root]            # read in addition when --defaults-group-suffix=_root is given
user=root
password=...
LineMeaning
[client]Group read by every MySQL client program
user, passwordLogin used when none is given on the command line
host=mysql-router, port=6446Default target: HAProxy's read/write port (Lesson 5). Override with -h/-P.
[client_root]Only read with --defaults-group-suffix=_root. Its user/password override [client], so one file serves both logins.

For mysqlsh (used by mstatus) the password goes through standard input instead: echo "$CLUSTER_ADMIN_PASSWORD" | docker exec -i mysql1 mysqlsh --passwords-from-stdin ....


Database users

AccountPurposePrivilegesCreated by
rootSetup onlyEverythingMySQL image (MYSQL_ROOT_PASSWORD, MYSQL_ROOT_HOST=%)
clusteradminCluster managementGroup Replication, clone, metadata, user creation. No rights on application data.dba.configureInstance() in cluster-init
exporterRead-only metricsPROCESS, REPLICATION CLIENT, SELECT on *.*monitoring-init (Lesson 4)
appTest dataSELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER on app.* onlycluster-init (automatic)
labapp_springbootSpring Boot lab appSame rights, on labapp_springboot.* onlycluster-init (automatic)
labapp_goGo lab appSame rights, on labapp_go.* onlycluster-init (automatic)

clusteradmin can create the app database but gets ERROR 1142 ... INSERT command denied when writing to it. That's intended: data gets its own least-privilege account.

Users are created once, on the primary (through the router on port 6446). Group Replication copies them to every node.

Application users: created automatically

cluster-init creates every application schema and user at the end of each run (scripts/init-cluster.js, step 5), with the names and passwords from .env:

SchemaUser (from .env)Used by
app (+ table app.t)APP_USER / APP_PASSWORDshell helpers m, w, tx, txloop
labapp_springbootLABAPP_SPRINGBOOT_DB_USER / _PASSWORDSpring Boot lab app
labapp_goLABAPP_GO_DB_USER / _PASSWORDGo lab app

It is safe to repeat: missing schemas and users are created, existing passwords are re-synced with .env. After changing a password in .env, just run it again:

docker compose up -d cluster-init
docker logs --tail 100 -f cluster-init      # "App account ...: user ... ready"

A variable missing from .env is reported as skipped. The log line App accounts NOT created means the cluster had no primary at that moment; run it again.

Manual fallback: create the app user and the test table

Only needed if cluster-init could not do it. Once the cluster is running (Lesson 2):

mroot -e "
  CREATE DATABASE IF NOT EXISTS app;
  CREATE USER IF NOT EXISTS '$APP_USER'@'%' IDENTIFIED BY '$APP_PASSWORD';
  ALTER USER '$APP_USER'@'%' IDENTIFIED BY '$APP_PASSWORD';
  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON app.* TO '$APP_USER'@'%';"

w "CREATE TABLE IF NOT EXISTS app.t (id INT AUTO_INCREMENT PRIMARY KEY, v INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"
  • ALTER USER makes the command safe to rerun: it resyncs the password with .env.
  • Every table needs a primary key; Group Replication rejects writes to tables without one.

Verify

w "SELECT @@hostname, CURRENT_USER()"     # primary's name, app@%
tx 1                                      # OK ... node=<primary>

Manual fallback: create the lab app users

Normally done by cluster-init (above). Each lab app (Lesson 8) gets its own schema and its own user, so their data, connections and grants never mix with each other or with the app schema of the helpers. The apps create their tables themselves at startup.

mroot -e "
  CREATE DATABASE IF NOT EXISTS labapp_springboot;
  CREATE USER IF NOT EXISTS '$LABAPP_SPRINGBOOT_DB_USER'@'%' IDENTIFIED BY '$LABAPP_SPRINGBOOT_DB_PASSWORD';
  ALTER USER '$LABAPP_SPRINGBOOT_DB_USER'@'%' IDENTIFIED BY '$LABAPP_SPRINGBOOT_DB_PASSWORD';
  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON labapp_springboot.* TO '$LABAPP_SPRINGBOOT_DB_USER'@'%';

  CREATE DATABASE IF NOT EXISTS labapp_go;
  CREATE USER IF NOT EXISTS '$LABAPP_GO_DB_USER'@'%' IDENTIFIED BY '$LABAPP_GO_DB_PASSWORD';
  ALTER USER '$LABAPP_GO_DB_USER'@'%' IDENTIFIED BY '$LABAPP_GO_DB_PASSWORD';
  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON labapp_go.* TO '$LABAPP_GO_DB_USER'@'%';"

mroot -e "SELECT user, host FROM mysql.user WHERE user LIKE 'labapp%'"

Which app holds which connections, per node (both apps set program_name as a connection attribute):

for n in mysql1 mysql2 mysql3; do
  mroot -h $n -P 3306 -N -e "SELECT @@hostname, ATTR_VALUE, COUNT(*) FROM performance_schema.session_connect_attrs
                             WHERE ATTR_NAME = 'program_name' AND ATTR_VALUE LIKE 'labapp%' GROUP BY ATTR_VALUE"
done

Transaction loop

sequenceDiagram
    participant L as txloop
    participant C as Helper container
    participant H as HAProxy / Router
    participant P as Primary
    loop every second
        L->>C: tx N (new connection)
        C->>H: INSERT INTO app.t
        alt primary available
            H->>P: forward
            P-->>C: OK
            C-->>L: OK node=mysql2 id=...
        else no primary (failover)
            H--xC: ERROR 2003 / 2013
            C-->>L: FAIL
        else connection hangs
            C--xL: killed by timeout after 15 s
        end
    end

Each txloop iteration opens a new connection, so the loop survives a failover and reports exactly when writes stopped and resumed.

Used throughout the failover and router tests:

txloop
10:12:01  OK    #41     node=mysql2  id=1841
10:12:02  OK    #42     node=mysql2  id=1842
10:12:03  FAIL  #43     ERROR 2003 (HY000): Can't connect to remote MySQL server
10:12:18  FAIL  #44     ERROR timeout after 15s
10:12:19  OK    #45     node=mysql1  id=1849

Each tx opens a new connection, so the loop keeps running through a failover: FAIL while there is no primary, then OK with the new node=. Results are appended to tx.log (git-ignored). Stop with Ctrl+C, then:

grep -c ' OK ' tx.log; grep -c ' FAIL ' tx.log      # successes / failures
grep ' FAIL ' tx.log                                 # what failed and why
: > tx.log                                           # empty the log before the next test

The MySQL HA Lab series

  1. Series introduction
  2. Lesson 1: Environment and SQL helpers (you are here)
  3. Lesson 2: A self-bootstrapping InnoDB Cluster
  4. Lesson 3: Failover, quorum and recovery
  5. Lesson 4: Monitoring with Prometheus and Grafana
  6. Lesson 5: Highly available routing with HAProxy
  7. Lesson 6: Stale reads and read/write splitting
  8. Lesson 7: Failover tuning, switchover and partitions
  9. Lesson 8: A Spring Boot app that survives failover
  10. Lesson 9: The same app in Go
  11. Bonus: Bonus: Operations and troubleshooting

All code is on GitHub: mytechbytes/mysql-ha-lab. Questions or a different result in your lab? Leave a comment below.

← Previous MySQL HA Lab: Learn MySQL InnoDB Cluster High Availability Hands-On with Docker Next → MySQL HA Lab, Lesson 2: A Self-Bootstrapping InnoDB Cluster in Docker
Part 2 of 11 · Collection MySQL HA Lab Back to collection →
Membership

Go deeper.

Members get the full technical guides, production checklists and the source repos behind every article.

You may also like