So you've got a VPS and you're ready to set up your first database. Maybe you're deploying a Node.js API, launching a SaaS product, or hosting client projects. Whatever your reason, you've made a smart choice. Self-hosting databases gives you complete control over your data, better performance, and often costs less than managed database services.

But here's the thing: setting up a database on a VPS isn't just about running apt install mongodb and calling it a day. That's how you end up with an exposed database that gets compromised within hours. Trust me, I've seen it happen.

In this guide, I'll walk you through everything you need to know to install, secure, and manage MongoDB, MySQL, and PostgreSQL on a VPS. We'll cover the stuff that actually matters in production: proper authentication, firewall rules, backups, and performance tuning.

What You'll Learn

  • How to prepare your VPS before installing any database
  • Step-by-step installation for MongoDB, MySQL, and PostgreSQL
  • Essential security configurations that many tutorials skip
  • Backup strategies that actually work in production
  • Performance optimization tips for real-world workloads
  • How to connect your Node.js apps securely

When to Use Each Database

Database Best For Data Structure
MongoDB Flexible schemas, rapid prototyping, document storage, real-time apps Documents (JSON-like)
MySQL Traditional web apps, e-commerce, CMS, structured data with relationships Tables (relational)
PostgreSQL Complex queries, data integrity, geospatial data, advanced features Tables (relational) + JSON support

 Common Beginner Mistakes

  • Installing databases without enabling authentication
  • Exposing database ports (27017, 3306, 5432) to the public internet
  • Using root/admin accounts for application connections
  • Not setting up any backup strategy
  • Running databases with default configurations in production

VPS Preparation

Before you install any database, you need to prepare your VPS properly. Think of this as laying the foundation for a house. Skip this step, and everything you build on top will be shaky.

Update System Packages

First things first: update your system. This ensures you have the latest security patches and package versions.

Ubuntu/Debian

# Update package lists
sudo apt update

# Upgrade installed packages
sudo apt upgrade -y

# Install essential tools
sudo apt install -y curl wget gnupg2 software-properties-common apt-transport-https ca-certificates

CentOS/RHEL

# Update all packages
sudo yum update -y

# Install essential tools
sudo yum install -y curl wget gnupg2

Configure Firewall (UFW)

A firewall is your first line of defense. We'll use UFW (Uncomplicated Firewall) because it's... well, uncomplicated.

# Install UFW (if not already installed)
sudo apt install ufw -y

# Set default policies
sudo ufw default deny incoming
sudo ufw default allow outgoing

# Allow SSH (important! don't lock yourself out)
sudo ufw allow ssh

# Or if you use a custom SSH port
sudo ufw allow 2222/tcp

# Enable the firewall
sudo ufw enable

# Check status
sudo ufw status verbose

Always allow SSH before enabling the firewall. If you enable UFW without allowing SSH, you'll lock yourself out of your VPS. I've done this once. It's not fun.

Database Port Reference

Here are the default ports for each database. We'll configure these properly later:

  • MongoDB: 27017
  • MySQL: 3306
  • PostgreSQL: 5432

Why You Should NOT Expose DB Ports Publicly

Opening database ports to the internet is like leaving your house keys under the doormat. Bots constantly scan for open database ports, and an exposed database can be compromised in minutes. Always keep databases bound to localhost and use SSH tunnels for remote access.

Architecture Overview

┌─────────────────────────────────────────────────────────────┐
│                        YOUR VPS                              │
│  ┌─────────────────────────────────────────────────────────┐│
│  │                     FIREWALL (UFW)                       ││
│  │    ┌──────────┐                                          ││
│  │    │ Port 22  │ ◄── SSH (allowed from your IP)          ││
│  │    └──────────┘                                          ││
│  │    ┌──────────┐                                          ││
│  │    │ Port 80  │ ◄── HTTP (if needed)                    ││
│  │    └──────────┘                                          ││
│  │    ┌──────────┐                                          ││
│  │    │ Port 443 │ ◄── HTTPS (if needed)                   ││
│  │    └──────────┘                                          ││
│  │    ┌──────────┐                                          ││
│  │    │ 27017 ❌  │ ◄── MongoDB (BLOCKED from internet)     ││
│  │    │ 3306  ❌  │ ◄── MySQL (BLOCKED from internet)       ││
│  │    │ 5432  ❌  │ ◄── PostgreSQL (BLOCKED from internet)  ││
│  │    └──────────┘                                          ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  ┌──────────────┐     ┌──────────────┐                      │
│  │   Node.js    │────►│   Database   │                      │
│  │     App      │     │  (localhost) │                      │
│  └──────────────┘     └──────────────┘                      │
│         │                                                    │
│         ▼                                                    │
│  ┌──────────────┐                                           │
│  │    Nginx     │ ◄── Reverse proxy (optional)              │
│  └──────────────┘                                           │
└─────────────────────────────────────────────────────────────┘

