-- MySQL 8.x and earlier
mysql_native_password (deprecated but available)
caching_sha2_password (default)

-- MySQL 9.0+
mysql_native_password (COMPLETELY REMOVED)
caching_sha2_password (only option)

# Standard approach that worked in MySQL 8.x
docker exec wordpress-db mysqldump -u root -pTestPass wordpress > backup.sql

# Result in MySQL 9.x:
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' 
(using password: YES) when trying to connect

#!/bin/bash
# Universal MySQL 9.x Backup Solution

# Step 1: Prepare temporary environment
mkdir -p temp_mysql_data temp_run_mysqld
sudo cp -rp db_data/* temp_mysql_data/

# Step 2: Detect platform
PLATFORM="linux/amd64"
if [[ $(uname -m) == "aarch64" ]] || [[ $(uname -m) == "arm64" ]]; then
    PLATFORM="linux/arm64/v8"
fi

# Step 3: Execute backup with authentication bypass
docker run --rm \
  --platform ${PLATFORM} \
  -v $(pwd)/temp_mysql_data:/var/lib/mysql:ro \
  -v $(pwd)/temp_run_mysqld:/var/run/mysqld \
  -v $(pwd)/backup:/backup \
  mysql:9.4 \
  bash -c '
    # Start MySQL with authentication disabled
    docker-entrypoint.sh mysqld \
      --skip-grant-tables \
      --skip-networking &
    
    # Wait for MySQL to be ready
    echo "Waiting for MySQL to initialize..."
    for i in {1..60}; do
      if mysqladmin ping --silent 2>/dev/null; then
        echo "MySQL is ready"
        break
      fi
      sleep 1
    done
    
    # Perform backup with MySQL 9.x compatible options
    mysqldump \
      --no-tablespaces \
      --column-statistics=0 \
      --skip-add-drop-table \
      --single-transaction \
      --quick \
      --lock-tables=false \
      --all-databases > /backup/full_backup_$(date +%Y%m%d_%H%M%S).sql
    
    # Verify backup
    if [ $? -eq 0 ]; then
      echo "Backup completed successfully"
      ls -lh /backup/full_backup_*.sql
    else
      echo "Backup failed"
      exit 1
    fi
    
    # Clean shutdown
    mysqladmin shutdown 2>/dev/null || true
    sleep 5
  '

# Step 4: Cleanup
sudo rm -rf temp_mysql_data temp_run_mysqld

# docker-compose.yml - MySQL 9.4 Compatible
version: '3.9'

services:
  db:
    image: mysql:9.4
    container_name: wordpress-db
    platform: ${DOCKER_PLATFORM:-linux/amd64}
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: wordpress
      MYSQL_USER: wpuser
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
      # MySQL 9.x specific settings
      MYSQL_ROOT_HOST: '%'
      MYSQL_AUTHENTICATION_POLICY: 'caching_sha2_password'
    volumes:
      - db_data:/var/lib/mysql
      - ./mysql-config:/etc/mysql/conf.d:ro
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "--protocol=TCP"]
      interval: 30s
      timeout: 15s
      retries: 5
      start_period: 120s  # MySQL 9.x needs more time
    networks:
      - wordpress-net
    ports:
      - "3306:3306"

  wordpress:
    depends_on:
      db:
        condition: service_healthy
    image: wordpress:6-apache
    container_name: wordpress
    platform: ${DOCKER_PLATFORM:-linux/amd64}
    restart: unless-stopped
    ports:
      - "8080:80"
    environment:
      WORDPRESS_DB_HOST: db:3306
      WORDPRESS_DB_USER: wpuser
      WORDPRESS_DB_PASSWORD: ${MYSQL_PASSWORD}
      WORDPRESS_DB_NAME: wordpress
      WORDPRESS_CONFIG_EXTRA: |
        define('WP_HOME', 'http://localhost:8080');
        define('WP_SITEURL', 'http://localhost:8080');
    volumes:
      - wordpress_data:/var/www/html
      - ./uploads.ini:/usr/local/etc/php/conf.d/uploads.ini:ro
    networks:
      - wordpress-net

  backup:
    image: mysql:9.4
    container_name: wordpress-backup
    platform: ${DOCKER_PLATFORM:-linux/amd64}
    depends_on:
      db:
        condition: service_healthy
    environment:
      MYSQL_HOST: db
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    volumes:
      - ./backup:/backup
      - ./scripts:/scripts:ro
    command: ["/scripts/backup.sh"]
    networks:
      - wordpress-net

volumes:
  db_data:
  wordpress_data:

networks:
  wordpress-net:
    driver: bridge

#!/bin/bash
# export-database.sh - Works with MySQL 9.x

SOURCE_CONTAINER="wordpress-db"
BACKUP_FILE="wordpress_export_$(date +%Y%m%d_%H%M%S).sql"

# Method 1: Try standard export (might work on Ubuntu)
echo "Attempting standard export..."
if docker exec ${SOURCE_CONTAINER} mysqldump \
  -u root -p${MYSQL_ROOT_PASSWORD} \
  --no-tablespaces \
  --column-statistics=0 \
  wordpress > ${BACKUP_FILE} 2>/dev/null; then
  echo "✓ Standard export successful"
else
  echo "✗ Standard export failed, using bypass method..."
  
  # Method 2: Use bypass method (always works)
  ./mysql9x-backup.sh
fi

# Verify export
if [ -f ${BACKUP_FILE} ]; then
  echo "Export completed: ${BACKUP_FILE}"
  echo "Size: $(ls -lh ${BACKUP_FILE} | awk '{print $5}')"
  echo "Tables: $(grep -c 'CREATE TABLE' ${BACKUP_FILE})"
fi

#!/bin/bash
# import-database.sh - MySQL 9.x Compatible Import

TARGET_CONTAINER="wordpress-db-new"
IMPORT_FILE=$1

if [ -z "$IMPORT_FILE" ]; then
  echo "Usage: $0 <import_file.sql>"
  exit 1
fi

echo "Starting import process..."

# Step 1: Create database if not exists
docker exec ${TARGET_CONTAINER} mysql -u root -p${MYSQL_ROOT_PASSWORD} \
  -e "CREATE DATABASE IF NOT EXISTS wordpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Step 2: Import data
docker exec -i ${TARGET_CONTAINER} mysql -u root -p${MYSQL_ROOT_PASSWORD} wordpress < ${IMPORT_FILE}

# Step 3: Verify import
echo "Verifying import..."
docker exec ${TARGET_CONTAINER} mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "
  USE wordpress;
  SELECT 'Tables' as Type, COUNT(*) as Count FROM information_schema.tables 
  WHERE table_schema = 'wordpress'
  UNION ALL
  SELECT 'Posts' as Type, COUNT(*) FROM wp_posts
  UNION ALL  
  SELECT 'Users' as Type, COUNT(*) FROM wp_users;
"

echo "✓ Import completed successfully"

# Create proper .htaccess file
cat > wordpress_data/.htaccess << 'EOF'
# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>
# END WordPress
EOF

# Set correct permissions
# First, identify the web server user ID
WEB_USER_ID=$(docker exec wordpress id -u www-data)
sudo chown ${WEB_USER_ID}:${WEB_USER_ID} wordpress_data/.htaccess
chmod 644 wordpress_data/.htaccess

# Enable mod_rewrite in Apache
docker exec wordpress a2enmod rewrite
docker restart wordpress

-- Fix database URLs if site moved
UPDATE wp_options SET option_value = 'http://localhost:8080' 
WHERE option_name IN ('siteurl', 'home');

-- Verify WordPress database settings
SELECT option_name, option_value 
FROM wp_options 
WHERE option_name IN ('siteurl', 'home', 'template', 'stylesheet');

# Export with proper encoding
docker exec wordpress-db mysqldump \
  --default-character-set=utf8mb4 \
  --no-tablespaces \
  -u root -p${MYSQL_ROOT_PASSWORD} \
  wordpress > backup_utf8mb4.sql

# Import with proper encoding
docker exec -i wordpress-db-new mysql \
  --default-character-set=utf8mb4 \
  -u root -p${MYSQL_ROOT_PASSWORD} \
  wordpress < backup_utf8mb4.sql

# Diagnostic commands
docker exec wordpress-db mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "
  SELECT user, host, plugin FROM mysql.user WHERE user='root';
  SHOW VARIABLES LIKE 'authentication_policy';
  SHOW VARIABLES LIKE '%auth%';
"

# If authentication fails consistently, verify the container logs
docker logs wordpress-db 2>&1 | grep -i auth

# Enhanced health check for MySQL 9.x
healthcheck:
  test: |
    CMD mysql -h localhost -u root -p$$MYSQL_ROOT_PASSWORD -e "SELECT 1" || exit 1
  interval: 30s
  timeout: 20s
  retries: 10
  start_period: 180s  # MySQL 9.x needs significant startup time

# Fix volume permissions for different platforms

# Ubuntu/Debian
sudo chown -R 999:999 ./db_data

# AlmaLinux/RHEL with SELinux
sudo chown -R 999:999 ./db_data
sudo chcon -Rt svirt_sandbox_file_t ./db_data

# macOS/Windows (Docker Desktop)
# Permissions are handled automatically by Docker Desktop

#!/bin/bash
# migrate-wordpress.sh - Full WordPress Migration Script for MySQL 9.x

set -e

# Configuration
SOURCE_HOST="${1:-source-db}"
TARGET_HOST="${2:-target-db}"
BACKUP_DIR="./backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

log_info() { echo -e "${GREEN}[INFO]${NC} $1"; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
log_error() { echo -e "${RED}[ERROR]${NC} $1"; }

# Create backup directory
mkdir -p ${BACKUP_DIR}

# Function to detect MySQL version
detect_mysql_version() {
    local container=$1
    docker exec ${container} mysql --version | grep -oP '\d+\.\d+\.\d+' | head -1
}

# Function to export database
export_database() {
    local container=$1
    local output_file=$2
    
    log_info "Detecting MySQL version in ${container}..."
    local version=$(detect_mysql_version ${container})
    log_info "MySQL version: ${version}"
    
    if [[ ${version} == 9.* ]]; then
        log_warn "MySQL 9.x detected, using bypass method..."
        
        # Create temporary directory
        local temp_dir="temp_mysql_${TIMESTAMP}"
        mkdir -p ${temp_dir}
        
        # Copy database files
        docker cp ${container}:/var/lib/mysql ${temp_dir}/
        
        # Run bypass export
        docker run --rm \
            -v $(pwd)/${temp_dir}/mysql:/var/lib/mysql:ro \
            -v $(pwd)/${BACKUP_DIR}:/backup \
            mysql:${version} \
            bash -c "
                mysqld --skip-grant-tables --skip-networking &
                sleep 10
                mysqldump --all-databases --no-tablespaces --column-statistics=0 > /backup/${output_file}
                mysqladmin shutdown
            "
        
        # Cleanup
        rm -rf ${temp_dir}
    else
        log_info "Using standard export method..."
        docker exec ${container} mysqldump \
            --all-databases \
            --no-tablespaces \
            > ${BACKUP_DIR}/${output_file}
    fi
    
    log_info "Export completed: ${BACKUP_DIR}/${output_file}"
}

# Function to import database
import_database() {
    local container=$1
    local input_file=$2
    
    log_info "Importing database to ${container}..."
    
    docker exec -i ${container} mysql < ${BACKUP_DIR}/${input_file}
    
    if [ $? -eq 0 ]; then
        log_info "Import successful"
    else
        log_error "Import failed"
        exit 1
    fi
}

# Function to update WordPress URLs
update_wordpress_urls() {
    local container=$1
    local old_url=$2
    local new_url=$3
    
    log_info "Updating WordPress URLs from ${old_url} to ${new_url}..."
    
    docker exec ${container} mysql -e "
        USE wordpress;
        UPDATE wp_options SET option_value = '${new_url}' 
        WHERE option_name IN ('siteurl', 'home');
        
        UPDATE wp_posts SET guid = REPLACE(guid, '${old_url}', '${new_url}');
        UPDATE wp_posts SET post_content = REPLACE(post_content, '${old_url}', '${new_url}');
        UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '${old_url}', '${new_url}');
    "
}

# Main migration process
main() {
    log_info "Starting WordPress migration..."
    
    # Export from source
    export_database ${SOURCE_HOST} "wordpress_export_${TIMESTAMP}.sql"
    
    # Import to target
    import_database ${TARGET_HOST} "wordpress_export_${TIMESTAMP}.sql"
    
    # Update URLs if needed
    if [ ! -z "$3" ] && [ ! -z "$4" ]; then
        update_wordpress_urls ${TARGET_HOST} $3 $4
    fi
    
    log_info "Migration completed successfully!"
}

# Run main function
main "$@"

# docker-compose.test.yml - Test Environment
version: '3.9'

services:
  # Source database (MySQL 8.x)
  source-db:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD: sourcepass
      MYSQL_DATABASE: wordpress
    volumes:
      - source_data:/var/lib/mysql

  # Target database (MySQL 9.4)
  target-db:
    image: mysql:9.4
    environment:
      MYSQL_ROOT_PASSWORD: targetpass
      MYSQL_DATABASE: wordpress
    volumes:
      - target_data:/var/lib/mysql

volumes:
  source_data:
  target_data:

# Optimized MySQL 9.x configuration
services:
  db:
    image: mysql:9.4
    command: |
      --innodb_buffer_pool_size=1G
      --innodb_log_file_size=256M
      --innodb_flush_method=O_DIRECT
      --innodb_file_per_table=1
      --max_connections=200
      --slow_query_log=1
      --long_query_time=2