Missing Things

[kresnasatya]

1. Give option to install mysql (Mariadb) + Supervisor.
2. Backup database and upload it to cloudflare storage.
3. How to make connection into database using sql client like table plus?
4. How to make automation ssl certificate if the certificate is expired and update it into the nginx unit bundle?

NEXT STEPS:
- The certificate will need to be renewed before it expires. Certbot can automatically renew the certificate in the background, but you may need to take steps to enable that functionality. See https://certbot.org/renewal-setup for instructions.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
If you like Certbot, please consider supporting our work by:
 * Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
 * Donating to EFF:                    https://eff.org/donate-le
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[kresnasatya]

Optimize the server with constraint environment.

From this:

To this:

Redis

maxmemory 100mb
maxmemory-policy allkeys-lru

MySQL (MariaDB)

key_buffer_size = 64M
max_connections = 50

PHP

memory_limit = 256M

Supervisor

numprocs=2

[kresnasatya]

Backup DB + Upload it to R2 Cloudflare (AWS alternative)

1. Run as sudo (root).

sudo su

1. Create a backup script called [mariadb-backup.sh](http://mariadb-backup.sh/) and put it in /usr/local/bin

cd /usr/local/bin
nano mariadb-backup.sh

1. Put this script inside the mariadb-backup.sh. The backup file store in /var/backups/mariadb. The older backups will be deleted if more than 3 days (See: LOCAL_RETENTION_DAYS). You need to adjust value for DB_* and R2_*.

#!/bin/bash

# MySQL/MariaDB Daily Backup Script with Cloudflare R2 Upload
# This script creates daily backups of MySQL/MariaDB databases and uploads them to Cloudflare R2
# Designed to run via cron at 00:00 daily

# MySQL Configuration - MODIFY THESE ACCORDING TO YOUR SETUP
DB_USER="your_db_username"
DB_PASSWORD="your_db_password"
DB_HOST="localhost"
DB_PORT="3306"

# Local backup directory - ensure this directory exists and has proper permissions
BACKUP_DIR="/var/backups/mysql"

# Cloudflare R2 Configuration - MODIFY THESE ACCORDING TO YOUR R2 SETUP
R2_ENDPOINT="https://your-account-id.r2.cloudflarestorage.com"
R2_BUCKET="your-backup-bucket"
R2_ACCESS_KEY="your-r2-access-key"
R2_SECRET_KEY="your-r2-secret-key"
R2_BACKUP_PATH="mysql-backups"  # Path/folder in R2 bucket

# Retention settings
LOCAL_RETENTION_DAYS=3   # Keep local backups for 3 days
R2_RETENTION_DAYS=30     # Keep R2 backups for 30 days

# Date format for backup filename
DATE=$(date +"%Y%m%d_%H%M%S")
HOSTNAME=$(hostname)

# Log file for backup operations
LOG_FILE="/var/log/mysql_backup.log"

# Temporary directory for AWS CLI config
TEMP_DIR="/tmp/mysql_backup_$$"

# Function to log messages
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

# Function to check if required commands exist
check_dependencies() {
    local deps=("mysqldump" "mysql" "gzip" "aws")
    for cmd in "${deps[@]}"; do
        if ! command -v "$cmd" &> /dev/null; then
            log_message "ERROR: Required command '$cmd' not found"
            if [ "$cmd" = "aws" ]; then
                log_message "Please install AWS CLI: https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html"
            fi
            exit 1
        fi
    done
}

# Function to setup AWS CLI configuration for R2
setup_r2_config() {
    mkdir -p "$TEMP_DIR"
    
    # Create temporary AWS config for R2
    cat > "$TEMP_DIR/config" << EOF
[default]
region = auto
output = json
EOF

    cat > "$TEMP_DIR/credentials" << EOF
[default]
aws_access_key_id = $R2_ACCESS_KEY
aws_secret_access_key = $R2_SECRET_KEY
EOF

    export AWS_CONFIG_FILE="$TEMP_DIR/config"
    export AWS_SHARED_CREDENTIALS_FILE="$TEMP_DIR/credentials"
}

# Function to cleanup temporary AWS config
cleanup_r2_config() {
    rm -rf "$TEMP_DIR"
    unset AWS_CONFIG_FILE
    unset AWS_SHARED_CREDENTIALS_FILE
}

# Function to test R2 connection
test_r2_connection() {
    log_message "Testing R2 connection..."
    
    aws s3 ls "s3://$R2_BUCKET" --endpoint-url="$R2_ENDPOINT" &> /dev/null
    if [ $? -eq 0 ]; then
        log_message "R2 connection successful"
    else
        log_message "ERROR: Cannot connect to R2 bucket. Please check your R2 configuration."
        exit 1
    fi
}

# Function to create backup directory if it doesn't exist
create_backup_dir() {
    if [ ! -d "$BACKUP_DIR" ]; then
        mkdir -p "$BACKUP_DIR"
        if [ $? -eq 0 ]; then
            log_message "Created backup directory: $BACKUP_DIR"
        else
            log_message "ERROR: Failed to create backup directory: $BACKUP_DIR"
            exit 1
        fi
    fi
}

# Function to test database connection
test_db_connection() {
    mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1;" &> /dev/null
    if [ $? -ne 0 ]; then
        log_message "ERROR: Cannot connect to MySQL/MariaDB server"
        exit 1
    fi
    log_message "Database connection successful"
}

# Function to get list of databases (excluding system databases)
get_databases() {
    mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW DATABASES;" | grep -Ev '^(Database|information_schema|performance_schema|mysql|sys)$'
}

# Function to upload file to R2
upload_to_r2() {
    local local_file=$1
    local filename=$(basename "$local_file")
    local r2_key="$R2_BACKUP_PATH/$HOSTNAME/$filename"
    
    log_message "Uploading $filename to R2..."
    
    aws s3 cp "$local_file" "s3://$R2_BUCKET/$r2_key" --endpoint-url="$R2_ENDPOINT"
    
    if [ $? -eq 0 ]; then
        log_message "Successfully uploaded $filename to R2"
        return 0
    else
        log_message "ERROR: Failed to upload $filename to R2"
        return 1
    fi
}

# Function to backup a single database
backup_database() {
    local db_name=$1
    local backup_file="${BACKUP_DIR}/${HOSTNAME}_${db_name}_${DATE}.sql.gz"
    
    log_message "Starting backup of database: $db_name"
    
    # Create compressed backup
    mysqldump -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASSWORD" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --add-drop-database \
        --databases "$db_name" | gzip > "$backup_file"
    
    if [ ${PIPESTATUS[0]} -eq 0 ]; then
        local file_size=$(du -h "$backup_file" | cut -f1)
        log_message "Successfully created local backup of $db_name (Size: $file_size)"
        
        # Upload to R2
        if upload_to_r2 "$backup_file"; then
            log_message "Database $db_name backup completed successfully"
            return 0
        else
            log_message "WARNING: Local backup created but R2 upload failed for $db_name"
            return 1
        fi
    else
        log_message "ERROR: Failed to backup database: $db_name"
        # Remove partial backup file if it exists
        [ -f "$backup_file" ] && rm "$backup_file"
        return 1
    fi
}

# Function to backup all databases
backup_all_databases() {
    local databases=$(get_databases)
    local success_count=0
    local total_count=0
    
    if [ -z "$databases" ]; then
        log_message "WARNING: No user databases found to backup"
        return 0
    fi
    
    for db in $databases; do
        total_count=$((total_count + 1))
        if backup_database "$db"; then
            success_count=$((success_count + 1))
        fi
    done
    
    log_message "Backup summary: $success_count/$total_count databases backed up successfully"
}

# Function to clean up old local backups
cleanup_old_local_backups() {
    log_message "Cleaning up local backups older than $LOCAL_RETENTION_DAYS days"
    
    local deleted_count=0
    while IFS= read -r -d '' file; do
        rm "$file"
        deleted_count=$((deleted_count + 1))
        log_message "Deleted old local backup: $(basename "$file")"
    done < <(find "$BACKUP_DIR" -name "*.sql.gz" -type f -mtime +$LOCAL_RETENTION_DAYS -print0)
    
    if [ $deleted_count -eq 0 ]; then
        log_message "No old local backups found to delete"
    else
        log_message "Deleted $deleted_count old local backup files"
    fi
}

# Function to clean up old R2 backups
cleanup_old_r2_backups() {
    log_message "Cleaning up R2 backups older than $R2_RETENTION_DAYS days"
    
    local cutoff_date=$(date -d "$R2_RETENTION_DAYS days ago" '+%Y-%m-%d')
    local deleted_count=0
    
    # List objects in R2 and filter by date
    aws s3api list-objects-v2 --bucket "$R2_BUCKET" --prefix "$R2_BACKUP_PATH/$HOSTNAME/" --endpoint-url="$R2_ENDPOINT" --query "Contents[?LastModified<='$cutoff_date'].Key" --output text | while read -r key; do
        if [ "$key" != "None" ] && [ -n "$key" ]; then
            aws s3 rm "s3://$R2_BUCKET/$key" --endpoint-url="$R2_ENDPOINT"
            if [ $? -eq 0 ]; then
                deleted_count=$((deleted_count + 1))
                log_message "Deleted old R2 backup: $(basename "$key")"
            fi
        fi
    done
    
    log_message "R2 cleanup completed"
}

# Function to create backup summary
create_backup_summary() {
    local summary_file="${BACKUP_DIR}/backup_summary_${DATE}.txt"
    
    cat > "$summary_file" << EOF
MySQL/MariaDB Backup Summary
============================
Date: $(date)
Hostname: $HOSTNAME
Backup Location: Cloudflare R2 ($R2_BUCKET)

Databases backed up:
EOF

    local databases=$(get_databases)
    for db in $databases; do
        echo "- $db" >> "$summary_file"
    done
    
    # Upload summary to R2
    upload_to_r2 "$summary_file"
    rm "$summary_file"
}

# Main execution
main() {
    log_message "=== Starting MySQL/MariaDB backup process with R2 upload ==="
    
    # Check dependencies
    check_dependencies
    
    # Setup R2 configuration
    setup_r2_config
    
    # Create backup directory
    create_backup_dir
    
    # Test connections
    test_db_connection
    test_r2_connection
    
    # Perform backups
    backup_all_databases
    
    # Create backup summary
    create_backup_summary
    
    # Clean up old backups
    cleanup_old_local_backups
    cleanup_old_r2_backups
    
    # Cleanup R2 config
    cleanup_r2_config
    
    log_message "=== Backup process completed ==="
}

# Trap to ensure cleanup on script exit
trap cleanup_r2_config EXIT

# Execute main function
main "$@"

1. Save it and make it executable

chmod +x mariadb-backup.sh

1. Test executable script.

sudo /usr/local/bin/mariadb-backup.sh

1. Check if the backup was created.

ls -la /var/backups/mariadb/

1. Setup cron job and choose option number 1 for nano.

sudo crontab -e

1. We put daily backup at 2 AM

0 2 * * * /usr/local/bin/mariadb-backup.sh >/dev/null 2>&1

1. Verify cron job and check if cron service is running

sudo crontab -l
sudo systemctl status cron