Database

Database Setup

Complete guide to setting up and configuring databases on your VPS. Learn MySQL, PostgreSQL, MongoDB installation, optimization, and security.

Prerequisites

Root or sudo access to your VPS
Basic understanding of database concepts
Sufficient disk space for database files
Understanding of your application requirements
Backup storage location configured

MySQL/MariaDB Setup

MySQL and MariaDB are the most popular relational database systems. MariaDB is a drop-in replacement for MySQL with enhanced features.

Installation Options:

MySQL - Oracle's official MySQL
MariaDB - Community-developed fork
Percona Server - Enhanced MySQL variant

# Ubuntu/Debian - Install MariaDB
sudo apt update
sudo apt install mariadb-server -y

# Secure MariaDB installation
sudo mysql_secure_installation

# Start and enable MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Access MySQL/MariaDB
sudo mysql -u root

# Create database and user
CREATE DATABASE myapp_db;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

# Test connection
mysql -u myapp_user -p myapp_db

PostgreSQL Setup

PostgreSQL is a powerful, open-source object-relational database system known for its robustness, extensibility, and standards compliance.

PostgreSQL Advantages:

Advanced data types and features
Strong ACID compliance
Extensible with custom functions
Excellent JSON support
Built-in replication support

# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib -y

# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Switch to postgres user
sudo -u postgres psql

# Create database and user
CREATE DATABASE myapp_db;
CREATE USER myapp_user WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;
\q

# Allow password authentication
sudo nano /etc/postgresql/12/main/pg_hba.conf

# Add this line:
# local   myapp_db    myapp_user                            md5

# Restart PostgreSQL
sudo systemctl restart postgresql

# Test connection
psql -h localhost -U myapp_user -d myapp_db

MongoDB Setup

MongoDB is a popular NoSQL document database known for its flexibility, scalability, and ease of use with modern applications.

MongoDB Features:

Document-based storage
Flexible schema design
Horizontal scaling with sharding
Built-in replication
Rich query language

# Install MongoDB (Ubuntu 20.04+)
sudo apt update
sudo apt install gnupg -y

# Add MongoDB GPG key
wget -qO - https://www.mongodb.org/static/pgp/server-6.0.asc | sudo apt-key add -

# Add MongoDB repository
echo "deb [ arch=amd64,arm64 ] https://repo.mongodb.org/apt/ubuntu focal/mongodb-org/6.0 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-6.0.list

# Install MongoDB
sudo apt update
sudo apt install mongodb-org -y

# Start MongoDB
sudo systemctl start mongod
sudo systemctl enable mongod

# Access MongoDB shell
mongosh

# Create database and user
use myapp_db

db.createUser({
  user: "myapp_user",
  pwd: "secure_password",
  roles: ["readWrite"]
})

# Test connection
mongosh -u myapp_user -p --authenticationDatabase myapp_db

Database Security

Database security is crucial to protect sensitive data. Implement multiple layers of security to safeguard your databases.

Security Layers:

Network security (firewalls, VPN)
Authentication and authorization
Encryption (data at rest and in transit)
Access controls and auditing
Regular security updates

# MySQL/MariaDB security
# Configure MySQL to listen only on localhost
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add/modify:
# bind-address = 127.0.0.1

# Restart MySQL
sudo systemctl restart mysql

# PostgreSQL security
# Configure PostgreSQL to listen only on localhost
sudo nano /etc/postgresql/12/main/postgresql.conf

# Add/modify:
# listen_addresses = 'localhost'

# Update pg_hba.conf for local connections only
sudo nano /etc/postgresql/12/main/pg_hba.conf

# MongoDB security
# Enable authentication
sudo nano /etc/mongod.conf

# Add security configuration:
# security:
#   authorization: enabled

# Create admin user
mongosh
use admin
db.createUser({
  user: "admin",
  pwd: "very_secure_password",
  roles: ["userAdminAnyDatabase", "readWriteAnyDatabase"]
})

# Restart MongoDB
sudo systemctl restart mongod

Performance Optimization

Database performance optimization ensures your applications run smoothly and efficiently. Proper configuration can significantly improve response times.

Optimization Areas:

Memory configuration
Query optimization
Indexing strategies
Connection pooling
Caching mechanisms

# MySQL performance tuning
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add performance settings:
# [mysqld]
# innodb_buffer_pool_size = 1G
# innodb_log_file_size = 256M
# max_connections = 100
# query_cache_size = 128M
# tmp_table_size = 128M
# max_heap_table_size = 128M

