Database Setup

Install and configure MySQL or PostgreSQL on your TinyBox VPS

Choose Your Database

MySQL - Most popular relational database, excellent for web applications, WordPress, and general-purpose use.

1. Install MySQL

# Update system
apt update && apt upgrade -y

# Install MySQL Server
apt install mysql-server -y

# Start and enable MySQL
systemctl start mysql
systemctl enable mysql

# Check status
systemctl status mysql

2. Secure MySQL Installation

# Run security script
mysql_secure_installation

# Follow the prompts:
# - Set root password
# - Remove anonymous users: Y
# - Disallow remote root login: Y  
# - Remove test database: Y
# - Reload privileges: Y

Security Notice

Always set a strong root password and remove test databases. Never use root account for applications.

3. Create Database and User

# Connect to MySQL as root
mysql -u root -p

# Create database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Create user and grant privileges
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

# Apply changes and exit
FLUSH PRIVILEGES;
EXIT;

4. Configure MySQL (Optional)

# Edit MySQL configuration
nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Key settings to adjust:
# max_connections = 100
# innodb_buffer_pool_size = 256M
# query_cache_size = 16M

# Restart MySQL after changes
systemctl restart mysql

5. Test Connection

# Test connection with new user
mysql -u myapp_user -p myapp_db

# Show databases
SHOW DATABASES;

# Show current user
SELECT USER();

# Exit
EXIT;

PostgreSQL - Advanced, standards-compliant database with excellent performance and features for complex applications.

1. Install PostgreSQL

# Update system
apt update && apt upgrade -y

# Install PostgreSQL and contrib package
apt install postgresql postgresql-contrib -y

# Start and enable PostgreSQL
systemctl start postgresql
systemctl enable postgresql

# Check status
systemctl status postgresql

2. Initial Setup

# Switch to postgres user
sudo -u postgres psql

# Set password for postgres user
\password postgres

# Exit PostgreSQL prompt
\q

# Or set password from command line
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'strong_password_here';"

3. Create Database and User

# Connect to PostgreSQL
sudo -u postgres psql

# Create database
CREATE DATABASE myapp_db;

# Create user
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'strong_password_here';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

# Make user owner of database
ALTER DATABASE myapp_db OWNER TO myapp_user;

# Exit
\q

4. Configure Authentication

# Edit PostgreSQL authentication config
nano /etc/postgresql/*/main/pg_hba.conf

# Change this line for local connections:
# local   all             all                                     peer
# TO:
# local   all             all                                     md5

# Restart PostgreSQL
systemctl restart postgresql

5. Test Connection

# Test connection with new user
psql -U myapp_user -d myapp_db -h localhost

# List databases
\l

# Show current user
SELECT current_user;

# Exit
\q

Administration Tools

MySQL Tools

phpMyAdmin - Web-based interface
MySQL Workbench - Official GUI client
Adminer - Lightweight web interface
DBeaver - Universal database tool

Install phpMyAdmin:

apt install phpmyadmin -y
# Follow setup wizard
# Choose web server: apache2
# Configure with dbconfig-common: Yes

PostgreSQL Tools

pgAdmin - Official web interface
psql - Command-line client
DBeaver - Universal database tool
Adminer - Lightweight web interface

Install pgAdmin:

# Install pgAdmin4
apt install pgadmin4 -y

# Access via web browser
# Default port: 5050

Backup and Maintenance

MySQL Backup

# Backup single database
mysqldump -u root -p myapp_db > backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_backup.sql

# Restore database
mysql -u root -p myapp_db < backup.sql

Automated backup with cron job recommended

PostgreSQL Backup

# Backup single database
pg_dump -U myapp_user myapp_db > backup.sql

# Backup all databases
pg_dumpall -U postgres > all_backup.sql

# Restore database
psql -U myapp_user myapp_db < backup.sql

Use pg_basebackup for binary backups

Automated Backup Script

#!/bin/bash
# Create backup script
nano /usr/local/bin/db_backup.sh

# MySQL example:
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/home/backups"
mkdir -p $BACKUP_DIR

mysqldump -u root -p'YOUR_PASSWORD' --all-databases > $BACKUP_DIR/mysql_backup_$DATE.sql
gzip $BACKUP_DIR/mysql_backup_$DATE.sql

# Keep only last 7 backups
find $BACKUP_DIR -name "mysql_backup_*.sql.gz" -mtime +7 -delete

# Make executable
chmod +x /usr/local/bin/db_backup.sh

# Add to crontab (daily at 2 AM)
0 2 * * * /usr/local/bin/db_backup.sh

Performance Tuning

MySQL Performance

# Edit /etc/mysql/mysql.conf.d/mysqld.cnf
innodb_buffer_pool_size = 512M
max_connections = 50
query_cache_size = 32M
query_cache_limit = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

Adjust values based on available RAM

PostgreSQL Performance

# Edit /etc/postgresql/*/main/postgresql.conf
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB
max_connections = 50

Use pg_tune for automatic configuration

Common Issues & Solutions

Can't connect to database

Check if service is running and verify credentials:

                            # MySQL
systemctl status mysql
mysql -u root -p

# PostgreSQL  
systemctl status postgresql
sudo -u postgres psql
                        

Access denied for user

Verify username, password, and privileges:

                            # MySQL - Check user privileges
SHOW GRANTS FOR 'username'@'localhost';

# PostgreSQL - List users and privileges
\du
                        

Out of disk space

Clean up logs and optimize databases:

                            # Check disk usage
df -h
du -sh /var/lib/mysql
du -sh /var/lib/postgresql

# Clean MySQL binary logs
mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);"
                        

Database won't start

Check logs for error messages:

                            # MySQL logs
tail -f /var/log/mysql/error.log

# PostgreSQL logs
tail -f /var/log/postgresql/postgresql-*-main.log
                        