Restore Local DB

Restore a Local Database from a Production Dump

Bring a local MySQL database up to date with a production copy, without any risk of touching the live Azure production databases.

The destructive step is DROP DATABASE. Every guardrail exists to guarantee that when it runs, it runs against local MySQL and nothing else.

Inputs

Parse the user's request into:

If the user pastes a dump path in prose ("restore local coop from <BACKUP_DIR>\coop_x.sql.gz"), treat it as --dump.

Credentials file

All passwords are read at runtime from the machine's credentials file — never hardcode them in this skill. Location is platform-specific (per the database-guide skill):

• Windows: C:\Credentials\Credentials.txt
• Linux / macOS: ~/.config/hmis/credentials.txt

Resolve it once at the start: use the Windows path if it exists, else the $HOME/.config/hmis/credentials.txt path, else stop and ask the user where their credentials file is. Referenced below as <CREDS>.

Downloaded dumps and rollback backups go under a backup directory — C:\Backups on the Windows dev laptop (C:\Backups\pre-restore for rollback copies). Adjust for the platform if the skill ever runs elsewhere. Referenced below as <BACKUP_DIR>. Temp files (.err logs etc.) go in the session scratchpad directory, referenced as <SCRATCH>.

Section names and the exact set of DB servers in that file change over time — read the file and match on the section headings and the "Databases :" lines, don't assume a fixed layout.

Path selection

user named DB(s) only            -> Acquisition sub-procedure, then Phase 0..3 per DB
user gave --dump <path>          -> Phase 0..3 per DB (no acquisition)

Phase 0 runs in BOTH paths. A provided dump does not exempt the run from the no-SSH-tunnel assertion — the DROP DATABASE is just as destructive either way.

Keep the machine awake for the whole run

A large import takes many minutes and the machine going to sleep mid-import kills it, leaving the target DB half-populated. Before starting, disable sleep:

powercfg //change standby-timeout-ac 0
powercfg //change standby-timeout-dc 0
powercfg //change hibernate-timeout-ac 0
powercfg //change hibernate-timeout-dc 0

