Flip the DB Sync Direction, Flip Every Safety Assumption
Flipping db-push into db-pull is more than reversing arrows: local backs up first, the dump gets inspected before trusted, backtick rules change over SSH.
TL;DR
Reversing the sync direction puts your local database at risk, so the guard sequence has to flip. The script first backs up local data for rollback, then takes a consistent staging dump and checks size, tables and completion before wiping anything. It avoids SSH backtick issues and strictly compares row counts afterward, preferring a hard failure over a half-finished import.
One line in deploy/db-pull.sh made me pause mid-typing: a database-destroying command whose target was my own laptop. Writing db-push.sh, which replaces the staging database, never felt dramatic; staging can be rebuilt anytime. Building the reverse flipped the seats. Staging became the source, and the thing getting wiped was local, half-finished experiments included.
My first instinct was lazy in the best way: copy the old script, flip the arrows, done. Looking again, not even close. Every guard in the old script stood on one assumption, that staging is the thing that breaks. Flip the assumption and the whole guard order has to flip with it.
Gate one: back up local, not staging
In the pull direction, the first thing that runs is not a dump but a local database backup into backup/mude-local-before-pull-*.sql.gz. That file is the revert button. Import goes sideways halfway through? Restore this file and the laptop is back where it was. Old backups get pruned, only the newest three survive. The rule has no negotiation: this backup must finish before a single byte changes.
Gate two: take the dump apart before trusting it
The staging dump runs on the VPS with --single-transaction. The MySQL manual explains that this flag sets the isolation level to REPEATABLE READ and opens a transaction before the dump starts, so what you get is a consistent snapshot at a single point in time without blocking the application that is running [1]. The fine print: it only helps with transactional tables like InnoDB.
The dump itself is a logical backup: a set of statements that rebuilds the database, not a copy of the files [5]. Because of that nature, it doesn't get trusted on arrival. Three checks must pass in the script: file size above the floor, a non-zero count of CREATE TABLE lines, and the last five lines of the file must contain the Dump completed comment, mysqldump's built-in marker for a dump that finished whole [1]. Only then does the script approach the step that the MySQL manual itself introduces with Be very careful with this statement! [2] Now it's obvious why that step sits fifth, not first.
# sanity gate: fail fast, before the local database is touched
if [ "$dump_bytes" -lt 1000 ] || [ "$table_count" -eq 0 ]; then
echo "staging dump looks wrong: replace aborted"
exit 1
fi
gunzip -c "$STAG_DUMP_FILE" | tail -5 | grep -q 'Dump completed' || exit 1Gate three: backticks safe locally, deadly over SSH
The part that taught me the most turned out to sit at the end of the script: verification. I wanted to compare COUNT(*) per table, local against staging. The staging query has to run on the VPS over SSH, and that's exactly where the backticks around table names become a landmine. The remote shell parses the string it receives and eats the backticks as command substitution, so the SQL arriving at the server is no longer the SQL I wrote.
The bash manual is precise about the old backquote form: backslash keeps its literal meaning except when followed by $, a backtick, or a backslash, and the first unescaped backquote terminates the substitution [3]. The modern $( ... ) form is far more predictable. Both still get evaluated by a shell, so my strategy in remote SQL stopped being about fighting escaping altogether: validate table names first, letters, digits, and underscores only. No special characters, nothing to escape.
There's a small irony here: backticks around the database name on the local side stay. That command runs through docker exec straight inside the database container [4], it never travels over SSH. Same character, two different contexts, completely different fates.
Verification that is deliberately harsh
After the import finishes, the script recounts rows per table. All staging counts come back over a single SSH connection, the ordering is pinned to the local table order, then compared one by one. One mismatched number and the script exits with a failure and the list of offending tables. There's no best-effort mode.
The real change from this script isn't a feature, it's the order: backup, dump, sanity, replace, verify. Every gate is allowed to fail first, before the destructive step gets its turn. The longer I do this, the more convinced I get that a script willing to fail hard halfway is far cheaper than one that succeeds halfway and leaves local in half-staging shape.
Sources