---
title: "Cloning a live database to staging"
date: "2019-03-18"
path: "/194-cloning-a-live-database-to-staging.html"
section: "/ramblings"
description: "A small bash script that copies a live MySQL database into a staging copy, skipping log and session data, fixing DEFINERs and keeping the password off the command line."
---

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:

- the password is on the command line, so it sits in shell history and shows up in `ps` for anyone else on the box;
- it copies tables nobody needs on staging (logs, sessions, queues), which on a busy site are often the biggest ones;
- stored routines, views and triggers come across with a `DEFINER` naming the live user, and the import fails if that user does not exist or, on a managed service like Amazon RDS, if you lack `SUPER`.

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`:

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

```bash
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

```bash
#!/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:

```sql
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:

```sql
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:

```bash
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

- Run it against the right database. Hard-code `DB_TO` to something with "staging" in the name, and never to a variable that could be empty.
- The user in the option file needs at least `SELECT`, `SHOW VIEW` and `TRIGGER` on the source to dump, plus read access to the routines, and enough rights on the target to create and drop tables, views and routines.
- Staging now holds a copy of live customer data. Treat it with the same care, or add a step that anonymises email addresses and personal fields before anyone else is given access.