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:
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.
Top comments (0)