Database Administration Master Class #

Engineering-Grade Reference Manual for Database Administration
PostgreSQL, MySQL, MongoDB, and Redis administration

---

Table of Contents #

1. PostgreSQL
2. MySQL/MariaDB
3. MongoDB
4. Redis
5. Backup & Recovery
6. Performance Tuning
7. Monitoring

---

PostgreSQL #

🔹 Installation & Setup #

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Connect
sudo -u postgres psql

🔹 Database Operations #

-- Create database
CREATE DATABASE myapp;

-- List databases
\l

-- Connect to database
\c myapp

-- Create user
CREATE USER myuser WITH PASSWORD 'mypassword';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

-- Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- Query data
SELECT * FROM users;

-- Update data
UPDATE users SET email = 'newemail@example.com' WHERE username = 'john';

-- Delete data
DELETE FROM users WHERE username = 'john';

🔹 Backup & Restore #

# Backup database
pg_dump myapp > myapp_backup.sql

# Backup with compression
pg_dump myapp | gzip > myapp_backup.sql.gz

# Restore database
psql myapp < myapp_backup.sql

# Restore compressed backup
gunzip < myapp_backup.sql.gz | psql myapp

---

MySQL/MariaDB #

🔹 Installation #

# Ubuntu/Debian
sudo apt install mysql-server

# Secure installation
sudo mysql_secure_installation

# Connect
mysql -u root -p

🔹 Database Operations #

-- Create database
CREATE DATABASE myapp;

-- Use database
USE myapp;

-- Create user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

-- Create table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🔹 Backup & Restore #

# Backup database
mysqldump -u root -p myapp > myapp_backup.sql

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

# Restore database
mysql -u root -p myapp < myapp_backup.sql

---

MongoDB #

🔹 Installation #

# Ubuntu/Debian
wget -qO - https://www.mongodb.org/static/pgp/server-6.0.asc | sudo apt-key add -
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
sudo apt update
sudo apt install mongodb-org

# Start service
sudo systemctl start mongod
sudo systemctl enable mongod

# Connect
mongosh

🔹 Database Operations #

// Show databases
show dbs

// Use/create database
use myapp

// Create collection and insert
db.users.insertOne({
    username: "john",
    email: "john@example.com",
    created_at: new Date()
})

// Find documents
db.users.find()
db.users.find({ username: "john" })

// Update document
db.users.updateOne(
    { username: "john" },
    { $set: { email: "newemail@example.com" } }
)

// Delete document
db.users.deleteOne({ username: "john" })

// Create index
db.users.createIndex({ username: 1 })

🔹 Backup & Restore #

# Backup database
mongodump --db myapp --out /backup/

# Restore database
mongorestore --db myapp /backup/myapp/

---

Redis #

🔹 Installation #

# Ubuntu/Debian
sudo apt install redis-server

# Start service
sudo systemctl start redis
sudo systemctl enable redis

# Connect
redis-cli

🔹 Operations #

# Set key
SET mykey "Hello"

# Get key
GET mykey

# Set with expiration (seconds)
SETEX mykey 60 "Hello"

# Delete key
DEL mykey

# Check if key exists
EXISTS mykey

# List all keys
KEYS *

# Set hash
HSET user:1 name "John"
HSET user:1 email "john@example.com"

# Get hash field
HGET user:1 name

# Get all hash fields
HGETALL user:1

# List operations
LPUSH mylist "item1"
LPUSH mylist "item2"
LRANGE mylist 0 -1

---

Backup & Recovery #

🔹 Automated Backups #

#!/bin/bash
# backup-databases.sh

BACKUP_DIR="/backup/$(date +%Y%m%d)"
mkdir -p $BACKUP_DIR

# PostgreSQL
pg_dump myapp | gzip > $BACKUP_DIR/postgres_myapp.sql.gz

# MySQL
mysqldump -u root -p myapp | gzip > $BACKUP_DIR/mysql_myapp.sql.gz

# MongoDB
mongodump --db myapp --out $BACKUP_DIR/mongodb/

# Upload to S3
aws s3 sync $BACKUP_DIR s3://my-backups/$(date +%Y%m%d)/

# Cleanup old backups (keep last 30 days)
find /backup -type d -mtime +30 -exec rm -rf {} \;

---

Performance Tuning #

🔹 PostgreSQL Tuning #

-- Show current settings
SHOW ALL;

-- Analyze query performance
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';

-- Create index
CREATE INDEX idx_username ON users(username);

-- Vacuum database
VACUUM ANALYZE;

🔹 MySQL Tuning #

-- Show variables
SHOW VARIABLES;

-- Analyze query
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- Create index
CREATE INDEX idx_username ON users(username);

-- Optimize table
OPTIMIZE TABLE users;

---

Monitoring #

🔹 PostgreSQL Monitoring #

-- Active connections
SELECT * FROM pg_stat_activity;

-- Database size
SELECT pg_size_pretty(pg_database_size('myapp'));

-- Table sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

🔹 MySQL Monitoring #

-- Show processlist
SHOW PROCESSLIST;

-- Database size
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;

---

🎓 Database Administration Master Class Complete