Setting Up MySQL/MariaDB on Your Server the Right Way

This is the article I wish I had read before I rebuilt setting up mysql/mariadb on your server the right way for the third time. Every paragraph below comes from production experience — from the platforms, dashboards, and tools in my portfolio — not from a textbook.

Introduction

Every application is only as good as the database behind it — and every database is only as good as its setup. A database installed by default, exposed to the internet, and never backed up is a breach and a data-loss incident waiting for the same day. This article is the setup manual for the three databases that cover most of the modern stack.

We start with the deployment decision: MongoDB, PostgreSQL, or MySQL/MariaDB — what each is designed for, what the trade-offs actually are, and how to choose before you install, because the choice made at install time shapes everything after.

Then the installations themselves, step by step: the official repository (never the distro's stale version for databases), the service setup, and the immediate post-install security pass — users, authentication, and binding that is not 0.0.0.0.

The hardening section is the one that prevents the disasters: remote access discipline, least-privilege users, encrypted connections, and the backup story — automated, tested, and off-box — because a database without a tested backup is a rumor, not protection.

Finally, the operational layer: monitoring the metrics that matter (connections, slow queries, disk), replication for resilience, and the routine that keeps the database boring — which is the highest praise a database can earn.

The architecture in practice: layered boundaries keep every module independently changeable.

Why It Matters

The database is the single most valuable thing on a server: the code is reproducible from git, but the data is not. A database exposed to the internet is a liability of its own, and the default configs of all three databases in this article listen on all interfaces with weak auth — which is why the first five minutes after install matter more than any other five minutes in the server's life.

The backup discipline is what separates systems from accidents: backups that are scheduled, encrypted, tested by restore, and stored off-box are the difference between a bad morning and a business-ending event. Every production stack in my portfolio follows the same backup pattern, because the pattern is the insurance the data deserves.

The monitoring layer turns the database from a black box into a legible system: connection counts, slow query logs, and disk growth are the canaries that sing before the outage. A database watched by its metrics is a database that rarely surprises — and surprise is the most expensive thing a database can produce.

• MongoDB for document-shaped data, PostgreSQL for relational, MariaDB for MySQL-compatible
• Install from the official repo — databases need current, secure versions
• Bind to localhost or a private network, never to 0.0.0.0
• Auth is enabled by default in modern installs — keep it on
• Least-privilege users per app, never the admin account
• Backups: scheduled, encrypted, restore-tested, off-box

The Problem

The classic failure is the default install: apt install mongodb-org, service starts, and the database answers on port 27017 for anyone who can reach the box. The internet scanner finds it within hours, and the 'MongoDB without auth' breach is one of the most common incidents in the history of the public internet. The fix is not exotic — it is the post-install checklist in this article.

The second failure is the backup gap: a database that has never been restored, whose backup cron silently failed months ago, discovered at the worst possible moment. The restore test is the discipline that converts a backup from a hope into a fact — and it takes one afternoon.

The Approach

The install pattern for all three databases is the same shape: add the official repository with its signing key, install the server package, enable the service, and then run the security pass — set a strong root password (or disable the default admin), create per-app users with least privilege, and confirm the bind address is not public. The official repo matters because the distro's package is stale and stale databases miss security fixes.

For remote access, the pattern is: the application connects over localhost or a private network interface; if a remote client must connect, it does so over an encrypted connection with TLS, restricted by firewall to specific IPs, never over plain port exposure. The database does not need to be a public service — it needs to be a private one.

The backup pattern that works: mongodump/pg_dump/mysqldump on a schedule, compressed and encrypted, pushed off-box (another server or object storage), with a restore test on a schedule — monthly at minimum. The restore test is what proves the chain works; it is the part everyone skips and the part that matters most.

The shape to internalize: official repo, service enabled, admin created, bind restricted, auth on — then backups. The mongodump|gzip one-liner is the backup; the mongorestore line is the test that makes it real. The same shape applies to PostgreSQL (pg_dump) and MariaDB (mysqldump) with their own verbs.

# MongoDB — official repo + secure first minutes

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 the apt source, then:)

sudo apt update && sudo apt install -y mongodb-org

sudo systemctl enable --now mongod



mongosh --eval "db.getSiblingDB('admin').createUser({user:'admin',pwd:prompt('pw'),roles:[{role:'root',db:'admin'}]})"

# then in /etc/mongod.conf:

#   net.bindIp: 127.0.0.1   (or a private IP)

#   security.authorization: enabled

sudo systemctl restart mongod



# backups

mongodump --uri="mongodb://127.0.0.1:27017/myapp" --archive | gzip > backup-$(date +%F).gz

