Solving WordPress Backup Issues After MySQL 9.x Update: A RHEL/AlmaLinux Case Study

Troubleshooting WordPress Backup Issues After MySQL 9.x Updates in RHEL/AlmaLinux Environment

With the recent releases of MySQL 9.0 through 9.4, managing WordPress backups in Docker environments has become increasingly complex. I recently encountered significant challenges in our RHEL/AlmaLinux environment after upgrading to MySQL 9.1, and subsequently testing with newer versions. Let me share my comprehensive findings and solutions that work across all MySQL 9.x versions.

table of contents

Environment Overview

Here’s the production setup I’ve been working with:

  • OS: AlmaLinux 9 (RHEL compatible)
  • Architecture: ARM64/v8
  • Container Platform: Docker & Docker Compose
  • WordPress: Latest version (6.x)
  • MySQL: Tested with 9.1, 9.2, 9.3, and 9.4

Our project structure has evolved to handle these challenges:

wordpress-docker/
├── docker-compose.yml (Container configuration)
├── Dockerfile (WordPress container customization)
├── entrypoint.sh (Initialization script)
├── setup.sh (Environment setup script)
├── .env (Environment variables)
├── php.ini (PHP configuration)
├── backup/ (Backup storage directory)
└── scripts/ (Automation scripts)

The Initial Problem: Silent Backup Failures

During a routine system update that included MySQL 9.1, our backup system failed silently. This wasn’t immediately apparent – the containers appeared healthy, but our daily backups weren’t being created. This situation could have resulted in catastrophic data loss had we not discovered it during a routine audit.

Understanding MySQL 9.x Authentication Changes

MySQL 9.x introduced several significant changes that affect backup operations:

1. Authentication Plugin Evolution

Starting with MySQL 8.4 (April 2024) and continuing through MySQL 9.x:

  • The mysql_native_password plugin is now disabled by default
  • caching_sha2_password is the default authentication method
  • Legacy authentication methods require explicit enablement

2. Platform Support Changes

As of MySQL 9.4 (July 2025):

  • GCC 11 or later is required for compilation
  • ARM systems using RHEL7 are no longer supported
  • Enhanced ARM64 optimizations have been implemented

3. Security Enhancements

MySQL 9.x strengthened security measures:

  • More restrictive grant table handling
  • Enhanced password validation
  • Stricter authentication requirements for administrative operations

Unexpected Container Health Check Errors

While investigating our system, I encountered this perplexing error message:

template parsing error: template: :1:8: executing "" at <.State.Health.Status>: map has no entry for key "Health"

This Docker container health check issue appeared intermittently and sometimes resolved itself without intervention. Through extensive investigation, I identified several triggers and solutions.

Root Cause Analysis

Primary Issues Identified

  1. Docker Daemon State Management
    • Cache clearing operations
    • System restart status resets
    • Automatic Docker updates affecting container states
  2. MySQL 9.x Specific Problems
    • Authentication method incompatibilities
    • Grant table initialization failures
    • Container startup timing issues
  3. ARM64 Architecture Considerations
    • Platform-specific image availability
    • Performance variations between architectures
    • Emulation layer complications

Comprehensive Solution Implementation

Part 1: Enhanced Health Check Configuration

I’ve developed robust health check configurations that work reliably with MySQL 9.x:

WordPress Container Configuration:

healthcheck:
  test: ["CMD", "curl", "-f", "http://localhost:80", "--max-time", "10"]
  interval: 30s
  timeout: 15s
  retries: 5
  start_period: 60s

MySQL 9.x Container Configuration:

healthcheck:
  test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "--protocol=TCP"]
  interval: 30s
  timeout: 15s
  retries: 5
  start_period: 90s

Note the increased start_period for MySQL 9.x – this accounts for the additional initialization time required by newer versions.

Part 2: Advanced Setup Script with Retry Logic

Our enhanced setup script now includes sophisticated retry mechanisms:

#!/bin/bash

# Configuration
MAX_RETRIES=30
RETRY_DELAY=5

