🚀 Seamless MySQL Migration: How Mwacharo Exported a Remote Database from DigitalOcean and Imported It Locally

When managing cloud-based applications like Solssa, there are times you need to back up or clone your production database for local development. In this tutorial, we’ll walk through how Mwacharo, a developer working on Solssa, exported a MySQL database from a DigitalOcean droplet and imported it into his local machine using simple Linux terminal tools.

We’ll use IP: 123.456.78.90 for illustration.

🧱 Step 1: Access the Remote Server
Mwacharo first logged into his DigitalOcean droplet via the web-based Droplet Console since he was not yet connected via SSH from his laptop:

ssh root@123.456.78.90

Output:

Permission denied (publickey).

This error meant his local machine didn’t yet have SSH access configured.

📦 Step 2: Export the Remote MySQL Database
Once inside the droplet (via web console), he listed the available databases:

SHOW DATABASES;
The database he wanted to export was solssaSystem.

He then dumped the database using:

mysqldump -u root -p solssaSystem > solssaSystem.sql
After entering the MySQL root password, the .sql file was created in /root/.

🗜️ Step 3: Compress the SQL File
Instead of using zip, which wasn't installed, Mwacharo used gzip (a common Linux alternative):

gzip solssaSystem.sql

Result: solssaSystem.sql.gz

🖇️ Step 4: Fix SSH Access for File Download
On his local machine, Mwacharo tried to download the file using scp:

scp root@123.456.78.90:/root/solssaSystem.sql.gz ~/Downloads/
But he got the error:

Permission denied (publickey).
This meant his local machine wasn’t authorized to connect via SSH using key authentication.

🔑 Step 5: Enable SSH Access with Public Key
To connect securely:

On his local machine, Mwacharo generated an SSH key:

ssh-keygen
Then copied his public key to the remote droplet:

ssh-copy-id root@123.456.78.90
Alternatively, he could manually add his public key from ~/.ssh/id_rsa.pub into the droplet’s /root/.ssh/authorized_keys.

📥 Step 6: Download the Database File
Once SSH access was fixed, he downloaded the file:

scp root@123.456.78.90:/root/solssaSystem.sql.gz ~/Downloads/
🧩 Step 7: Extract and Import the File Locally
Back on his local Ubuntu system:

gunzip ~/Downloads/solssaSystem.sql.gz
mysql -u root -p solssaLocal < ~/Downloads/solssaSystem.sql
solssaLocal is the name of the database he created locally to import into.

✅ Conclusion
Mwacharo successfully cloned his Solssa production database into his local environment using only a few terminal commands and SSH configuration. This is a common and secure practice for developers working with cloud-hosted databases.