Pitfalls of Backup Automation: Issues and Solutions Following the MySQL 9.1 Update

Introduction

Automating WordPress backups is a crucial task for many of us who manage servers. When running Docker on RHEL-based environments like RHEL or AlmaLinux, I thought my backup scripts were working flawlessly. But after updating MySQL to version 9.1, I suddenly discovered they had stopped functioning altogether.

In this article, I’ll dive into why this problem occurred and how I fixed it.

Environment Setup

For this setup, I’m using:

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

The required file structure is:

wordpress-docker/
├── docker-compose.yml   (Container configuration definition)
├── Dockerfile           (Customization of the WordPress container)
├── entrypoint.sh        (Initialization script at startup)
├── setup.sh             (Environment setup script)
├── .env                 (Environment variable settings)
├── php.ini              (PHP configuration file)
└── backup/              (Backup storage directory)

Encountering the Issue

One day, as part of routine system updates, MySQL was upgraded to version 9.1. After that, I noticed that the backups that were supposed to run daily were no longer being taken.

Unexpected Errors and How I Addressed Them

While managing the system, I encountered the following error message:

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

This error is related to the health check feature of Docker containers. Interestingly, it occurred intermittently and sometimes resolved itself naturally.

Root Cause and Countermeasures

Such unexpected behavior changes aren’t uncommon in Docker environments. Possible main causes include:

• Clearing the Docker daemon cache
• Resetting statuses by rebooting the system
• Automatic updates of Docker
• Issues with container startup timing and dependencies

Solution: Improving docker-compose.yml

To tackle this issue, I added health check settings to the docker-compose.yml file and revised the configuration to be more robust.

WordPress container settings:

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

Database container settings:

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

Improving the Setup Script

I also enhanced the setup.sh script to more reliably check the container’s status:

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 smoothly."
            break
        fi
    fi
    echo "Waiting for the WordPress container to start..."
    sleep 5
done

Operational Recommendations

From this experience, I recommend the following operational policies:

• Regularly save logs
  docker compose logs > docker_logs_$(date +%Y%m%d).txt
• Regularly check system status
  docker system events --since 30m # Check events from the last 30 minutes
• Take backups before important operations
  docker exec wordpress-db mysqldump -u root -p wordpress > backup_$(date +%Y%m%d).sql

By improving the settings in this way, the system’s stability and reliability have increased.

The Core Issue: Database Backup Failure

While the system’s stability improved, I faced a more serious issue: I couldn’t take database backups.

When I ran the usual backup command:

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

I got the following error:

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

This error is a critical problem because:

• Daily backups can’t be performed
• Recovery in case of failure becomes difficult
• The system’s safety cannot be ensured

Why Did This Problem Occur?

This issue arose after updating to MySQL 9.1. The backup process, which had been working normally until then, suddenly started failing after the update.

Journey to the Solution

1. First Attempt: The Usual Backup Command

First, I tried the standard backup method but encountered an authentication error:

docker exec wordpress-db mysqldump -u root -p dbin > backup_$(date +%Y%m%d).sql
Enter password:
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

2. Considering Temporary Workarounds

Next, I attempted the following measures, but none were successful:

• Specifying the password directly
• Accessing with a different user
• Running the backup inside the container
  docker exec -it wordpress-db bash
mysqldump -u root -p dbin > dbin_backup.sql
docker exec wordpress-db mysqldump -u root -p dbin > dbin_backup.sql

Whether inside or outside the container, I couldn’t export due to the following error:

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

I focused on trying to export, but when I checked whether I could even log in, I found that was impossible as well.

3. Solution: Backup Method Using a Temporary Environment

Finally, I was able to solve the problem using the following steps.

Preparing a Temporary Data Directory

First, I created temporary directories and copied the data:

# Create temporary directories
mkdir -p temp_mysql_data temp_run_mysqld

# Copy data
sudo cp -r db_data/* temp_mysql_data/

Running the Backup

Next, I ran the backup using a temporary Docker 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'

Cleanup

After the backup was complete, I removed the temporary directories:

# Remove temporary directories
sudo rm -rf temp_mysql_data temp_run_mysqld

Why Does This Method Work?

This solution works because:

• Skipping authentication bypasses the strict authentication requirements of MySQL 9.1.
• Disabling network access ensures security.
• Using a temporary environment means it doesn’t affect the production environment.

Detailed Explanation of the Solution

Let’s delve deeper into how this backup method works.

Components of the Command

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

Main options explained:

• --rm: Automatically removes the container after it exits.
• --platform linux/arm64/v8: Specifies the ARM64 architecture.
• -v: Sets up three volume mounts:
  • For database files
  • For MySQL socket files
  • For backup storage

Explanation of the Execution Command

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

Roles of each part:

• docker-entrypoint.sh mysqld: Initializes and starts the MySQL server.
• --skip-grant-tables --skip-networking: Skips authentication and disables network access for security.
• sleep 30: Waits for the MySQL server to finish starting up.
• mysqldump options:
  • --no-tablespaces: Excludes tablespace information.
  • --skip-add-drop-table: Does not generate DROP TABLE statements.
  • --all-databases: Backs up all databases.
• Output file name: Saves with a timestamp in the format YYYYMMDD_HHMMSS.

Points to be aware of:

• You need to prepare temporary directories.
• May require administrative privileges (sudo) to execute.
• Ensure sufficient wait time until the backup is complete.

An Improved Solution for More Practical Operations

The temporary environment backup method is effective in emergencies, but for long-term operations, a more systematic approach is needed.

1. Implementing Automatic Backups via docker-compose.yml

By adding the following improvements to the previous configuration, more stable operation is possible:

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 enhance reliability, I recommend combining the following two methods:

• Daily backups using the wpsql container
  • Automated regular backups
  • Integrated management as part of the system
  • Integration with health checks
• Manual backups using a temporary environment
  • Reliable backups before important changes
  • Emergency backups during trouble
  • Obtaining data for testing

3. Enhancing Monitoring and Management

a. Monitoring Health Checks

Check the container status:

docker ps

Check health check details:

docker inspect wordpress-db | grep -A 10 Health

b. Verifying Backup Success

Check backup files:

ls -l backup/

Check the size of the latest backup file:

find backup/ -type f -name "*.sql" -mtime -1 -ls

c. Regularly Checking Logs

Check backup container logs:

docker logs wpsql

4. Recovery Procedures

a. Restoring from Backup

Restore the database:

docker exec -i wordpress-db mysql -u root -p${MYSQL_ROOT_PASSWORD} < backup/[backup_filename]

b. Switching Procedures During Failures

• Stop the container
• Backup the data directory
• Start a new container
• Restore the data

You can find the GitHub page for the completed one-click WordPress startup script here:

GitHub

GitHub – superdoccimo/wpbk Contribute to superdoccimo/wpbk development by creating an account on GitHub.

Conclusion

For WordPress backups in a MySQL 9.1 environment, it’s crucial to:

• Properly implement health checks
• Use dual backup methods
• Regularly monitor the system
• Have clear recovery procedures in place

By combining these elements, you can build a more reliable backup system.

👉 GitHub Repository for One-Click WordPress Setup