Core Dev Database: Build & Import¶

This runbook documents how to refresh the developer copy of the Core database by running the create-dev-db.sh script against a staging snapshot clone.

The script automatically handles schema copying, data subsetting to internal accounts, dependency management, and export. This runbook focuses on execution, validation, and troubleshooting.

Required access¶

AWS permissions: RDS access to staging snapshot instances in eu-central-1
Database credentials: MySQL user with CREATE, DROP, INSERT, SELECT on snapshot clone
Source environment: Non-production snapshot clone only (never production)
Script location: _scripts/create-dev-db.sh in the Core git repository

When to run¶

Run this process when:

Refreshing the developer database with fresh data
Setting up a new developer environment
Testing schema or data migrations against realistic data
Preparing QA or staging datasets

Before you start¶

Critical safety check

This script drops and recreates core_dev_build. It must only run on a snapshot clone, never against production or staging-prod.

Account ID requirement

You must provide a list of internal/test account IDs. Do not include real customer accounts unless explicitly approved by product/legal.

Step 1: Configure script variables¶

Edit ./_scripts/create-dev-db.sh and update the variables at the top:

HOST parameter¶

Set the RDS endpoint of your cloned production database:

HOST="your-cloned-prod-instance.c6yhq7g47ej.eu-central-1.rds.amazonaws.com"

Snapshot clone only

The HOST must point to a cloned snapshot instance, never production or staging-prod.

ACCOUNT_IDS parameter¶

Define the internal and test accounts to include:

ACCOUNT_IDS="111,222,333"

These should be:

Internal employee accounts
QA/test accounts
System accounts (e.g., support utilities)
Representative enough for dev/testing (typically 5–15 accounts)
Never real customer accounts or production accounts

The script supports both system accounts (prefixed with specific identifiers) and regular test accounts.

Step 2: Run the script¶

Once you've updated HOST and ACCOUNT_IDS, execute from the core repository root:

./_scripts/create-dev-db.sh

The script will:

Prompt for your database password (for the snapshot clone)
Drop and recreate core_dev_build database
Copy the schema from core
Copy data for selected accounts only
Handle foreign key constraints and cleanup
Export a compressed dump to core_dev_build.sql.gz

Script prompts¶

Database password: Your MySQL user password for the snapshot instance

The script will proceed automatically once you provide the password.

Step 3: Verify the output¶

After the script completes, you should have core_dev_build.sql.gz in your working directory.

Expected output size¶

The dump should be significantly smaller than production (~100–500 MB depending on selected accounts).

ls -lh core_dev_build.sql.gz

If the file is larger than expected, see Troubleshooting.

Step 4: Import into target environment¶

To import the dump into a dev, stage, or local environment:

gunzip < core_dev_build.sql.gz | mysql -h <target-host> -u <user> -p <database>

Replace:

<target-host>: RDS endpoint or localhost
<user>: MySQL username
<database>: database name (typically core_dev_build)

You'll be prompted for your password.

Example: Import to local MySQL¶

gunzip < core_dev_build.sql.gz | mysql -h localhost -u root -p core_dev_build

Example: Import to RDS staging¶

gunzip < core_dev_build.sql.gz | mysql -h staging-core.c6yhq7g47ej.eu-central-1.rds.amazonaws.com -u admin -p core_dev_build

Step 5: Validate data¶

After import, run these checks to confirm the data is correct.

Size sanity check¶

Verify the dump size is reasonable:

du -h core_dev_build.sql.gz
# Expected: 100–500 MB

Data scope validation¶

Verify only the selected accounts are present:

SELECT COUNT(*) FROM accounts;
SELECT COUNT(*) FROM domains;
SELECT COUNT(*) FROM invoices;

These counts should match your account selection. If significantly lower than expected, see Missing data.

Data integrity check¶

Ensure no domains or related records exist outside the selected accounts:

SELECT COUNT(*)
FROM domains
WHERE account_id NOT IN (<ACCOUNT_IDS>);
-- Should return: 0

Replace <ACCOUNT_IDS> with your comma-separated list (e.g., 111,222,333).

Verify sensitive tables are empty¶

These tables should be empty (0 rows):

SELECT COUNT(*) FROM tokens;
SELECT COUNT(*) FROM payment_logs;
SELECT COUNT(*) FROM pending_payments;

All should return 0.

Troubleshooting¶

Script fails early¶

Check:

Database connectivity: mysql -h <host> -u <user> -p -e "SELECT VERSION();"
Credentials are correct
Snapshot instance is available in AWS console
No ongoing backups or operations on the snapshot

Action: Verify credentials and snapshot status, then re-run.

MySQL client warning: "column statistics not supported by the server"¶

This is harmless. It occurs due to MySQL 8.x ↔ MariaDB 10.11.x version mismatch in the source and target. You can safely ignore it.

Foreign key constraint errors during script execution¶

Symptom:

Error: Cannot add or update a child row: a foreign key constraint fails

Cause:

Script regression or unsupported schema change
Missing dependency in the subset logic

Action:

Do not patch the dump manually
File an issue with the script maintainer
Update the script logic to handle the constraint

Missing data in dev¶

Symptom:

Counts from the validation queries are much lower than expected.

Cause:

Account ID selection too narrow
Missing dependency in data subset logic

Action:

Add additional account IDs and re-run the script
If accounts are present but dependent records are missing, update the script to include those tables in the keep-set

Dump too large (> 500 MB)¶

Symptom:

core_dev_build.sql.gz exceeds expected size.

Cause:

Selected accounts have high volume of domains or transactions
Large tables (e.g., events, logs) not properly filtered

Action:

Reduce the account ID list to a smaller set
Contact the script maintainer to exclude or limit specific high-volume tables

Import hangs or takes too long¶

Check:

Network connectivity between client and target RDS instance
Target RDS instance has sufficient disk space
No long-running queries on the target database

Action:

Check AWS RDS console for CPU/connections
Consider importing during off-hours if on a shared instance
If import stalls, Ctrl+C to cancel and retry

Environment references¶

Source: stage.cs2d7d5picp4.eu-central-1.rds.amazonaws.com → core (snapshot clone only)
Target DB: core_dev_build
Script: _scripts/create-dev-db.sh
Output: core_dev_build.sql.gz
Client: MySQL 8.x
Server: MariaDB 10.11.x

Support & ownership¶

Script maintenance: engineering
Account selection & approval: engineering, QA
Execution: devops, engineering

For issues or questions, contact the Core service team.