Sync production MySQL to your laptop, safely

Some bugs only show up with real data: a model with 40 variants, a city slug with a trailing space, a price row that is NULL when it "can't be". For those I want production data locally — without risking production and without copying things I shouldn't.

This is the shape of the script I use across several projects.

Rule 1: a read-only user, always

The script connects with a user that only has SELECT, SHOW VIEW, TRIGGER and LOCK TABLES. If I ever point it at the wrong host or reverse the arguments, the worst it can do is read.

Credentials come from environment variables (SRC_HOST, SRC_USER, SRC_PASSWORD, LOCAL_*), never from the repository.

Rule 2: map production names to local names

Production and local databases rarely share names, so the script keeps an explicit map:

bash

DB_MAP=(
  "shop_db        shop_local"
  "catalog_db     catalog"
  "blog_db        blog_local"
)

Arguments are always the production names. An unknown name prints the map and exits — no guessing.

bash

./db-sync.sh                    # every DB in the map
./db-sync.sh shop_db catalog_db # just these

Rule 3: structure only for sensitive tables

Leads, SMS logs, sessions and audit trails contain phone numbers and personal data. I need their schema to run the app, but not their rows:

bash

STRUCTURE_ONLY_TABLES="audit_trail lead leads lead_detail sms_log queue"
IGNORE_TABLES="logs user_session"

The dump runs twice per database: once with --no-data for the structure-only list, once with --ignore-table for everything else. Per-database extras use IGNORE_TABLES_<db> so a giant cache table in one DB doesn't affect the others.

Rule 4: don't lock production

bash

mariadb-dump --single-transaction --quick --skip-lock-tables \
  --routines --triggers "$SRC_DB" | gzip > "$DUMP"

--single-transaction gives a consistent InnoDB snapshot without table locks.
--quick streams rows instead of buffering whole tables in memory.
Piping through gzip keeps the file small and the network transfer short.

Rule 5: import into a fresh local database

bash

mariadb -e "DROP DATABASE IF EXISTS \`$LOCAL_DB\`; CREATE DATABASE \`$LOCAL_DB\`"
zcat "$DUMP" | mariadb "$LOCAL_DB"

Dropping and recreating avoids half-merged states where an old local table survives because production renamed it.

Anything that writes while you are importing — a webhook, a retrying job — needs to be safe to replay, which is its own problem: idempotent APIs and webhooks.

What I'd add next

A row-count comparison after import, printed as a table.
--where filters for very large tables (last 90 days only).
A guard that refuses to run if LOCAL_HOST isn't 127.0.0.1 or localhost.

Small script, big payoff: reproducing a production bug locally in minutes instead of guessing from logs.