Database migration is a critical part of moving WordPress sites between environments. This guide covers various approaches to migrating your WordPress database using WP-CLI, with special attention to handling URL replacements effectively.

Basic Migration Process

Before starting any database migration, always create backups of both your source and destination databases. Here’s the standard process:

On Your Source Site

bashCopy# Create a backup
wp db export backup_source.sql

# Create migration file
wp db export migration.sql

On Your Destination Site

bashCopy# Create backup of existing destination database
wp db export backup_live.sql

# Import the migration file
wp db import migration.sql

# Update WordPress configuration
wp option update siteurl "https://your-new-domain.com"
wp option update home "https://your-new-domain.com"

Advanced URL Replacement Strategies

Strategy 1: Full URL Replacement

If you want to replace all instances of your old domain with the new one:

bashCopywp search-replace 'old-domain.com' 'new-domain.com'

Strategy 2: Preserve Post Content URLs

To maintain all URLs within post content while updating URLs elsewhere in the database:

bashCopywp search-replace 'old-domain.com' 'new-domain.com' --skip-tables=wp_posts

This command updates URLs in all tables except wp_posts, preserving links within your post content.

Key Tables Affected by URL Updates

The WordPress database contains several tables where URLs might need updating:

  1. wp_options: Contains site configuration including home and siteurl
  2. wp_posts: Contains post content, excerpts, and custom fields
  3. wp_postmeta: Contains post metadata
  4. wp_links: Contains blogroll links (if used)
  5. wp_comments: Contains comment content
  6. wp_termmeta: Contains term metadata

Best Practices and Safety Tips

  1. Always create backups before any database operation
  2. Test the migration process on a staging environment first
  3. Verify file permissions after migration
  4. Clear the cache after migration
  5. Test the site thoroughly after migration, especially:
    • Homepage and key landing pages
    • Media attachments
    • Internal links
    • Forms and interactive elements

Common Issues and Solutions

Broken Media Links

If media links break after migration, verify that:

  • The wp_postmeta table has been properly updated
  • Your media files have been transferred to the new server
  • The upload directory path is correct in your WordPress settings

Mixed Content Warnings

If you’re moving from HTTP to HTTPS, you might need to run an additional search-replace:

bashCopywp search-replace 'http://new-domain.com' 'https://new-domain.com'

Database Connection Issues

If you encounter database connection problems after migration, verify:

  • Database credentials in wp-config.php
  • Database user permissions
  • Database host configuration

Advanced WP-CLI Commands for Migration

Dry Run Testing

Before performing actual replacements, test with the –dry-run flag:

bashCopywp search-replace 'old-domain.com' 'new-domain.com' --dry-run

Custom SQL Export

Export specific tables:

bashCopywp db export --tables=wp_options,wp_postmeta custom_export.sql

Conclusion

WordPress database migration requires careful planning and execution. By following these guidelines and choosing the appropriate URL replacement strategy, you can ensure a smooth transition to your new environment while maintaining data integrity.

Remember to always maintain backups and test thoroughly after migration. For complex sites with custom functionality, consider additional testing steps specific to your implementation.