Dumping Your MySQL data (schema and data) from a Remote Server to Another

Migrating a Database to a Remote Instance Using scp

Recently, I needed to migrate our database from a local setup—where both the database and the API server were hosted on the same EC2 instance—to a dedicated remote database server separation of concerns.

As part of this migration, I exported our database and securely copied the dump file to my local machine using scp. Here’s a breakdown of the process.

🛠️ What is scp?

scp (Secure Copy Protocol) is a command-line utility that uses SSH to copy files between machines securely. It’s great for transferring backups or configuration files between servers or your local environment.

Step 1: Prepare Your SSH Key

Make sure you have access to the private key that allows SSH access to your server. In my case, I used a key called dev_backend_key.pem.

Step 2: Run the scp Command

Here’s the command I used to copy the database dump (dump.sql) from the remote EC2 instance to my local development folder:

scp -i dev_backend_key.pem \
  ubuntu@ec2-46-271-019-759.eu-north-1.compute.amazonaws.com:/var/www/dump.sql \
  /Users/your_name/backend_server/dump.sql

Let’s break that down:

• -i dev_backend_key.pem: Path to your SSH key
• ubuntu@<host>: The SSH user and hostname of your EC2 instance
• /var/www/dump.sql: The path to the file on the remote server
• /Users/your_name/backend_server/dump.sql: The destination path on your local machine, including what you want the file to be named

You should see output like:

dump.sql                                         100% 673KB  1.5MB/s   00:00

🎯 Tip: Export Schema Only vs. Full Data

Depending on your migration needs, you can choose to:

• Export just the schema (for initializing a fresh database), or
• Export both schema and data (as I did) for a full migration

This gives you flexibility in how you spin up your new environment.