# Function to check container health
check_container_health() {
    local container_name=$1
    local retry_count=0
    
    while [ $retry_count -lt $MAX_RETRIES ]; do
        # Check if container exists and is running
        if docker inspect "$container_name" &>/dev/null; then
            status=$(docker inspect "$container_name" 2>/dev/null | jq -r '.[0].State.Status // empty')
            health=$(docker inspect "$container_name" 2>/dev/null | jq -r '.[0].State.Health.Status // "none"')
            
            if [ "$status" = "running" ]; then
                if [ "$health" = "healthy" ] || [ "$health" = "none" ]; then
                    # Additional application-level check
                    if [ "$container_name" = "wordpress" ]; then
                        if docker exec "$container_name" curl -s -f http://localhost:80 >/dev/null 2>&1; then
                            echo "✓ $container_name container is running and healthy"
                            return 0
                        fi
                    elif [ "$container_name" = "wordpress-db" ]; then
                        if docker exec "$container_name" mysqladmin ping -h localhost --protocol=TCP >/dev/null 2>&1; then
                            echo "✓ $container_name container is running and healthy"
                            return 0
                        fi
                    fi
                fi
            fi
        fi
        
        echo "⏳ Waiting for $container_name container (attempt $((retry_count + 1))/$MAX_RETRIES)..."
        sleep $RETRY_DELAY
        retry_count=$((retry_count + 1))
    done
    
    echo "❌ Failed to verify $container_name container health after $MAX_RETRIES attempts"
    return 1
}

# Main execution
echo "🚀 Starting WordPress environment setup..."

# Start containers
docker compose up -d

# Verify container health
check_container_health "wordpress-db" || exit 1
check_container_health "wordpress" || exit 1

echo "✅ WordPress environment is ready!"

Part 3: Operational Best Practices for MySQL 9.x

Comprehensive Logging Strategy

# Create timestamped log directory
LOG_DIR="logs/$(date +%Y%m%d)"
mkdir -p "$LOG_DIR"

# Capture detailed logs
docker compose logs --timestamps > "$LOG_DIR/docker_logs.txt"

# Monitor MySQL-specific events
docker exec wordpress-db tail -n 100 /var/log/mysql/error.log > "$LOG_DIR/mysql_error.log" 2>/dev/null

# System event monitoring
docker system events --since 30m --format '{{json .}}' | jq '.' > "$LOG_DIR/docker_events.json"

Pre-operation Safety Checks

# Verify MySQL version and authentication plugins
docker exec wordpress-db mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "
    SELECT VERSION();
    SELECT plugin_name, LOAD_OPTION FROM information_schema.plugins 
    WHERE plugin_name LIKE '%password%' OR plugin_name LIKE '%auth%';
"

The Core Issue: Database Backup Failure in MySQL 9.x

Despite improving system stability, the critical issue remained: database backups were completely failing with MySQL 9.x.

The Authentication Challenge

When attempting standard backup commands:

docker exec wordpress-db mysqldump -u root -p${MYSQL_ROOT_PASSWORD} dbin > backup_$(date +%Y%m%d).sql

I consistently encountered:

Enter password: 
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

This failure was particularly concerning because:

  • Daily automated backups ceased functioning
  • Disaster recovery became impossible
  • Data integrity was at risk
  • Manual interventions failed

Understanding the MySQL 9.x Security Model

MySQL 9.x implements several security enhancements that affect backup operations:

  1. Enhanced Authentication Requirements
    • Stricter password validation
    • Mandatory use of secure authentication plugins
    • Enhanced privilege checking for administrative operations
  2. Grant Table Changes
    • Modified privilege structures
    • New security contexts for different operations
    • Stricter localhost vs. network authentication
  3. Container-Specific Challenges
    • Volume permission complications
    • Network namespace isolation
    • Authentication plugin loading issues

The Working Solution: Bypass Authentication for Backups

After extensive testing across MySQL 9.1 through 9.4, I developed a reliable solution that works consistently:

