Comprehensive Guide to WordPress Database Migration Using WP-CLI
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:
- wp_options: Contains site configuration including home and siteurl
- wp_posts: Contains post content, excerpts, and custom fields
- wp_postmeta: Contains post metadata
- wp_links: Contains blogroll links (if used)
- wp_comments: Contains comment content
- wp_termmeta: Contains term metadata
Best Practices and Safety Tips
- Always create backups before any database operation
- Test the migration process on a staging environment first
- Verify file permissions after migration
- Clear the cache after migration
- 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.