# PostgreSQL performance tuning
sudo nano /etc/postgresql/12/main/postgresql.conf

# Key performance settings:
# shared_buffers = 256MB
# effective_cache_size = 1GB
# work_mem = 4MB
# maintenance_work_mem = 64MB
# checkpoint_completion_target = 0.9
# wal_buffers = 16MB
# default_statistics_target = 100

# MongoDB performance tuning
sudo nano /etc/mongod.conf

# Add performance settings:
# storage:
#   wiredTiger:
#     engineConfig:
#       cacheSizeGB: 1
# systemLog:
#   destination: file
#   logAppend: true
#   path: /var/log/mongodb/mongod.log
# net:
#   maxIncomingConnections: 100

# Restart services after configuration
sudo systemctl restart mysql
sudo systemctl restart postgresql
sudo systemctl restart mongod

Backup & Recovery

Regular database backups are essential for data protection and disaster recovery. Different database systems have different backup approaches.

Backup Strategies:

Full backups (complete database)
Incremental backups (changes only)
Logical backups (SQL dumps)
Physical backups (file system copies)
Point-in-time recovery

# MySQL backup automation
# Create backup script
sudo nano /usr/local/bin/mysql_backup.sh

#!/bin/bash
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="backup_user"
MYSQL_PASSWORD="backup_password"

mkdir -p $BACKUP_DIR

# Full database backup
mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --all-databases > $BACKUP_DIR/full_backup_$DATE.sql

# Compress backup
gzip $BACKUP_DIR/full_backup_$DATE.sql

# Clean old backups (keep last 7 days)
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "MySQL backup completed: $BACKUP_DIR/full_backup_$DATE.sql.gz"

# PostgreSQL backup
pg_dump -U postgres -h localhost myapp_db > /backups/postgres_backup.sql

# MongoDB backup
mongodump --db myapp_db --out /backups/mongodb_backup

# Schedule automated backups
sudo crontab -e

# Add daily backup at 2 AM:
# 0 2 * * * /usr/local/bin/mysql_backup.sh

Monitoring & Maintenance

Regular monitoring and maintenance keep your databases running optimally and help prevent issues before they occur.

Monitoring Tasks:

Performance metrics tracking
Log analysis and error detection
Storage usage monitoring
Connection and query analysis
Regular maintenance tasks

# MySQL monitoring
# Check running queries
mysql -u root -p -e "SHOW PROCESSLIST;"

# Check database sizes
mysql -u root -p -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;"

# Monitor connections
mysql -u root -p -e "SHOW STATUS WHERE Variable_name LIKE 'Threads_%';"

# PostgreSQL monitoring
# Check active connections
psql -U postgres -c "SELECT * FROM pg_stat_activity;"

# Monitor database sizes
psql -U postgres -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;"

# MongoDB monitoring
# Check database stats
mongosh --eval "db.stats()"

# Monitor connections
mongosh --eval "db.serverStatus().connections"

# Check slow queries (enable profiling first)
mongosh
use myapp_db
db.setProfilingLevel(2, { slowms: 100 })

# View slow queries
db.system.profile.find().sort({ ts: -1 }).limit(5)

Contents

Related Docs

Backup & Restore

Comprehensive database backup and recovery strategies

Security Hardening

Secure your database installations and configurations

Monitoring Tools

Monitor database performance and health

Need Help?

Can't find what you're looking for? Our support team is here to help.

# Ubuntu/Debian - Install MariaDB sudo apt update sudo apt install mariadb-server -y # Secure MariaDB installation sudo mysql_secure_installation # Start and enable MariaDB sudo systemctl start mariadb sudo systemctl enable mariadb # Access MySQL/MariaDB sudo mysql -u root # Create database and user CREATE DATABASE myapp_db; CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_password'; GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost'; FLUSH PRIVILEGES; EXIT; # Test connection mysql -u myapp_user -p myapp_db

# Install PostgreSQL sudo apt update sudo apt install postgresql postgresql-contrib -y # Start and enable PostgreSQL sudo systemctl start postgresql sudo systemctl enable postgresql # Switch to postgres user sudo -u postgres psql # Create database and user CREATE DATABASE myapp_db; CREATE USER myapp_user WITH PASSWORD 'secure_password'; GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user; \q # Allow password authentication sudo nano /etc/postgresql/12/main/pg_hba.conf # Add this line: # local myapp_db myapp_user md5 # Restart PostgreSQL sudo systemctl restart postgresql # Test connection psql -h localhost -U myapp_user -d myapp_db

