A properly configured database server is the backbone of any dynamic web application. Whether you choose MySQL for its widespread compatibility or PostgreSQL for its advanced features and standards compliance, your SakuraHost VPS provides the ideal environment for running a production database. This guide covers installation, security hardening, user management, and backup strategies for both database systems.
Which Database Should You Choose? MySQL is ideal for WordPress, WHMCS, and most PHP applications. PostgreSQL excels with complex queries, JSON data, geospatial operations, and modern frameworks like Django, Rails, and Next.js with Prisma. Both are production-ready on SakuraHost VPS.
Part 1: MySQL 8 Server Setup
Installation
Install MySQL 8 Server:
sudo apt update
sudo apt install mysql-server -y
sudo systemctl start mysql
sudo systemctl enable mysql
Run the security hardening script:
sudo mysql_secure_installation
This script will prompt you to set a root password, remove anonymous users, disable remote root login, remove the test database, and reload privilege tables. Answer Yes to all prompts.
Creating Databases and Users
Log in to MySQL as root:
sudo mysql
Create a database and a dedicated user with limited privileges:
-- Create the database
CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create a user with a strong password
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'YourStrongPassword123!';
-- Grant privileges only on the specific database
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON myapp_production.* TO 'myapp_user'@'localhost';
-- Apply changes
FLUSH PRIVILEGES;
Security Principle: Never use the root account for application connections. Always create dedicated users with the minimum required privileges for each application. Use strong, unique passwords for every database user.
MySQL Performance Tuning
Edit the MySQL configuration for your VPS resources:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Recommended settings for a 4GB RAM VPS:
[mysqld]
# InnoDB settings
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
# Query cache (disabled in MySQL 8, use ProxySQL if needed)
# Connection settings
max_connections = 150
wait_timeout = 300
interactive_timeout = 300
# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Restart MySQL to apply: sudo systemctl restart mysql
Part 2: PostgreSQL Server Setup
Installation
Install PostgreSQL:
sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl start postgresql
sudo systemctl enable postgresql
Creating Databases and Users
Switch to the postgres system user and access the shell:
sudo -u postgres psql
-- Create a new user
CREATE USER myapp_user WITH PASSWORD 'YourStrongPassword123!';
-- Create a database owned by the user
CREATE DATABASE myapp_production OWNER myapp_user;
-- Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE myapp_production TO myapp_user;
-- Exit
q
PostgreSQL Authentication Configuration
Edit the pg_hba.conf file to configure how clients authenticate:
sudo nano /etc/postgresql/16/main/pg_hba.conf
For local application connections, ensure this line exists:
# TYPE DATABASE USER ADDRESS METHOD
local all myapp_user md5
host all myapp_user 127.0.0.1/32 md5
PostgreSQL Performance Tuning
sudo nano /etc/postgresql/16/main/postgresql.conf
Recommended settings for a 4GB RAM VPS:
# Memory
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB
# Write-Ahead Log
wal_buffers = 16MB
checkpoint_completion_target = 0.9
# Query planner
random_page_cost = 1.1
effective_io_concurrency = 200
# Connections
max_connections = 100
# Logging
log_min_duration_statement = 1000
Restart PostgreSQL: sudo systemctl restart postgresql
3. Database Backup Strategies
MySQL Automated Backups
#!/bin/bash
# /usr/local/bin/backup-mysql.sh
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR
mysqldump --all-databases --single-transaction --routines --triggers | gzip > "$BACKUP_DIR/full_backup_$DATE.sql.gz"
# Keep only last 7 days
find $BACKUP_DIR -type f -mtime +7 -delete
PostgreSQL Automated Backups
#!/bin/bash
# /usr/local/bin/backup-postgres.sh
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/full_backup_$DATE.sql.gz"
find $BACKUP_DIR -type f -mtime +7 -delete
Schedule daily backups with cron:
sudo crontab -e
# Add: 0 2 * * * /usr/local/bin/backup-mysql.sh
# Or: 0 2 * * * /usr/local/bin/backup-postgres.sh
4. Security Best Practices
- Bind to localhost only - Unless remote access is required, databases should only accept local connections. Verify
bind-address = 127.0.0.1in MySQL orlisten_addresses = 'localhost'in PostgreSQL - Never expose database ports to the internet - Use SSH tunnels for remote administration
- Regular updates - Keep your database software patched with
sudo apt update && sudo apt upgrade - Enable slow query logging - Identify and optimize poorly performing queries
- Test your backups - A backup you have never restored is not a backup. Periodically test restore procedures
Further Reading: For MySQL documentation, visit dev.mysql.com/doc. For PostgreSQL, see postgresql.org/docs. The DigitalOcean Database Tutorials are also helpful. Need a managed database? Contact SakuraHost Support.