Install MySQL or PostgreSQL on Your Opus Host VPS

sudo apt update
sudo apt install -y mysql-server

sudo mysql_secure_installation

sudo mysql

CREATE DATABASE myapp;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'change-this-password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

mysql -u myuser -p myapp

sudo apt update
sudo apt install -y postgresql postgresql-contrib

sudo -u postgres psql

CREATE DATABASE myapp;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'change-this-password';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;
\q

psql -h localhost -U myuser -d myapp

​

Allow remote connections (optional)

1. Bind the database to 0.0.0.0 in its config (/etc/mysql/mysql.conf.d/mysqld.cnf or /etc/postgresql/*/main/postgresql.conf).
2. Update the client authentication file (pg_hba.conf for PostgreSQL).
3. Open the port in your firewall. See Firewall.
4. Restart the service.

​

Back up a database

# MySQL
mysqldump -u myuser -p myapp > myapp-backup.sql

# PostgreSQL
pg_dump -U myuser myapp > myapp-backup.sql