# Install MongoDB (Ubuntu 20.04+) sudo apt update sudo apt install gnupg -y # Add MongoDB GPG key wget -qO - https://www.mongodb.org/static/pgp/server-6.0.asc | sudo apt-key add - # Add MongoDB repository echo "deb [ arch=amd64,arm64 ] https://repo.mongodb.org/apt/ubuntu focal/mongodb-org/6.0 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-6.0.list # Install MongoDB sudo apt update sudo apt install mongodb-org -y # Start MongoDB sudo systemctl start mongod sudo systemctl enable mongod # Access MongoDB shell mongosh # Create database and user use myapp_db db.createUser({ user: "myapp_user", pwd: "secure_password", roles: ["readWrite"] }) # Test connection mongosh -u myapp_user -p --authenticationDatabase myapp_db

# MySQL/MariaDB security # Configure MySQL to listen only on localhost sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf # Add/modify: # bind-address = 127.0.0.1 # Restart MySQL sudo systemctl restart mysql # PostgreSQL security # Configure PostgreSQL to listen only on localhost sudo nano /etc/postgresql/12/main/postgresql.conf # Add/modify: # listen_addresses = 'localhost' # Update pg_hba.conf for local connections only sudo nano /etc/postgresql/12/main/pg_hba.conf # MongoDB security # Enable authentication sudo nano /etc/mongod.conf # Add security configuration: # security: # authorization: enabled # Create admin user mongosh use admin db.createUser({ user: "admin", pwd: "very_secure_password", roles: ["userAdminAnyDatabase", "readWriteAnyDatabase"] }) # Restart MongoDB sudo systemctl restart mongod

# MySQL performance tuning sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf # Add performance settings: # [mysqld] # innodb_buffer_pool_size = 1G # innodb_log_file_size = 256M # max_connections = 100 # query_cache_size = 128M # tmp_table_size = 128M # max_heap_table_size = 128M # PostgreSQL performance tuning sudo nano /etc/postgresql/12/main/postgresql.conf # Key performance settings: # shared_buffers = 256MB # effective_cache_size = 1GB # work_mem = 4MB # maintenance_work_mem = 64MB # checkpoint_completion_target = 0.9 # wal_buffers = 16MB # default_statistics_target = 100 # MongoDB performance tuning sudo nano /etc/mongod.conf # Add performance settings: # storage: # wiredTiger: # engineConfig: # cacheSizeGB: 1 # systemLog: # destination: file # logAppend: true # path: /var/log/mongodb/mongod.log # net: # maxIncomingConnections: 100 # Restart services after configuration sudo systemctl restart mysql sudo systemctl restart postgresql sudo systemctl restart mongod

# MySQL backup automation # Create backup script sudo nano /usr/local/bin/mysql_backup.sh #!/bin/bash BACKUP_DIR="/backups/mysql" DATE=$(date +%Y%m%d_%H%M%S) MYSQL_USER="backup_user" MYSQL_PASSWORD="backup_password" mkdir -p $BACKUP_DIR # Full database backup mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --all-databases > $BACKUP_DIR/full_backup_$DATE.sql # Compress backup gzip $BACKUP_DIR/full_backup_$DATE.sql # Clean old backups (keep last 7 days) find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete echo "MySQL backup completed: $BACKUP_DIR/full_backup_$DATE.sql.gz" # PostgreSQL backup pg_dump -U postgres -h localhost myapp_db > /backups/postgres_backup.sql # MongoDB backup mongodump --db myapp_db --out /backups/mongodb_backup # Schedule automated backups sudo crontab -e # Add daily backup at 2 AM: # 0 2 * * * /usr/local/bin/mysql_backup.sh

# MySQL monitoring # Check running queries mysql -u root -p -e "SHOW PROCESSLIST;" # Check database sizes mysql -u root -p -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;" # Monitor connections mysql -u root -p -e "SHOW STATUS WHERE Variable_name LIKE 'Threads_%';" # PostgreSQL monitoring # Check active connections psql -U postgres -c "SELECT * FROM pg_stat_activity;" # Monitor database sizes psql -U postgres -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;" # MongoDB monitoring # Check database stats mongosh --eval "db.stats()" # Monitor connections mongosh --eval "db.serverStatus().connections" # Check slow queries (enable profiling first) mongosh use myapp_db db.setProfilingLevel(2, { slowms: 100 }) # View slow queries db.system.profile.find().sort({ ts: -1 }).limit(5)