This guide walks through installing and securing MySQL, MariaDB, and PostgreSQL on a LumaDock Linux VPS. It covers database creation, optional remote access, safe firewall rules, backups, and basic verification commands.
Requirements
You need a LumaDock VPS running Ubuntu, Debian, AlmaLinux, Rocky, or CentOS. Connect via SSH as root or a sudo-enabled user. Update packages before beginning.
Connect and update your system
ssh root@YOUR_SERVER_IP
Ubuntu and Debian:
apt update && apt -y upgrade
AlmaLinux, Rocky, and CentOS:
dnf -y update
MySQL and MariaDB
Ubuntu and Debian install MySQL by default. AlmaLinux and Rocky install MariaDB, which is fully compatible with MySQL.
Step 1 – Install the database server
Ubuntu and Debian:
apt install -y mysql-server systemctl enable --now mysql
AlmaLinux, Rocky, and CentOS:
dnf install -y mariadb-server systemctl enable --now mariadb
Step 2 – Secure the installation
mysql_secure_installation
Follow the prompts to set a root password and remove unsafe defaults.
Step 3 – 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;
Step 4 – Optional remote access
Edit the configuration to listen on all interfaces.
Ubuntu and Debian:
nano /etc/mysql/mysql.conf.d/mysqld.cnf
<change>
bind-address = 127.0.0.1
<to>
bind-address = 0.0.0.0
AlmaLinux and Rocky:
nano /etc/my.cnf.d/server.cnf bind-address=0.0.0.0
Create a user allowed from your application server’s IP.
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;
Restart the service.
Ubuntu and Debian:
systemctl restart mysql
AlmaLinux and Rocky:
systemctl restart mariadb
Step 5 – Test the connection
Local test:
mysql -u myappuser -p myappdb
Remote test:
mysql -h YOUR_DB_SERVER_IP -u myappuser -p myappdb
Step 6 – Backups and restores
Backup:
mysqldump -u root -p myappdb > /root/myappdb_$(date +%F).sql
Restore:
mysql -u root -p myappdb < /root/myappdb_2025-01-01.sql
PostgreSQL
PostgreSQL is available on all major Linux distributions and offers strong features for production systems. Install, secure, and configure it with the steps below.
Step 1 – Install PostgreSQL
Ubuntu and Debian:
apt install -y postgresql systemctl enable --now postgresql
AlmaLinux, Rocky, and CentOS:
dnf install -y postgresql-server postgresql postgresql-setup --initdb systemctl enable --now postgresql
Step 2 – Set a password for the postgres user
sudo -u postgres psql -c
"ALTER USER postgres WITH PASSWORD 'StrongPassword123!';"
Step 3 – Create a database and role
Command method:
sudo -u postgres psql
CREATE ROLE myappuser WITH LOGIN PASSWORD 'StrongPassword123!';
CREATE DATABASE myappdb OWNER myappuser;
\q
Step 4 – Optional remote access
Find configuration paths:
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"
Enable external connections:
# postgresql.conf listen_addresses = '*'
Allow your application IP:
# pg_hba.conf host all all 203.0.113.10/32 md5
Restart PostgreSQL:
systemctl restart postgresql
Step 5 – Test connections
Local:
sudo -u postgres psql -d myappdb -c '\dt'
Remote:
psql -h YOUR_DB_SERVER_IP -U myappuser -d myappdb -W
Step 6 – Backup and restore
Backup:
sudo -u postgres pg_dump myappdb > /root/myappdb_$(date +%F).sql
Restore:
sudo -u postgres psql -d myappdb < /root/myappdb_2025-01-01.sql
Firewall rules
If you enable the LumaDock Cloud Firewall, allow traffic only from trusted sources.
MySQL and MariaDB use TCP 3306.
PostgreSQL uses TCP 5432.
UFW on Ubuntu and Debian
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
Firewalld on AlmaLinux and Rocky
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
Troubleshooting
Connection refused can indicate firewall blocks or bind settings.
Access denied errors usually mean user host mismatch.
PostgreSQL’s “no pg_hba.conf entry” means the client IP is not allowed.
Service startup failures may indicate port conflicts or configuration errors.
Handy commands
MySQL and MariaDB service checks:
systemctl status mysql systemctl status mariadb
PostgreSQL service check:
systemctl status postgresql
Database logs live under each system’s journal or, for PostgreSQL on Debian-based systems, inside /var/log/postgresql/.
Your LumaDock VPS now has working database servers that you can use for applications or remote services. Regular backups, restricted firewall rules, and strong passwords build a safe and reliable setup.