Troubleshooting WordPress Backup Issues After MySQL 9.1 Update in RHEL/AlmaLinux Environment

While managing WordPress backups is a critical task for any system administrator, I recently encountered a significant challenge in our RHEL/AlmaLinux environment. Our previously reliable backup system suddenly stopped working after upgrading to MySQL 9.1. Let me share how I diagnosed and solved this issue.

Environment Overview

Here’s the setup I was working with:

• OS: AlmaLinux (RHEL compatible)
• Architecture: ARM64/v8
• Docker & Docker Compose
• WordPress (Latest version)
• MySQL 9.1

Our project structure looked like this:

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)

The Initial Problem

During a routine system update that included MySQL 9.1, our backup system quietly failed. It wasn’t until I performed a routine check that I discovered our daily backups weren’t being created anymore – a situation that could have had serious consequences for our data security.

Unexpected Errors and Initial Investigation

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 healthcheck issue was particularly interesting because it would appear intermittently and sometimes resolve itself without intervention. Let me explain what I discovered during my investigation.

Root Causes and Analysis

In Docker environments, these types of intermittent issues aren’t uncommon. Through my investigation, I identified several potential triggers:

• Docker daemon cache clearing
• System restart status resets
• Automatic Docker updates
• Container startup timing and dependency issues

Implementing a Robust Solution

To address these issues, I enhanced our docker-compose.yml file with more robust healthcheck configurations:

WordPress Container Configuration:

healthcheck:
  test: ["CMD", "curl", "-f", "http://localhost:80"]
  interval: 30s
  timeout: 10s
  retries: 3
  start_period: 40s

Database Container Configuration:

healthcheck:
  test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
  interval: 30s
  timeout: 10s
  retries: 3
  start_period: 40s

Enhanced Setup Script

To make our system more resilient, I improved the setup.sh script to better handle container state verification:

while true; do
    status=$(docker inspect wordpress 2>/dev/null | jq -r '.[0].State.Status')
    if [ "$status" = "running" ]; then
        if docker exec wordpress curl -s -f http://localhost:80 >/dev/null 2>&1; then
            echo "WordPress container is running and healthy"
            break
        fi
    fi
    echo "Waiting for WordPress container to initialize..."
    sleep 5
done

Operational Best Practices

Through this experience, I’ve developed several recommended practices that have proven invaluable:

Regular Log Monitoring

docker compose logs > docker_logs_$(date +%Y%m%d).txt

System Status Checks

# Monitor recent events
docker system events --since 30m

Pre-operation Backups

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

These improvements have significantly enhanced our system’s stability and reliability.

The Core Issue: Database Backup Failure

While the above changes improved our overall system stability, we still faced a more critical issue: our database backups were completely failing.

The Core Issue: Database Backup Failure

While addressing our system stability improved overall operations, we encountered a more serious problem that threatened our data security. Our database backups had completely stopped working.

When attempting a standard backup command:

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

I encountered this critical error:

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

This failure was particularly concerning for several reasons:

• Daily backups were no longer functioning
• Disaster recovery became impossible
• System security was compromised

Understanding the Root Cause

This issue emerged specifically after our upgrade to MySQL 9.1. A previously reliable backup process had suddenly become non-functional, highlighting the significant changes in MySQL 9.1’s security model.

The Path to Resolution

1. Initial Attempts

My first approach was to try various traditional backup methods, all of which failed. When attempting to access the database directly from inside the container:

docker exec -it wordpress-db bash
mysqldump -u root -p dbin > dbin_backup.sql

Or trying from outside:

docker exec wordpress-db mysqldump -u root -p dbin > dbin_backup.sql

Both attempts resulted in the same error:

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

Upon further investigation, I discovered that I couldn’t even log into MySQL – a clear indication that the issue went deeper than just backup permissions.

2. The Working Solution

After considerable testing, I developed a solution that successfully circumvented these authentication issues. Here’s the step-by-step approach:

First, prepare a temporary directory:

# Create temporary directories
mkdir -p temp_mysql_data temp_run_mysqld

