🔐 How to Manage Your VPS Database Safely from Your Local PC

Managing your VPS database remotely doesn’t mean exposing it to the internet 🌍. The safest and most professional way is to connect through an SSH tunnel — a secure encrypted bridge between your PC and your server.

Let’s go step by step 🚀

🧱 1. Why You Shouldn’t Open Port 3306

Many developers make the mistake of opening MariaDB/MySQL ports directly (3306). ❌ Bad idea — it exposes your database to brute-force attacks and scanning bots.

✅ Instead, you’ll use an SSH tunnel to forward traffic securely through port 22 (SSH). Only you — and anyone with SSH access — can reach the database.

🧑‍💻 2. Verify That MariaDB Is Securely Listening Only on Localhost

On your VPS (connect via SSH as root or your sudo user):

sudo systemctl status mariadb --no-pager
sudo ss -lntp | grep 3306

You should see:

LISTEN 127.0.0.1:3306 ...

If not, edit:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Make sure it says:

bind-address = 127.0.0.1

Then restart MariaDB:

sudo systemctl restart mariadb

🛠️ 3. Create a Database and User

Enter the MariaDB console:

sudo mariadb

Inside the prompt:

CREATE DATABASE myproject CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myuser'@'127.0.0.1' IDENTIFIED BY 'MySecurePass123!';
GRANT ALL PRIVILEGES ON myproject.* TO 'myuser'@'127.0.0.1';
FLUSH PRIVILEGES;

Exit with \q.

🌉 4. Create the SSH Tunnel from Your PC

On Windows PowerShell (or your terminal on macOS/Linux):

ssh -N -L 3307:127.0.0.1:3306 daniel@your-vps-ip

What this does:

• 🎯 Local port 3307 on your PC → securely forwarded to 127.0.0.1:3306 on your VPS.
• Keep this terminal window open while connected.

You can now reach your server’s database as if it were local!

🖥️ 5. Connect Using DBeaver

Open DBeaver (free, cross-platform GUI).

1. Click 🆕 New Connection → choose MariaDB (or MySQL).

2. Under General tab:

   • Host: 127.0.0.1
   • Port: 3307
   • Database: myproject
   • Username: myuser
   • Password: MySecurePass123!

3. ✅ Test Connection → Finish

🧭 Alternative: Use DBeaver’s Built-In SSH

Instead of creating the tunnel manually:

1. Go to Connection Settings → SSH.

2. Enable Use SSH Tunnel.

3. Fill:

   • Host: your-vps-ip
   • User: daniel
   • (Optionally add your private key file or password)

4. Leave the database host as 127.0.0.1 and port 3306.

Click Test Connection — and you’re in! 🎉

🧩 6. Bonus Tips

• 💾 Back up databases regularly:

  mysqldump -u myuser -p -h 127.0.0.1 -P 3307 myproject > backup.sql
  

• 👥 Create read-only users for reports:

  CREATE USER 'report'@'127.0.0.1' IDENTIFIED BY 'ReadOnly!';
  GRANT SELECT ON myproject.* TO 'report'@'127.0.0.1';
  

• 🔒 Disable root SSH login and use SSH keys for extra safety.

🧭 7. Summary

🌟 Final Thoughts

You now have a secure, professional database management workflow 💼 No open ports, no risky phpMyAdmin, just clean SSH + DBeaver.

Next time you deploy a project, you can safely manage production data from your laptop — encrypted, authenticated, and easy to use. 🔒✨