WordPress Database Migration and Troubleshooting: A Practical Guide

Database Migration Challenges with MySQL 9.x

Initial Problems

When attempting to migrate a WordPress database, we encountered several challenges, particularly with MySQL 9.x in AlmaLinux environments. Here’s our experience and solutions.

Distribution-Specific Issues

# Attempting standard mysqldump in AlmaLinux (Red Hat-based)
docker exec wordpress-db mysqldump -u root -pTestPass dbin > backup.sql
# Results in authentication errors

Note: While this issue occurs in AlmaLinux (Red Hat-based), Ubuntu users typically don’t encounter this problem.

Step-by-Step Problem Solving

1. Database Connection Error

Initially encountered:

CopyError establishing a database connection

2. Database Migration Process

# Checking original database
mysql> USE dbin;
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_dbin           |
+--------------------------+
| wp_commentmeta           |
| wp_comments              |
...

Real-World Database Migration: Troubleshooting WordPress in Docker

When migrating WordPress databases in a Docker environment, you might encounter unexpected challenges. Here’s our real-world experience solving these issues, especially with MySQL 9.x in AlmaLinux environments.

The Challenge We Faced

What started as a seemingly straightforward database migration turned into an interesting troubleshooting journey. Here’s what happened:

Initial Symptoms

• WordPress showing “Error establishing a database connection”
• Unable to access wp-admin
• Database authentication issues specific to AlmaLinux with MySQL 9.x

The Migration Process

First, we tried the standard approach:

bashCopydocker exec wordpress-db mysqldump -u root -pTestPass dbin > backup.sql

This worked fine in Ubuntu but failed in AlmaLinux – a crucial distinction we discovered during our troubleshooting.

Database Verification Steps

mysql> USE dbfun;
mysql> SHOW TABLES;
Empty set (0.00 sec)

This empty result was our first clue something wasn’t right.

The Plot Thickens: Permalinks Issue

After successfully migrating the database, we faced another challenge:

• Homepage loaded correctly
• All other pages returned 404 errors
• Missing .htaccess file was the culprit

Real-World Database Migration: Troubleshooting WordPress in Docker

When migrating WordPress databases in a Docker environment, you might encounter unexpected challenges. Here’s our real-world experience solving these issues, especially with MySQL 9.x in AlmaLinux environments.

The Challenge We Faced

What started as a seemingly straightforward database migration turned into an interesting troubleshooting journey. Here’s what happened:

Initial Symptoms

• WordPress showing “Error establishing a database connection”
• Unable to access wp-admin
• Database authentication issues specific to AlmaLinux with MySQL 9.x

The Migration Process

First, we tried the standard approach:

bashCopydocker exec wordpress-db mysqldump -u root -pTestPass dbin > backup.sql

This worked fine in Ubuntu but failed in AlmaLinux – a crucial distinction we discovered during our troubleshooting.

Database Verification Steps

sqlCopymysql> USE dbfun;
mysql> SHOW TABLES;
Empty set (0.00 sec)

This empty result was our first clue something wasn’t right.

The Plot Thickens: Permalinks Issue

After successfully migrating the database, we faced another challenge:

• Homepage loaded correctly
• All other pages returned 404 errors
• Missing .htaccess file was the culprit

How We Solved It: A Step-by-Step Guide

The Database Migration Solution

After several attempts, we found a reliable workaround for AlmaLinux environments:

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'

Why this works:

• Bypasses authentication issues specific to MySQL 9.x
• Creates a clean temporary environment
• Ensures consistent backup regardless of OS

Fixing the Permalinks

The next challenge was getting permalinks working. Here’s what we needed:

1. Create a proper .htaccess file:

# 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

2. Set correct permissions:

# First, check the container's www-data user ID
docker exec wordpress id www-data
uid=33(www-data) gid=33(www-data) groups=33(www-data)

# Then set the correct permissions
sudo chown 33:33 .htaccess
sudo chmod 644 .htaccess

Verifying the Migration

After setting up the database and fixing permalinks, we needed to verify everything was working correctly:

# Check the database tables
mysql> USE dbfun;
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_dbfun          |
+--------------------------+
| wp_commentmeta           |
| wp_comments              |
| wp_links                 |
...
16 rows in set (0.01 sec)

# Verify crucial WordPress settings
mysql> SELECT option_name, option_value 
    FROM wp_options 
    WHERE option_name IN ('siteurl', 'home', 'template', 'stylesheet');
+-------------+-------------------+
| option_name | option_value      |
+-------------+-------------------+
| home        | https://showa.fun |
| siteurl     | https://showa.fun |
| stylesheet  | swell_child       |
| template    | swell             |
+-------------+-------------------+

Testing the Results

Key checkpoints we followed:

1. Homepage accessibility
2. Individual post URLs (e.g., /ubuntu-windows-share/)
3. WordPress admin panel
4. Image loading and media library access

A Note About Different Environments

What we learned about environment-specific issues:

• Ubuntu: Standard MySQL commands work fine
• AlmaLinux: Requires special handling with MySQL 9.x
• Docker considerations: User permissions and volume mounts

Key Lessons Learned

Through this migration process, we discovered several crucial insights:

1. Environment-Specific Challenges
   • MySQL 9.x behaves differently on AlmaLinux vs. Ubuntu
   • Authentication methods vary by distribution
   • Docker container user permissions require special attention
2. Critical Success Factors
   bash
   # Always verify database content after migration
mysql> SELECT COUNT(*) FROM wp_posts;
mysql> SELECT option_name, option_value FROM wp_options
WHERE option_name IN ('siteurl', 'home');
3. Best Practices
   • Always create backups before migration
   • Test permalinks thoroughly
   • Verify user permissions in both host and container
   • Check Apache configuration and mod_rewrite status

Troubleshooting Checklist

✓ Database connection verified
✓ Tables properly migrated
✓ WordPress settings confirmed
✓ Permalinks structure working
✓ User permissions set correctly
✓ .htaccess properly configured

Final Thoughts

While this migration presented unique challenges, particularly with MySQL 9.x on AlmaLinux, the solutions we found provide a reliable path forward. Whether you’re dealing with similar issues or planning a migration, these steps should help guide you through the process.

Remember: The key to successful migration lies in understanding your specific environment and methodically addressing each challenge as it arises.