Set Up a Non-Root User (If You Haven't)

Running everything as root is bad practice. Create a dedicated user for your applications:

# Create a new user
sudo adduser deployer

# Add to sudo group
sudo usermod -aG sudo deployer

# Switch to the new user
su - deployer

Set up SSH key authentication for your new user and disable password authentication. This significantly improves security.

MongoDB Installation & Setup

What MongoDB is Best For

MongoDB shines when you need flexibility. It's perfect for applications where your data structure might evolve over time, like content management systems, real-time analytics, or IoT applications. If you're building with Node.js, MongoDB feels natural because you're working with JSON-like documents throughout your stack.

Installing MongoDB on Ubuntu

MongoDB isn't in the default Ubuntu repositories, so we need to add the official MongoDB repository first.

# Import MongoDB public GPG key
curl -fsSL https://www.mongodb.org/static/pgp/server-7.0.asc | \
   sudo gpg -o /usr/share/keyrings/mongodb-server-7.0.gpg \
   --dearmor

# Add MongoDB repository (Ubuntu 22.04)
echo "deb [ arch=amd64,arm64 signed-by=/usr/share/keyrings/mongodb-server-7.0.gpg ] https://repo.mongodb.org/apt/ubuntu jammy/mongodb-org/7.0 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-7.0.list

# Update package list
sudo apt update

# Install MongoDB
sudo apt install -y mongodb-org

Start and Enable MongoDB 

# Start MongoDB
sudo systemctl start mongod

# Enable MongoDB to start on boot
sudo systemctl enable mongod

# Check status
sudo systemctl status mongod

If MongoDB started successfully, you'll see "active (running)" in the status output.

Creating a Database and User

Before enabling authentication, let's create an admin user and a database user for your application.

# Connect to MongoDB shell
mongosh

# Switch to admin database
use admin

# Create admin user
db.createUser({
  user: "adminUser",
  pwd: "YourStrongAdminPassword123!",
  roles: [
    { role: "userAdminAnyDatabase", db: "admin" },
    { role: "readWriteAnyDatabase", db: "admin" }
  ]
})

# Create your application database
use myapp

# Create application-specific user
db.createUser({
  user: "myappUser",
  pwd: "YourStrongAppPassword456!",
  roles: [
    { role: "readWrite", db: "myapp" }
  ]
})

# Exit
exit

Use a password generator for production passwords. At minimum: 16+ characters, mixed case, numbers, and symbols. Store them securely in a password manager or secrets management tool.

Enabling Authentication

By default, MongoDB allows anyone to connect without a password. Let's fix that.

# Edit MongoDB configuration
sudo nano /etc/mongod.conf

Find the security section and modify it:

# mongod.conf

# Network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1  # Only listen on localhost

# Security
security:
  authorization: enabled

Restart MongoDB to apply changes:

sudo systemctl restart mongod

Testing Authentication 

# Try connecting without auth (should fail)
mongosh

# Connect with authentication
mongosh -u "myappUser" -p "YourStrongAppPassword456!" --authenticationDatabase "myapp"

# Or using connection string
mongosh "mongodb://myappUser:YourStrongAppPassword456!@localhost:27017/myapp"

Connecting from Node.js 

// Install mongoose
// npm install mongoose

const mongoose = require('mongoose');

// Connection string with authentication
const MONGO_URI = 'mongodb://myappUser:YourStrongAppPassword456!@127.0.0.1:27017/myapp';

mongoose.connect(MONGO_URI)
  .then(() => console.log('MongoDB connected successfully'))
  .catch(err => console.error('MongoDB connection error:', err));

// Better approach: use environment variables
// const MONGO_URI = process.env.MONGO_URI;

Always use environment variables for database credentials in production. We'll cover this in the production setup section.

MySQL Installation & Setup

What MySQL is Best For

MySQL is the workhorse of web applications. It's battle-tested, well-documented, and perfect for applications with structured data and relationships. E-commerce sites, content management systems, and traditional web applications often use MySQL because of its reliability and wide hosting support.

Installing MySQL

# Install MySQL Server
sudo apt install mysql-server -y

# Start and enable MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

# Check status
sudo systemctl status mysql

Running mysql_secure_installation

This is crucial. The mysql_secure_installation script helps you secure your MySQL installation by setting up a root password, removing anonymous users, and other security measures.

sudo mysql_secure_installation

The script will ask you several questions:

  1. VALIDATE PASSWORD COMPONENT: Yes (recommended for production)
  2. Password validation policy: Choose 2 (STRONG) for production
  3. New password: Enter a strong root password
  4. Remove anonymous users: Yes
  5. Disallow root login remotely: Yes
  6. Remove test database: Yes
  7. Reload privilege tables: Yes

Creating a Database and User

# Log into MySQL as root
sudo mysql

# Create database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Create application user
CREATE USER 'myappuser'@'localhost' IDENTIFIED BY 'YourStrongPassword789!';

# Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'myappuser'@'localhost';

# Apply changes
FLUSH PRIVILEGES;

# Verify user
SELECT user, host FROM mysql.user;

# Exit
EXIT;

  Always use utf8mb4 instead of utf8. MySQL's utf8 only supports 3-byte characters, which means emojis and some special characters won't work. utf8mb4 supports full 4-byte UTF-8.

  

Configuring MySQL for Production

Edit the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Key settings to verify:

[mysqld]
# Bind to localhost only (security)
bind-address = 127.0.0.1

# Disable symbolic links (security)
symbolic-links = 0

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Performance settings (adjust based on your VPS RAM)
innodb_buffer_pool_size = 256M
max_connections = 100
# Restart MySQL
sudo systemctl restart mysql

Remote Access (Safe Method)

If you need to access MySQL from your local machine for development, don't open port 3306. Use an SSH tunnel instead:

# From your local machine
ssh -L 3306:localhost:3306 user@your-vps-ip

# Now you can connect to localhost:3306 on your local machine
# and it tunnels to your VPS MySQL

Connecting from Node.js 

// Install mysql2
// npm install mysql2

const mysql = require('mysql2/promise');

// Create connection pool
const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'myappuser',
  password: 'YourStrongPassword789!',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Example query
async function getUsers() {
  const [rows] = await pool.execute('SELECT * FROM users');
  return rows;
}

module.exports = pool;

PostgreSQL Installation & Setup

What PostgreSQL is Best For

PostgreSQL is the "advanced" choice. It's incredibly powerful, supports complex queries, has excellent JSON support (giving you MongoDB-like flexibility with relational integrity), and handles advanced features like full-text search, geospatial data, and custom types. If you need data integrity and plan to do complex reporting, PostgreSQL is your friend.

Installing PostgreSQL

# Install PostgreSQL and contrib package
sudo apt install postgresql postgresql-contrib -y

# Start and enable
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Check status
sudo systemctl status postgresql

Understanding PostgreSQL Users

PostgreSQL has its own user system separate from Linux users. By default, it creates a "postgres" superuser. Let's set it up properly.

# Switch to postgres user
sudo -i -u postgres

# Open PostgreSQL prompt
psql

# Set password for postgres user
\password postgres

# Create your application database
CREATE DATABASE myapp;

# Create application user
CREATE USER myappuser WITH ENCRYPTED PASSWORD 'YourStrongPassword321!';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO myappuser;

# Connect to myapp database to grant schema permissions
\c myapp

# Grant schema permissions (required in PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO myappuser;

# Exit
\q

# Exit postgres user shell
exit

Configuring pg_hba.conf

PostgreSQL uses pg_hba.conf to control client authentication. This is where you specify who can connect and how.

# Find your pg_hba.conf location
sudo -u postgres psql -c "SHOW hba_file;"

# Typically located at:
sudo nano /etc/postgresql/16/main/pg_hba.conf

The file controls authentication methods. Here's a secure configuration:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections (localhost only)
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 local connections (localhost only)
host    all             all             ::1/128                 scram-sha-256

# Reject everything else
host    all             all             0.0.0.0/0               reject

 Authentication Methods

  • peer: Uses OS username (only for local connections)
  • md5: Password authentication (legacy)
  • scram-sha-256: More secure password auth (recommended)
  • reject: Denies connection

Configuring postgresql.conf

sudo nano /etc/postgresql/16/main/postgresql.conf

Key settings:

# Connection settings
listen_addresses = 'localhost'    # Only listen on localhost
port = 5432

# Memory settings (adjust based on your VPS RAM)
shared_buffers = 256MB
effective_cache_size = 768MB
work_mem = 4MB

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# Restart PostgreSQL
sudo systemctl restart postgresql

Testing the Connection 

# Connect as application user
psql -U myappuser -d myapp -h localhost

# You'll be prompted for the password
# If successful, you'll see the psql prompt

# Test by listing tables
\dt

# Exit
\q

Connecting from Node.js 

// Install pg
// npm install pg

const { Pool } = require('pg');

// Create connection pool
const pool = new Pool({
  host: '127.0.0.1',
  port: 5432,
  database: 'myapp',
  user: 'myappuser',
  password: 'YourStrongPassword321!',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Example query
async function getUsers() {
  const result = await pool.query('SELECT * FROM users');
  return result.rows;
}

// Handle errors
pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

module.exports = pool;

Database Security (Critical)

This is the section that separates a hobby project from a production system. I've seen databases get hacked within hours of deployment because of poor security. Don't let that happen to you.

Every unsecured database on the internet gets scanned by bots. They will find your open port. They will try default credentials. They will ransomware your data. This isn't paranoia—it's documented reality.

Security Checklist

 Essential Security Measures

  • Enable authentication on all databases
  • Use strong, unique passwords (16+ characters)
  • Bind databases to localhost (127.0.0.1)
  • Never expose database ports to the internet
  • Create separate users for each application
  • Use least-privilege principle (only grant needed permissions)
  • Keep database software updated
  • Enable connection encryption (SSL/TLS)
  • Set up firewall rules
  • Regular security audits

Firewall Rules for Databases

Here's how to configure your firewall to protect databases:

# Check current rules
sudo ufw status

# Make sure database ports are NOT open to the public
# (They shouldn't be if you followed the steps above)

# If you accidentally opened a database port, remove it:
sudo ufw delete allow 27017
sudo ufw delete allow 3306
sudo ufw delete allow 5432

# Verify the rules
sudo ufw status verbose

SSH Tunnel for Remote Database Access

Need to access your database from your local machine? Don't open ports. Use SSH tunnels.

┌─────────────────┐          ┌─────────────────────────────────────┐
│  Your Computer  │          │             Your VPS                 │
│                 │          │                                      │
│  ┌───────────┐  │   SSH    │  ┌───────────┐    ┌──────────────┐  │
│  │ DB Client │──┼──────────┼─►│  SSH (22) │───►│   Database   │  │
│  │ :3306     │  │  Tunnel  │  │           │    │   :3306      │  │
│  └───────────┘  │          │  └───────────┘    │  (localhost) │  │
└─────────────────┘          │                    └──────────────┘  │
                             └─────────────────────────────────────┘

Your DB client connects to localhost:3306
SSH tunnel forwards this to VPS's localhost:3306
Database port never exposed to internet
# SSH Tunnel Examples

# MongoDB
ssh -L 27017:localhost:27017 user@your-vps-ip -N

# MySQL
ssh -L 3306:localhost:3306 user@your-vps-ip -N

# PostgreSQL
ssh -L 5432:localhost:5432 user@your-vps-ip -N

# The -N flag means "don't execute a remote command"
# Leave the terminal open while you need the tunnel

Tools like DBeaver, TablePlus, and MongoDB Compass support SSH tunnels natively. You can configure the tunnel in the connection settings rather than manually running SSH commands.

User Permission Best Practices 

# MongoDB - Create read-only user for reporting
db.createUser({
  user: "reportUser",
  pwd: "ReadOnlyPassword",
  roles: [{ role: "read", db: "myapp" }]
})

# MySQL - Create read-only user
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'ReadOnlyPassword';
GRANT SELECT ON myapp.* TO 'readonly'@'localhost';

# PostgreSQL - Create read-only user
CREATE USER readonly WITH ENCRYPTED PASSWORD 'ReadOnlyPassword';
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Enable SSL/TLS Connections

For additional security, enable encrypted connections to your database:

MongoDB SSL

# In mongod.conf
net:
  ssl:
    mode: requireSSL
    PEMKeyFile: /etc/ssl/mongodb.pem

MySQL SSL 

# In mysqld.cnf
[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON

PostgreSQL SSL 

# In postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'

Backups & Restore Strategy

If you don't have backups, you don't have data. It's that simple. Hard drives fail, software has bugs, and sometimes you'll accidentally delete something important. A proper backup strategy is non-negotiable for production systems.

┌─────────────────────────────────────────────────────────────────┐
│                    Backup Strategy Overview                      │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│    Database ──► Backup Script ──► Local Backup ──► Remote       │
│                  (cron job)         (/backups)     Storage      │
│                      │                  │              │         │
│                      │                  │              ▼         │
│                      ▼                  ▼         ┌─────────┐   │
│                 ┌────────┐          ┌────────┐   │  S3     │   │
│                 │ Daily  │          │ 7 days │   │  Cloud  │   │
│                 │ 2 AM   │          │ local  │   │ Storage │   │
│                 └────────┘          └────────┘   └─────────┘   │
│                                                                  │
│   Retention: 7 daily + 4 weekly + 12 monthly backups            │
└─────────────────────────────────────────────────────────────────┘

MongoDB Backup 

# Manual backup
mongodump --uri="mongodb://myappUser:password@localhost:27017/myapp" --out=/backups/mongodb/$(date +%Y%m%d)

# Compressed backup
mongodump --uri="mongodb://myappUser:password@localhost:27017/myapp" --gzip --archive=/backups/mongodb/myapp_$(date +%Y%m%d).gz

# Restore from backup
mongorestore --uri="mongodb://myappUser:password@localhost:27017/myapp" /backups/mongodb/20250124/

# Restore from compressed backup
mongorestore --uri="mongodb://myappUser:password@localhost:27017/myapp" --gzip --archive=/backups/mongodb/myapp_20250124.gz

MySQL Backup 

# Manual backup (single database)
mysqldump -u myappuser -p myapp > /backups/mysql/myapp_$(date +%Y%m%d).sql

# Compressed backup
mysqldump -u myappuser -p myapp | gzip > /backups/mysql/myapp_$(date +%Y%m%d).sql.gz

# Backup all databases
mysqldump -u root -p --all-databases > /backups/mysql/all_databases_$(date +%Y%m%d).sql

# Restore from backup
mysql -u myappuser -p myapp < /backups/mysql/myapp_20250124.sql

# Restore from compressed backup
gunzip < /backups/mysql/myapp_20250124.sql.gz | mysql -u myappuser -p myapp

PostgreSQL Backup 

# Manual backup (custom format - recommended)
pg_dump -U myappuser -Fc myapp > /backups/postgres/myapp_$(date +%Y%m%d).dump

# Plain SQL backup
pg_dump -U myappuser myapp > /backups/postgres/myapp_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -U myappuser myapp | gzip > /backups/postgres/myapp_$(date +%Y%m%d).sql.gz

# Backup all databases
pg_dumpall -U postgres > /backups/postgres/all_databases_$(date +%Y%m%d).sql

# Restore from custom format
pg_restore -U myappuser -d myapp /backups/postgres/myapp_20250124.dump

# Restore from SQL
psql -U myappuser -d myapp -f /backups/postgres/myapp_20250124.sql

Wrapping Up

Congratulations! If you've followed along, you now have a solid understanding of how to install, secure, and manage databases on a VPS. Here's what we covered:

  • VPS preparation and firewall configuration
  • Installing and configuring MongoDB, MySQL, and PostgreSQL
  • Essential security measures that actually matter
  • Backup strategies that work in production
  • Performance optimization basics
  • Monitoring and maintenance practices
  • Production-ready architecture patterns

Remember, setting up a database is just the beginning. The real work is maintaining it: keeping it secure, monitoring performance, and ensuring reliable backups. Take the time to set things up properly now, and you'll save yourself countless headaches later.