Step 1: Prepare the Backup Environment

# Create temporary directories with proper permissions
mkdir -p temp_mysql_data temp_run_mysqld
chmod 755 temp_mysql_data temp_run_mysqld

# Copy database files (preserving permissions)
sudo cp -rp db_data/* temp_mysql_data/

# Verify file ownership
sudo chown -R 999:999 temp_mysql_data/

Step 2: Execute the Backup with Authentication Bypass

docker run --rm \
  --platform linux/arm64/v8 \
  -v $(pwd)/temp_mysql_data:/var/lib/mysql \
  -v $(pwd)/temp_run_mysqld:/var/run/mysqld \
  -v $(pwd)/backup:/backup \
  mysql:9.4 \
  bash -c '
    # Initialize MySQL with bypassed authentication
    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
        break
      fi
      sleep 1
    done
    
    # Perform the backup
    echo "Starting backup..."
    mysqldump \
      --no-tablespaces \
      --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"
    else
      echo "Backup failed"
      exit 1
    fi
    
    # Graceful shutdown
    mysqladmin shutdown
    sleep 5
 

Step 3: Clean Up and Verify

# Remove temporary directories
sudo rm -rf temp_mysql_data temp_run_mysqld

# Verify backup integrity
echo "Backup file created:"
ls -lh backup/full_backup_*.sql | tail -1

# Quick content verification
head -n 50 backup/full_backup_*.sql | grep -E "^-- MySQL dump|^-- Server version"

Why This Solution Works Across MySQL 9.x Versions

This approach is effective for several key reasons:

1. Authentication Bypass Mechanism

  • --skip-grant-tables: Safely bypasses MySQL 9.x’s strict authentication
  • Works consistently across versions 9.1 through 9.4
  • Maintains data integrity during the operation

2. Enhanced Security Measures

  • --skip-networking: Prevents network access during vulnerable state
  • Temporary container isolation
  • No persistent security changes

3. Environmental Isolation

  • Separate container instance for backup operations
  • No impact on running production containers
  • Clean state for each backup operation

Understanding the Enhanced Command Structure

Let’s examine the improvements for MySQL 9.x compatibility:

Container Configuration for MySQL 9.x

docker run --rm \
  --platform linux/arm64/v8 \           # Explicit ARM64 platform specification
  --memory="2g" \                       # Memory limit for large databases
  --cpus="1.5" \                        # CPU limit to prevent resource exhaustion
  -v $(pwd)/temp_mysql_data:/var/lib/mysql:ro \  # Read-only mount for safety
  -v $(pwd)/temp_run_mysqld:/var/run/mysqld \
  -v $(pwd)/backup:/backup \
  mysql:9.4

Enhanced Backup Options for MySQL 9.x

mysqldump \
  --no-tablespaces \              # Required for MySQL 9.x compatibility
  --skip-add-drop-table \          # Preserve existing structures
  --single-transaction \           # Consistent backup for InnoDB
  --quick \                        # Stream results (memory efficient)
  --lock-tables=false \            # Avoid locking issues
  --column-statistics=0 \          # MySQL 9.x compatibility
  --skip-definer \                 # Avoid permission issues on restore
  --all-databases                  # Complete backup

Production-Ready Automated Backup Solution

For long-term operational sustainability, I’ve developed a comprehensive automated solution:

Enhanced Docker Compose Configuration

version: '3.9'

services:
  db:
    image: mysql:9.4
    platform: linux/arm64/v8
    container_name: wordpress-db
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
      # MySQL 9.x specific configurations
      MYSQL_ROOT_HOST: '%'
      MYSQL_AUTHENTICATION_POLICY: 'caching_sha2_password'
    volumes:
      - db_data:/var/lib/mysql
      - ./mysql-init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "--protocol=TCP"]
      interval: 30s
      timeout: 15s
      retries: 5
      start_period: 90s
    networks:
      - wordpress-network

  wordpress:
    depends_on:
      db:
        condition: service_healthy
    image: wordpress:latest
    platform: linux/arm64/v8
    container_name: wordpress
    restart: unless-stopped
    ports:
      - "8080:80"
    environment:
      WORDPRESS_DB_HOST: db:3306
      WORDPRESS_DB_USER: ${MYSQL_USER}
      WORDPRESS_DB_PASSWORD: ${MYSQL_PASSWORD}
      WORDPRESS_DB_NAME: ${MYSQL_DATABASE}
    volumes:
      - wordpress_data:/var/www/html
      - ./uploads.ini:/usr/local/etc/php/conf.d/uploads.ini
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:80", "--max-time", "10"]
      interval: 30s
      timeout: 15s
      retries: 5
      start_period: 60s
    networks:
      - wordpress-network

  backup:
    image: mysql:9.4
    platform: linux/arm64/v8
    container_name: wordpress-backup
    depends_on:
      db:
        condition: service_healthy
    environment:
      MYSQL_HOST: db
      MYSQL_USER: root
      MYSQL_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      BACKUP_SCHEDULE: "0 2 * * *"  # Daily at 2 AM
      BACKUP_RETENTION_DAYS: 30
    volumes:
      - ./backup:/backup
      - ./backup-scripts:/scripts
    command: >
      /bin/bash -c "
      # Install cron
      apt-get update && apt-get install -y cron
      
      # Create backup script
      cat > /backup.sh << 'EOF'
      #!/bin/bash
      echo \"Starting backup at \$(date)\"
      
      # Wait for database to be available
      until mysqladmin ping -h \"\$MYSQL_HOST\" --silent; do
        sleep 1
      done
      
      # Perform backup with retry logic
      MAX_RETRIES=3
      RETRY_COUNT=0
      
      while [ \$RETRY_COUNT -lt \$MAX_RETRIES ]; do
        BACKUP_FILE=\"/backup/\${MYSQL_DATABASE}_\$(date +%Y%m%d_%H%M%S).sql\"
        
        if MYSQL_PWD=\$MYSQL_PASSWORD mysqldump \
          -h \$MYSQL_HOST \
          -u \$MYSQL_USER \
          --no-tablespaces \
          --single-transaction \
          --quick \
          --lock-tables=false \
          --column-statistics=0 \
          \$MYSQL_DATABASE > \"\$BACKUP_FILE\"; then
          
          # Compress backup
          gzip \"\$BACKUP_FILE\"
          echo \"Backup completed: \${BACKUP_FILE}.gz\"
          
          # Clean old backups
          find /backup -name \"*.sql.gz\" -mtime +\${BACKUP_RETENTION_DAYS} -delete
          echo \"Old backups cleaned\"
          
          exit 0
        else
          echo \"Backup attempt \$((RETRY_COUNT + 1)) failed\"
          RETRY_COUNT=\$((RETRY_COUNT + 1))
          sleep 10
        fi
      done
      
      echo \"Backup failed after \$MAX_RETRIES attempts\"
      exit 1
      EOF
      
      chmod +x /backup.sh
      
      # Setup cron job
      echo \"\$BACKUP_SCHEDULE /backup.sh >> /var/log/backup.log 2>&1\" | crontab -
      
      # Start cron and tail logs
      cron && tail -f /var/log/backup.log /dev/null
      "
    networks:
      - wordpress-network

volumes:
  db_data:
  wordpress_data:

networks:
  wordpress-network:
    driver: bridge

Backup Monitoring Script

#!/bin/bash

# backup-monitor.sh - Monitor backup health and send alerts

BACKUP_DIR="./backup"
MAX_AGE_HOURS=26  # Alert if backup is older than 26 hours
MIN_SIZE_MB=1     # Alert if backup is smaller than 1MB

# Function to send alert (customize based on your notification system)
send_alert() {
    local message=$1
    echo "ALERT: $message"
    # Add your notification method here (email, Slack, etc.)
}

# Check latest backup
latest_backup=$(ls -t $BACKUP_DIR/*.sql.gz 2>/dev/null | head -1)

if [ -z "$latest_backup" ]; then
    send_alert "No backup files found in $BACKUP_DIR"
    exit 1
fi

# Check backup age
backup_age_minutes=$(( ($(date +%s) - $(stat -f %m "$latest_backup" 2>/dev/null || stat -c %Y "$latest_backup")) / 60 ))
backup_age_hours=$(( backup_age_minutes / 60 ))

if [ $backup_age_hours -gt $MAX_AGE_HOURS ]; then
    send_alert "Latest backup is $backup_age_hours hours old (threshold: $MAX_AGE_HOURS hours)"
fi

# Check backup size
backup_size_bytes=$(stat -f %z "$latest_backup" 2>/dev/null || stat -c %s "$latest_backup")
backup_size_mb=$(( backup_size_bytes / 1024 / 1024 ))

if [ $backup_size_mb -lt $MIN_SIZE_MB ]; then
    send_alert "Latest backup is only ${backup_size_mb}MB (minimum: ${MIN_SIZE_MB}MB)"
fi

echo "✓ Backup monitoring complete: $(basename $latest_backup)"
echo "  Age: $backup_age_hours hours"
echo "  Size: ${backup_size_mb}MB"

MySQL 9.4 Specific Considerations

With the release of MySQL 9.4 in July 2025, several new considerations apply:

New Features Affecting Backups

  1. Vector Data Type Support
    • New VECTOR column type for AI/ML applications
    • Requires special handling in backups
    • Not supported in older MySQL versions
  2. Enhanced Security Features
    • Improved authentication plugin architecture
    • New password validation requirements
    • Enhanced encryption options
  3. Performance Improvements
    • Optimized backup operations for large databases
    • Better memory management
    • Improved ARM64 performance

Compatibility Considerations

When working with MySQL 9.4, ensure:

# Check MySQL version and features
docker exec wordpress-db mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "
    SELECT VERSION();
    SHOW VARIABLES LIKE 'authentication_policy';
    SHOW VARIABLES LIKE 'mysql_native_password';
"

Recovery Procedures for MySQL 9.x

Standard Recovery Process

# 1. Stop WordPress container to prevent conflicts
docker compose stop wordpress

# 2. Decompress backup
gunzip backup/wordpress_backup_20250825_020000.sql.gz

# 3. Restore database
docker exec -i wordpress-db mysql -u root -p${MYSQL_ROOT_PASSWORD} < backup/wordpress_backup_20250825_020000.sql

# 4. Verify restoration
docker exec wordpress-db mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "
    USE ${MYSQL_DATABASE};
    SHOW TABLES;
    SELECT COUNT(*) FROM wp_posts;
"

# 5. Restart WordPress
docker compose start wordpress

Emergency Recovery with Authentication Issues

If standard recovery fails due to authentication issues:

#!/bin/bash

# emergency-restore.sh - Emergency database restoration for MySQL 9.x

BACKUP_FILE=$1

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 <backup_file>"
    exit 1
fi

echo "Starting emergency restoration..."

# Stop all containers
docker compose down

# Backup current data
echo "Backing up current data..."
sudo cp -rp db_data db_data_backup_$(date +%Y%m%d_%H%M%S)

# Start MySQL with skip-grant-tables
docker run -d \
  --name mysql-recovery \
  --platform linux/arm64/v8 \
  -v $(pwd)/db_data:/var/lib/mysql \
  -v $(pwd)/$BACKUP_FILE:/backup.sql \
  mysql:9.4 \
  --skip-grant-tables --skip-networking

# Wait for MySQL to start
sleep 10

# Restore database
docker exec mysql-recovery bash -c "
    mysql < /backup.sql
    
    # Reset root password for MySQL 9.x
    mysql -e \"
        FLUSH PRIVILEGES;
        ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '${MYSQL_ROOT_PASSWORD}';
        ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY '${MYSQL_ROOT_PASSWORD}';
        FLUSH PRIVILEGES;
    \"
"

# Stop recovery container
docker stop mysql-recovery
docker rm mysql-recovery

# Start normal services
docker compose up -d

echo "Emergency restoration completed!"

Platform-Specific Optimizations

ARM64 Architecture Considerations

For ARM64 systems (like Apple Silicon or AWS Graviton), consider these optimizations:

# Optimized for ARM64
services:
  db:
    image: mysql:9.4
    platform: linux/arm64/v8
    environment:
      # ARM64 optimized settings
      MYSQL_INNODB_BUFFER_POOL_SIZE: '1G'
      MYSQL_INNODB_LOG_FILE_SIZE: '256M'
      MYSQL_MAX_CONNECTIONS: '200'
      MYSQL_THREAD_CACHE_SIZE: '9'
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2G
        reservations:
          cpus: '1'
          memory: 1G

x86_64 Fallback for Compatibility

If you need to run on systems without ARM64 support:

# Force x86_64 emulation (performance impact)
docker run --platform linux/amd64 mysql:9.4

Comprehensive Troubleshooting Guide

Common Issues and Solutions

Issue 1: Authentication Failures

ERROR 1045 (28000): Access denied for user 'root'@'localhost'

Solution:

  • Verify authentication plugin compatibility
  • Check password encoding
  • Ensure proper grant tables initialization

Issue 2: Container Health Check Failures

template parsing error: <.State.Health.Status>: map has no entry for key "Health"

Solution:

  • Update Docker to latest version
  • Clear Docker cache: docker system prune -a
  • Rebuild containers with proper health checks

Issue 3: Backup Performance Issues

Solution:

  • Use --single-transaction for InnoDB tables
  • Implement --quick option for large databases
  • Consider parallel backup tools for very large databases

Monitoring and Alerting Best Practices

Prometheus Metrics Export

# Add to docker-compose.yml
mysqld-exporter:
  image: prom/mysqld-exporter
  environment:
    DATA_SOURCE_NAME: "root:${MYSQL_ROOT_PASSWORD}@(db:3306)/"
  ports:
    - "9104:9104"
  depends_on:
    - db

Grafana Dashboard Configuration

Create comprehensive monitoring with:

  • Backup success/failure rates
  • Database size trends
  • Query performance metrics
  • Container health status

GitHub Repository and Resources

I’ve created a comprehensive GitHub repository with all scripts and configurations:

Repository:

The repository includes:

  • Complete Docker Compose configurations for MySQL 9.x
  • Automated backup scripts
  • Recovery procedures
  • Health monitoring tools
  • Platform-specific optimizations

Additional Resources

Documentation:

Video Tutorials:

Conclusion

Through extensive testing with MySQL 9.1 through 9.4, I’ve learned that successful backup strategies require:

1. Understanding Version-Specific Changes

  • Authentication plugin evolution
  • Security enhancements
  • Platform compatibility requirements

2. Implementing Robust Solutions

  • Multiple backup strategies
  • Automated monitoring
  • Emergency recovery procedures

3. Continuous Monitoring

  • Health check implementations
  • Performance metrics
  • Alert systems

4. Documentation and Testing

  • Regular recovery drills
  • Updated documentation
  • Version compatibility testing

The solutions presented here have been tested across multiple MySQL 9.x versions and provide a reliable foundation for WordPress backup operations in modern containerized environments. As MySQL continues to evolve, these patterns and practices will help maintain data integrity and system reliability.

Remember: Always test your backup and recovery procedures in a non-production environment before implementing them in production systems.

Future Considerations

As we move forward with MySQL development:

  • MySQL 9.5 and beyond: Continue monitoring release notes for changes
  • Container orchestration: Consider Kubernetes for larger deployments
  • Cloud-native solutions: Evaluate managed database services
  • AI/ML Integration: Prepare for VECTOR data type adoption

Stay informed about MySQL developments and adjust your backup strategies accordingly to ensure continued data protection and system reliability.

If you like this article, please
Follow !

Please share if you like it!
table of contents