If the laptop lid may be closed, also set the lid action to "do nothing" (powercfg //setacvalueindex SCHEME_CURRENT SUB_BUTTONS LIDACTION 0 + //setdcvalueindex ... 0 + //setactive SCHEME_CURRENT). Restore the previous power scheme (powercfg //setactive SCHEME_BALANCED, or re-apply the saved timeouts) in Phase 3 once every DB is done.

Run the import itself as a detached process (a hidden powershell.exe Start-Process, or nohup under a persistent shell) that survives the controlling session being interrupted, and have it write progress to a status file you poll.

Acquisition sub-procedure (only when no --dump given)

Runs before Phase 0. It opens an SSH tunnel to Azure, dumps on the VM, downloads the compressed file, then kills the tunnel. The restore never coexists with a tunnel.

A1 — Resolve each DB to its Azure DB server

Read <CREDS>. In the NEW - MySQL Flexible Servers section it lists, per server:

--- db3 --- 10.30.2.6 ---
Databases : coop, rhdrawer (+ audits)
Username  : hmis_admin
Password  : <complex>

and the SSH host aliases in the NEW - SSH access section (new-shared1..4, each forwarding local ports 4305=db1 4306=db2 4307=db3 4308=db4). Any new-shared* alias forwards all four DB ports, so you can tunnel through a single host for several DBs.

Build, for each <db>:

• DB_PRIVATE_IP (e.g. 10.30.2.6)
• DB_LOCAL_PORT (4305..4308 matching db1..db4)
• DB_ADMIN_PASSWORD (the complex Azure password for that server)
• an SSH host alias to tunnel through (any new-shared*; prefer the one whose "Clients" list names this DB)

If a DB is not listed in the credentials file, stop and ask the user for the DB server's private IP, the matching local port, and which SSH alias to use.

A2 — Open ONE tunnel

/c/Windows/System32/OpenSSH/ssh.exe -F ~/.ssh/config -N -f <ssh-alias>

Verify the needed port(s) answer:

mysql -h 127.0.0.1 -P <DB_LOCAL_PORT> -u hmis_admin -p'<DB_ADMIN_PASSWORD>' -e "SELECT 1;"

Only one new-shared* tunnel at a time — the 4305-4308 ports are identical across every alias.

A3 — Dump on the VM, gzip, download

Do the dump on the VM against the DB's private IP. Dumping through the tunnel from the laptop is far slower — an 8 GB DB did not finish in 2 minutes that way, versus ~90 seconds on the VM.

Write a --defaults-extra-file cnf locally, scp it to /var/tmp on the VM, chmod 600, then run mysqldump on the VM. Quote the password in the cnf (password="...") — the Azure admin passwords contain #, ?, =, +, and an unquoted value silently truncates at the first special char and fails with Access denied. Then:

# on the VM, detached:
nohup sh -c 'nice -n 10 mysqldump --defaults-extra-file=/var/tmp/<db>.cnf \
  --single-transaction --quick --routines --triggers --events \
  --set-gtid-purged=OFF --no-tablespaces <db> 2>/var/tmp/<db>.err \
  | gzip > /var/tmp/<db>_prod_<ts>.sql.gz; echo $? > /var/tmp/<db>.done' >/dev/null 2>&1 &

Poll /var/tmp/<db>.done. When it reads 0 and <db>.err is empty: gzip -t the file on the VM, check its tail decompresses to -- Dump completed on ..., then scp it to <BACKUP_DIR>\<db>_prod_<ts>.sql.gz. Confirm the downloaded byte size matches the VM's exactly.

A4 — Tear the tunnel down

taskkill //F //IM ssh.exe

Then clean up the VM: rm -f /var/tmp/<db>.cnf /var/tmp/<db>_prod_<ts>.sql.gz /var/tmp/<db>.err /var/tmp/<db>.done. Delete the local cnf too — it holds the Azure admin password.

The downloaded <BACKUP_DIR>\<db>_prod_<ts>.sql.gz is now the --dump for that DB.

Phase 0 — Safety preconditions (ABORT on any failure)

Run this immediately before touching local MySQL, in every path.

0.1 — No SSH tunnels

taskkill //F //IM ssh.exe 2>&1 || true          # ssh-agent service is fine, leave it
# assert zero ssh.exe (excluding the ssh-agent Windows service):
tasklist | grep -i "ssh.exe" | grep -v "ssh-agent"          # must produce NOTHING
# assert no forwarded DB / Payara-admin ports listening (the ~/.ssh/config
# new-shared* hosts forward 4305-4308 for the four DBs plus a 3x848 admin port):
netstat -ano | grep LISTENING | grep -E ":(430[5-9]|3[0-9]848)\b"   # must produce NOTHING

If any ssh.exe remains or any forwarded port is listening -> ABORT, tell the user to close their tunnels first. (On the acquisition path this check runs after A4 has already killed the tunnel this skill opened; a surviving tunnel here means one the user opened separately.)

0.2 — MySQL target is the LOCAL instance

Get the local simple password from <CREDS> (LOCAL DEVELOPMENT - MySQL section). Then:

mysql --host=127.0.0.1 --port=3306 --protocol=TCP -uroot -p<simple> -N \
  -e "SELECT CONCAT(@@hostname,'|',@@version,'|',@@port,'|',@@datadir);"

Assert ALL of:

• @@version does NOT contain azure (Azure servers report 8.0.46-azure)
• @@port = 3306
• @@hostname is this machine (not a db*/Azure host)
• @@datadir is a local Windows path

Any mismatch -> ABORT.

0.3 — Command self-scan

Before running any DROP / import / mysqldump command, grep the command string for forbidden tokens and abort on a match:

10.30.    20.24.    40.83.    57.158.    carecode.org

plus any Azure admin-password fragment you loaded in A1. Every restore command must use --host=127.0.0.1 --port=3306 --protocol=TCP with the simple local password only. A complex password anywhere in a restore command = wrong target.

Phase 1 — Sanity gate (per DB)

Confirm the local DB is stale test data, not something real:

mysql --host=127.0.0.1 --port=3306 --protocol=TCP -uroot -p<simple> <db> -N \
  -e "SELECT COUNT(*) FROM bill WHERE createdAt >= NOW() - INTERVAL <D> DAY;"

• result < <N> -> PASS, safe to drop & restore.
• result >= <N> -> STOP this DB. Do not drop. Report the count and the latest MAX(createdAt) and ask the user to investigate.
• no bill table -> fall back to COUNT(*) of the largest table and require explicit user confirmation before proceeding.

Also print, for the report: SELECT COUNT(*) FROM bill; and SELECT MAX(createdAt) FROM bill; (the "before" numbers).