How to install databases (MySQL/MariaDB and PostgreSQL) on a Linux VPS

Part A — MySQL / MariaDB

Ubuntu/Debian typically install MySQL by default. On AlmaLinux/Rocky, the default server is MariaDB (a drop-in replacement for MySQL). Steps below cover both.

Step A1 — Install the database server

Ubuntu/Debian (MySQL):

apt install -y mysql-server
systemctl enable --now mysql

AlmaLinux/Rocky/CentOS (MariaDB):

dnf install -y mariadb-server
systemctl enable --now mariadb

Step A2 — Secure the installation

Run the security script to set a root password and remove unsafe defaults:

mysql_secure_installation

If prompted about VALIDATE PASSWORD, you can choose a strength policy. For production, pick a strong password.

Step A3 — Create a database and a user

mysql -u root -p

CREATE DATABASE myappdb;
CREATE USER 'myappuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON myappdb.* TO 'myappuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Using @'localhost' limits the user to local connections only (safer). See next step for remote access.

Step A4 — (Optional) Enable remote access

1. Edit the server config and listen on all interfaces:

   Ubuntu/Debian (MySQL)

   nano /etc/mysql/mysql.conf.d/mysqld.cnf
   # change:
   # bind-address = 127.0.0.1
   # to:
   bind-address = 0.0.0.0

   AlmaLinux/Rocky (MariaDB)

   nano /etc/my.cnf.d/server.cnf
   # in [mysqld] section, set:
   bind-address=0.0.0.0

2. Create a user allowed from your app server IP (replace with your real IP/Subnet):

   mysql -u root -p

   CREATE USER 'myappuser'@'203.0.113.10' IDENTIFIED BY 'StrongPassword123!';
   GRANT ALL PRIVILEGES ON myappdb.* TO 'myappuser'@'203.0.113.10';
   FLUSH PRIVILEGES;
   EXIT;

3. Restart the service:

   # Ubuntu/Debian
   systemctl restart mysql
   
   # AlmaLinux/Rocky
   systemctl restart mariadb

Step A5 — Test & verify

# Local test
mysql -u myappuser -p myappdb

# Remote test (run from your app host/laptop if you have mysql client)
mysql -h YOUR_DB_SERVER_IP -u myappuser -p myappdb

If connection fails, check firewall rules and the user’s allowed host.

Step A6 — Backup & restore

# Backup a single DB
mysqldump -u root -p myappdb > /root/myappdb_$(date +%F).sql

# Restore
mysql -u root -p myappdb < /root/myappdb_2025-01-01.sql

Schedule backups via cron and store copies off-server.

Part B — PostgreSQL

PostgreSQL is an advanced open-source RDBMS with strong reliability and features (roles, extensions, strict typing).

Step B1 — Install the database server

Ubuntu/Debian:

apt install -y postgresql
systemctl enable --now postgresql

AlmaLinux/Rocky/CentOS:

dnf install -y postgresql-server postgresql
postgresql-setup --initdb
systemctl enable --now postgresql

Step B2 — Secure the postgres superuser

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'StrongPassword123!';"

You’ll use the postgres Linux account to administer the DB server.

Step B3 — Create a database and a role (user)

sudo -u postgres createuser --pwprompt myappuser
sudo -u postgres createdb -O myappuser myappdb

Alternatively, via psql:

sudo -u postgres psql
CREATE ROLE myappuser WITH LOGIN PASSWORD 'StrongPassword123!';
CREATE DATABASE myappdb OWNER myappuser;
\q

Step B4 — (Optional) Enable remote access

1. Find your config file paths (works everywhere):

   sudo -u postgres psql -c "SHOW config_file;"
   sudo -u postgres psql -c "SHOW hba_file;"

2. Edit postgresql.conf and listen on all interfaces:

   # set:
   listen_addresses = '*'

3. Edit pg_hba.conf to allow your app host (replace with your IP):

   # Add near the top:
   host    all     all     203.0.113.10/32     md5

4. Restart PostgreSQL:

   systemctl restart postgresql

Step B5 — Test & verify

# Local test
sudo -u postgres psql -d myappdb -c '\dt'

# Remote test (from your app host/laptop)
psql -h YOUR_DB_SERVER_IP -U myappuser -d myappdb -W

If connection fails, re-check listen_addresses, pg_hba.conf and firewall rules.

Step B6 — Backup & restore

# Backup a single DB
sudo -u postgres pg_dump myappdb > /root/myappdb_$(date +%F).sql

# Restore
sudo -u postgres psql -d myappdb < /root/myappdb_2025-01-01.sql

For large databases, consider pg_dump -Fc (custom format) and pg_restore.

Firewall rules (Cloud + OS)

LifeinCloud Cloud Firewall: If enabled, add inbound rules from your trusted IP(s):

• MySQL/MariaDB — TCP 3306
• PostgreSQL — TCP 5432

Inside the VPS:

# Allow from anywhere (testing only)
ufw allow 3306/tcp
ufw allow 5432/tcp

# Safer: allow only from your app server IP
ufw allow from 203.0.113.10 to any port 3306 proto tcp
ufw allow from 203.0.113.10 to any port 5432 proto tcp

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --add-port=5432/tcp --permanent
# Safer (source-restricted):
firewall-cmd --add-rich-rule='rule family="ipv4" source address="203.0.113.10/32" port port="3306" protocol="tcp" accept' --permanent
firewall-cmd --add-rich-rule='rule family="ipv4" source address="203.0.113.10/32" port port="5432" protocol="tcp" accept' --permanent
firewall-cmd --reload

Open only what you need, and prefer IP-restricted rules for production.

Troubleshooting & common errors

Appendix: Handy commands

# MySQL / MariaDB
systemctl status mysql   # Ubuntu/Debian
systemctl status mariadb # AlmaLinux/Rocky

# PostgreSQL
systemctl status postgresql

# Systemd journal
journalctl -u mysql -e
journalctl -u mariadb -e
journalctl -u postgresql -e

# Debian/Ubuntu PostgreSQL logs
ls /var/log/postgresql/