# Copy database files
sudo cp -r db_data/* temp_mysql_data/

Then, execute the backup using a temporary container:

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.1 \
  bash -c 'docker-entrypoint.sh mysqld --skip-grant-tables --skip-networking & sleep 30 && mysqldump --no-tablespaces --skip-add-drop-table --all-databases > /backup/full_backup_$(date +%Y%m%d_%H%M%S).sql && sleep 5'

Finally, clean up:

# Remove temporary directories
sudo rm -rf temp_mysql_data temp_run_mysqld

Why This Solution Works

This approach proves effective for several key reasons:

1. Authentication Bypass
   By using --skip-grant-tables, we safely bypass MySQL 9.1’s strict authentication requirements while maintaining data integrity.
2. Security Considerations
   The --skip-networking flag ensures that while authentication is bypassed, the database remains secure by preventing network access.
3. Environmental Isolation
   By using a temporary container with mounted volumes, we:

• Protect the production environment
• Maintain data consistency
• Avoid potential conflicts with running services

Understanding the Command Structure

Let’s break down the key components of our solution:

1. Container Configuration

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.1

Each flag serves a specific purpose:

• --rm: Automatically removes the container after completion
• --platform: Specifies ARM64 architecture compatibility
• -v options: Mount necessary directories for data access and backup storage

2. Command Execution Details

bash -c 'docker-entrypoint.sh mysqld --skip-grant-tables --skip-networking & sleep 30 && \
mysqldump --no-tablespaces --skip-add-drop-table --all-databases > \
/backup/full_backup_$(date +%Y%m%d_%H%M%S).sql && sleep 5'

Let’s examine each component:

1. Initialization

• docker-entrypoint.sh mysqld: Properly initializes the MySQL server
• Standard Docker image startup procedure

1. Security Options

• --skip-grant-tables: Bypasses authentication requirements
• --skip-networking: Enhances security by disabling network access

1. Timing Controls

• sleep 30: Ensures MySQL is fully initialized
• Can be adjusted based on system performance

1. Backup Configuration

• --no-tablespaces: Excludes tablespace information
• --skip-add-drop-table: Preserves existing table structures
• --all-databases: Comprehensive backup of all databases

1. Output Handling

• Timestamps in the filename ensure unique backups
• Format: YYYYMMDD_HHMMSS.sql

Implementation Considerations

When using this solution, keep in mind:

1. Directory Management

• Temporary directories must be properly created
• Sufficient disk space is essential
• Cleanup after backup is important

1. Permission Requirements

• sudo access may be needed for file operations
• Proper volume mounting permissions are crucial

1. Timing Adjustments

• Adjust sleep durations based on:
  • System performance
  • Database size
  • Available resources

A More Practical Operational Solution

While our previous solution works well for manual backups, I’ve developed a more sustainable approach for long-term operations. Here’s how we can enhance our setup:

1. Automated Backup Implementation

By modifying our docker-compose.yml, we can create a more robust automated backup system:

services:
  db:
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 30s
      timeout: 10s
      retries: 3
      start_period: 40s

  wpsql:
    depends_on:
      db:
        condition: service_healthy
    command: >
      /bin/bash -c "
      while ! mysqladmin ping -h \"$$MYSQL_HOST\" --silent; do
        sleep 1;
      done;
      while true; do
        echo 'Starting backup...';
        MYSQL_PWD=$$MYSQL_PASSWORD mysqldump -h $$MYSQL_HOST -u $$MYSQL_USER ${MYSQL_DATABASE} > /backup/${MYSQL_DATABASE}_backup.sql;
        echo 'Backup complete.';
        sleep 86400;
      done"

2. Dual Backup Strategy

To ensure maximum data security, I recommend implementing two complementary backup methods:

A. Automated Daily Backups via wpsql Container

• Runs as an integrated system component
• Leverages container health checking
• Provides consistent, scheduled backups
• Requires minimal manual intervention

B. Manual Backups Using Temporary Environment

• Perfect for pre-deployment verification
• Essential before major system changes
• Useful for creating test data copies
• Provides an emergency backup option

3. Enhanced Monitoring and Management

I’ve implemented several monitoring practices to ensure backup reliability:

A. Health Check Monitoring

# Check container status
docker ps

# Verify health check details
docker inspect wordpress-db | grep -A 10 Health

B. Backup Verification

# List backup files
ls -l backup/

# Check recent backup file sizes
find backup/ -type f -name "*.sql" -mtime -1 -ls

C. Log Monitoring

# Review backup container logs
docker logs wpsql

4. Recovery Procedures

I’ve documented our recovery procedures to ensure quick response in case of emergencies:

A. Restoring from Backup

# Database restoration command
docker exec -i wordpress-db mysql -u root -p${MYSQL_ROOT_PASSWORD} < backup/[backup_filename].sql

B. Emergency Recovery Steps

1. Stop affected containers
2. Create data directory backup
3. Launch new container instances
4. Restore from latest backup

Ready-to-Use WordPress Solution

For those interested in implementing this solution, I’ve created a GitHub repository with all the necessary scripts and configurations:

GitHub Repository: wpbk

Conclusion

Through this experience with MySQL 9.1 and WordPress backups, I’ve learned that several elements are crucial for a reliable backup system:

1. Proper Health Check Implementation

• Regular container status monitoring
• Automated health verification
• Quick problem detection

1. Redundant Backup Strategies

• Automated daily backups
• Manual backup capabilities
• Multiple backup storage locations

1. Consistent Monitoring

• Regular log review
• Backup file verification
• System status checks

1. Clear Recovery Procedures

• Documented restoration steps
• Tested recovery processes
• Emergency response plans

By implementing these elements, we’ve created a more resilient and reliable backup system for our WordPress environment on RHEL/AlmaLinux with MySQL 9.1.