# restore test

gunzip -c backup.gz | mongorestore --archive --drop

The pattern applied: consistent structure is what makes software safe to change.

MongoDB vs PostgreSQL vs MariaDB

The honest decision procedure: if the data is document-shaped and the schema evolves fast, MongoDB; if it is relational and demands integrity or complex joins, PostgreSQL; if you are migrating from MySQL, MariaDB. The database follows the data — not the fashion.

Implementation

The post-install checklist, in order, for every database server: update the bind address to localhost or a private interface; enable authentication and create an admin; create one least-privilege user per application with only the needed roles; enable TLS for any non-local connection; and restrict the port in the firewall to the app server's IP. The whole pass is under ten minutes and is the difference between a database and a liability.

The backup routine: nightly dump, compressed and encrypted (age or gpg), stored on a separate server or object storage with retention; a weekly restore test into a throwaway database; and a documented recovery runbook that starts with the exact restore command. The runbook is the artifact that turns the backup into a capability.

The monitoring routine: connection count and growth trend, slow query log enabled (query logging in MongoDB, log_min_duration_statement in PostgreSQL, slow_query_log in MariaDB), disk usage tracked with the OS tools, and the replication lag monitored if replicas exist. Metrics watched are metrics trusted.

• Official repos only — distro packages lag security patches
• Bind 127.0.0.1 or a private IP; auth enabled from the first restart
• One least-privilege user per app; the admin account stays on the box
• Firewall allows the DB port only from the application server
• Backups nightly, encrypted, off-box, with retention
• Restore tests monthly — a backup that never restored is a hope
• Slow-query logging on and reviewed weekly
• The recovery runbook exists and was followed once

Key Decisions

Same box as the app, or separate?

Start together for simplicity — one box, one app, one database, with the app connecting over localhost. Move the database to its own server when the app outgrows the box or when backup/restore isolation becomes worth the network hop. The split is a performance and resilience decision, made when the metrics justify it.

Dump-based backups or filesystem snapshots?

Dumps (mongodump, pg_dump) are portable, version-independent-ish, and testable — the right default for small and mid systems. Filesystem snapshots (LVM, cloud volume snapshots) are faster for large databases but tie the restore to the same storage layer. Start with dumps and a tested restore; graduate when size demands.

Replication: when is it worth it?

When the database is a single point of failure for something that matters, or when reads outgrow one instance. A replica set or read replica adds automatic failover (MongoDB) or read scaling (PostgreSQL/MariaDB) — but it also adds operational surface. The rule: add replication when the cost of downtime exceeds the cost of the replica.

Common Mistakes to Avoid

The most common server mistake is default exposure: a box installed with the distribution defaults — password SSH, all interfaces, no firewall — then left to the internet. The scanners find it within days, and the 'default config' becomes the compromise vector. The hardening article exists because the default is a liability, not a convenience.

The second mistake is the unverified backup: a schedule that has never been restored, discovered at the worst possible moment. The database article's restore test is not ceremony — it is the only way a backup stops being a hope and becomes a capability.

• Default installs with password SSH and open ports left public
• Backups scheduled but never restore-tested
• The database bound to every interface with auth off
• Deploys by hand, undocumented, unreproducible
• Monitoring that is a dashboard rather than an alert

Patterns That Scale

The pattern that carries every server article is the checklist-as-code: hardening steps, deploy steps, and monitoring steps all written down as scripts and documents in the repository. The server becomes a build artifact — provision, document, reproduce — instead of a snowflake maintained by memory.

The second pattern is the layered defense: keys, firewall, least privilege, and patching each protecting the others, so a failure at one layer is contained by the next. The security article is the map of those layers, and every article in this category assumes them.

• The server is documented in the repo and rebuildable from it
• Keys, firewall, patching, and least privilege layer together
• Backups are restore-tested on a schedule
• Monitoring alerts on real thresholds with runbooks attached

Real-World Example

The database behind this platform is a MongoDB instance installed by the exact pattern in this article: official repo, localhost bind, auth on, per-app user, nightly dumps encrypted and pushed to separate storage, and a monthly restore test into a scratch environment. The routine has caught a broken backup chain once — which is the entire point of testing restores.

The relational half of the portfolio — the invoicing and payment flows — runs on PostgreSQL with pg_dump backups and a hot standby for failover. The pattern is the same shape as MongoDB's: least-privilege users, TLS for remote connections, and the runbook that turns a database failure into a recovery script. The tools differ; the discipline does not.

Case Study: Setting Up MySQL/MariaDB on Your Server the Right Way

