WordPress Site Migration: How to Batch Replace Domains in the Database Using SQL

WordPress Database Domain Replacement Methods

When migrating a WordPress site or changing its domain, a common issue is that the database still contains numerous old domain links (e.g., in post content, image URLs). Manually editing each link is impractical; executing SQL queries for batch replacement is the most efficient approach.

Core SQL Replacement Statement

The core of batch replacement is using MySQL's REPLACE() function. The most basic and commonly used statement for replacing strings in post content (post_content) is:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'old-domain.com', 'new-domain.com');

Statement Breakdown:

• UPDATE wp_posts: Specifies the database table to update, typically wp_posts, which stores all post and page content.
• SET post_content = REPLACE(post_content, 'old-string', 'new-string'): Replaces all occurrences of "old-string" with "new-string" in the post_content field.

Detailed Steps

1. Backup Your Database: Before any SQL operation, always create a full backup via your hosting panel, phpMyAdmin, or a plugin to prevent errors.
2. Access Database Management Tool: Log into your hosting control panel (e.g., cPanel), find and open phpMyAdmin. Select your WordPress database.
3. Execute the SQL Statement:
   • Click the "SQL" tab in the top navigation.
   • Paste the modified SQL statement into the command input box (replace the example domains with your actual old and new domains).
   • Click the "Go" or "Execute" button.
4. Verify Results: After successful execution, browse your site's front end or check a few posts in the editor to confirm links and image URLs are updated.

Extended Applications and Important Notes

This method is not limited to domain replacement; it can be used for batch modifications of other content.

• Replace Data in Other Tables:

  -- Replace post excerpts
  UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, 'old-text', 'new-text');
  -- Replace post meta (e.g., custom fields)
  UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'old-text', 'new-text') WHERE meta_key = 'your-field-name';

• Replace GUID: The wp_posts table has a guid field that may contain the old domain. Note: official WordPress documentation advises against modifying this field unless you fully understand its implications. In most cases, replacing post_content is sufficient.
• Note Table Prefix: If your WordPress installation uses a custom database prefix (not wp_), replace wp_ in the statements with your actual prefix, e.g., myprefix_posts.
• Plugin Alternatives: If you are uncomfortable with direct database operations, consider using dedicated plugins like "Better Search Replace" or "Velvet Blues Update URLs," which provide a safer graphical interface for this task.

With this simple SQL statement, you can quickly handle the tedious link updates after site migration, significantly improving efficiency.