Peter Davies

Ramblings

Cloning a live database to staging

Every staging site needs refreshing from live now and then. For years I did this with a one-liner along the lines of mysqldump -h db.example.com -u user -pSECRET live_db > dump.sql, then hand-edited the URLs. That works, but it has three problems:

This is the script I use now. The source and target here are two databases on the same server, which is the common case on RDS: one cluster, live_db and staging_db side by side. Point the import at a different host if your staging database lives elsewhere.

Keep the password in an option file

Create a file only you can read, for example ~/.staging-clone.cnf:

[client]
host=db.example.com
user=clone_user
password=CHANGE_ME

chmod 600 ~/.staging-clone.cnf

Both mysql and mysqldump take --defaults-extra-file. It must be the first option on the line or it is ignored.

If you cannot use a file, exporting MYSQL_PWD inside the script is a fallback, but it is visible in the process environment and MySQL documents it as insecure. The option file is the better habit.

The script

#!/bin/bash
set -euo pipefail

CNF="$HOME/.staging-clone.cnf"
DB_FROM="live_db"
DB_TO="staging_db"

STRUCT_FILE="$(mktemp)"
DATA_FILE="$(mktemp)"
trap 'rm -f "$STRUCT_FILE" "$DATA_FILE"' EXIT

EXCLUDED_TABLES=(
  sessions
  api_log
  job_queue
  job_queue_log
  profiler
)

IGNORE_ARGS=()
for TABLE in "${EXCLUDED_TABLES[@]}"; do
  IGNORE_ARGS+=("--ignore-table=${DB_FROM}.${TABLE}")
done

echo "Dumping structure"
mysqldump --defaults-extra-file="$CNF" \
  --single-transaction --no-data --routines --triggers \
  "$DB_FROM" \
  | sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' \
  > "$STRUCT_FILE"

echo "Dumping data"
mysqldump --defaults-extra-file="$CNF" \
  --single-transaction --no-create-info --skip-triggers \
  "${IGNORE_ARGS[@]}" \
  "$DB_FROM" \
  > "$DATA_FILE"

echo "Importing structure"
mysql --defaults-extra-file="$CNF" "$DB_TO" < "$STRUCT_FILE"

echo "Importing data"
mysql --defaults-extra-file="$CNF" "$DB_TO" < "$DATA_FILE"

echo "Done"

Why it is two dumps

The structure dump has every table, including the excluded ones. The data dump skips the rows for those tables. The result on staging is that every table exists, so the application does not fall over, but the log, session and queue tables are empty.

If you used --ignore-table on a single combined dump, those tables would be missing altogether on staging.

--no-data with --routines and --triggers puts the stored procedures, functions and triggers in the structure file. --skip-triggers on the data dump stops them being written twice.

--single-transaction on both dumps reads InnoDB tables from a consistent snapshot without locking them, so live carries on as normal while the copy runs.

The DEFINER fix

A dump of a view or routine looks like this:

CREATE DEFINER=`live_user`@`%` SQL SECURITY DEFINER VIEW ...

The sed line rewrites every DEFINER= clause to DEFINER=CURRENT_USER, so the objects are created as whichever user runs the import. It runs only on the structure file, which is small; running it over a multi-gigabyte data file is slow and could alter row data that happens to contain the same text.

Changing the URLs

Most web applications store their own base URL in the database, and a staging copy that still points at the live domain will redirect you straight back to live. Fix it after the import with SQL against the specific settings table, rather than a blind sed across the whole dump. For Magento 1 that is:

UPDATE core_config_data
   SET value = REPLACE(value, 'https://www.example.com/', 'https://staging.example.com/')
 WHERE path IN ('web/unsecure/base_url', 'web/secure/base_url');

Never sed URLs inside a WordPress dump. WordPress stores PHP-serialised arrays with string lengths in them, and changing the length of a URL breaks those values. Use WP-CLI's wp search-replace, which understands serialisation.

Files as well

If staging needs the uploaded media too, rsync it across with an exclude list for caches and anything generated:

rsync -zrl --delete --stats --exclude-from=/home/staging/exclude.txt \
  www.example.com:/var/www/example.com/public_html/ \
  /var/www/staging.example.com/public_html/

Watch the trailing slashes: with them, the contents of the source directory go into the target directory, which is what you want here.

Things to watch for