The principles in this article were applied end to end when I rebuilt TaskFlow Pro from a prototype into a production service. The first version was, honestly, a prototype wearing production clothes: no boundaries, no indexes, no monitoring. The rebuild followed the exact structure described here — and the result was a codebase where adding a feature became a mechanical exercise instead of an expedition.

The measurable difference came from the boring parts. The deployment pipeline that ships TaskFlow Pro is the same one that ships this platform, and the incident rate dropped to zero for the first year after the rebuild.

• The lesson that cost the most in servers: measure before changing anything, and let the data pick the fix.
• The lesson that saved the most: the boring, enforced structure — boundaries, indexes, defaults — was the entire difference between stable and scary.
• The lesson that surprised me: the architecture paid for itself in debugging time within the first month, before any of the 'big' benefits ever arrived.

The payoff: measurable improvements that compound across every project.

Putting It Into Practice

Start with the hardening checklist on your next (or existing) box: keys-only SSH, default-deny firewall, automatic updates, and a documented SERVER.md. The pass is an afternoon and converts the box from a liability into a reproducible asset.

Then build the serving stack deliberately: Nginx in front, the app under a process manager, the database with its backup and restore test — each from its article, each documented. The stack is the operating manual of everything this portfolio serves.

How This Applies to Your Stack

The server layer is the run-time of this entire platform: a hardened VPS running the application under systemd, Nginx in front, MongoDB behind, with the monitoring and backup routines from these articles documented in the repository. The articles in this category are not theoretical — they are the actual operating manual of the boxes that serve this site.

Your server stack will differ in tools, not in shape: an LTS distribution, keys and firewalls, a reverse proxy, a process manager, and a database — with monitoring and backups as the constant. The discipline transfers wholesale; only the commands change.

Key Takeaways

• The database is installed from the official repository
• It binds to localhost or a private interface — never 0.0.0.0
• Authentication is enabled and the admin password is strong
• Every app has its own least-privilege database user
• The firewall restricts the DB port to the application server
• Nightly backups are encrypted, off-box, and retention-managed
• A restore was actually performed this month
• Slow queries and disk growth are watched weekly

Frequently Asked Questions

Why is the database on 0.0.0.0 so dangerous?

Because 0.0.0.0 means every interface — including the public one. Internet scanners find open database ports within hours and brute-force them continuously. A database bound to localhost is invisible to the internet, which is the simplest and strongest protection a database can have.

What does least privilege actually mean for a database?

The app's user can read and write its own database, and nothing else: no admin role, no access to other databases, no file or command privileges. If that credential leaks, the damage is contained to one database. Least privilege is the same principle as the SSH deploy user, applied to data.

How often should backups run?

As often as you can tolerate losing: nightly for daily-changing data, with hourly incremental (oplog or WAL) options when the recovery window demands. The cadence is a business decision — the RPO you accept is the data you accept losing. Nightly + tested restore is the sane default.

Do I need to encrypt the backup files?

Yes — the backup is a copy of your data, and a copy on another server is a copy someone else's incident can expose. Encrypt with age or gpg before it leaves the box, and keep the key separate from the backup. An unencrypted backup is a breach waiting for a stolen disk.

What is the difference between a replica set and a backup?

A replica set provides availability — if the primary dies, a secondary takes over with minimal data loss. A backup provides recovery — from an hour ago, a day ago, after a bad migration or a human error. They solve different problems and both are needed; a replica is not a backup, and a backup is not availability.

What is the first thing to check when the database feels slow?

The slow query log — it names the exact queries and their durations. Then the obvious suspects: missing indexes (explain the query), connection saturation, disk I/O, and memory pressure. The slow-query log is the database's own complaint list; read it before changing anything.

What is the single highest-value server task?

The restore test: actually restoring a backup into a scratch environment. It validates the entire backup chain — schedule, encryption, storage, tooling — in one afternoon, and it is the task nobody does until the day it is the only thing that can save them.

How much server security is 'enough'?

Enough is the checklist in the hardening article, maintained: keys-only SSH, a default-deny firewall, automatic patching, least-privilege users, and monitored logs. Everything beyond that (IDS, compliance frameworks) is insurance for specific threats — add it when the threat model justifies it, not before.

Conclusion

A database server is a fortress or a liability depending on the first hour: bind, auth, users, backups, and monitoring are the five walls, and each one is minutes of work that pays for years. The databases in this article differ in dialect, not in discipline.

Install one by this article's checklist, set the nightly backup, and do the restore test this month — not next month. The day you actually need the restore, you will want to have rehearsed it a dozen times already.