How to Replace Database Entries Using SSH

Overview

Replacing database entries is a common task during domain migrations, URL changes, or fixing incorrect data across a website. This guide covers three approaches: direct MySQL queries, sed with mysqldump for bulk replacements, and WP-CLI for WordPress sites.

Important: Always create a full database backup before performing any search-and-replace operation.

Method 1: MySQL Command-Line Queries

Connect to MySQL

mysql -u username -p database_name

Search for entries

SELECT * FROM wp_options WHERE option_value LIKE '%old-domain.com%';

Replace a value in a specific table and column

UPDATE wp_options SET option_value = REPLACE(option_value, 'old-domain.com', 'new-domain.com');

Replace across multiple tables

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://old-domain.com', 'https://new-domain.com');
UPDATE wp_posts SET guid = REPLACE(guid, 'http://old-domain.com', 'https://new-domain.com');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'http://old-domain.com', 'https://new-domain.com');
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://old-domain.com', 'https://new-domain.com');

Count matches before replacing

SELECT COUNT(*) FROM wp_posts WHERE post_content LIKE '%old-domain.com%';

Exit MySQL

EXIT;

Method 2: sed with mysqldump (Bulk Replace in Dump File)

This approach exports the database, performs the replacement on the dump file, and re-imports it. It’s useful for large-scale replacements.

Step 1: Export the database

mysqldump -u username -p database_name > dump.sql

Step 2: Replace strings in the dump file

sed -i 's/old-domain\.com/new-domain.com/g' dump.sql

Note: Escape dots with \. in sed patterns since . matches any character in regex.

Step 3: Re-import the modified dump

mysql -u username -p database_name < dump.sql

Caution with serialized data

WordPress and many PHP applications store serialized data in the database. Simple string replacement with sed or SQL REPLACE() breaks serialized data because the replacement changes string lengths without updating the length counters. For WordPress sites, use WP-CLI instead (see Method 3).

Method 3: WP-CLI Search-Replace (WordPress)

WP-CLI handles serialized data correctly, making it the safest option for WordPress sites.

Basic search-replace

wp search-replace 'old-domain.com' 'new-domain.com' --path=/home/username/public_html

Dry run (preview changes without applying)

wp search-replace 'old-domain.com' 'new-domain.com' --dry-run --path=/home/username/public_html

Output shows how many replacements would be made per table:

+------------------+-----------------------+--------------+------+
| Table            | Column                | Replacements | Type |
+------------------+-----------------------+--------------+------+
| wp_options       | option_value          | 5            | PHP  |
| wp_posts         | post_content          | 142          | PHP  |
| wp_postmeta      | meta_value            | 38           | PHP  |
+------------------+-----------------------+--------------+------+

Replace HTTP with HTTPS

wp search-replace 'http://yourdomain.com' 'https://yourdomain.com' --path=/home/username/public_html

Replace across specific tables only

wp search-replace 'old-value' 'new-value' wp_posts wp_postmeta --path=/home/username/public_html

Skip specific tables

wp search-replace 'old-domain.com' 'new-domain.com' --skip-tables=wp_users --path=/home/username/public_html

Common Use Cases

Tips

• Always back up first: mysqldump -u user -p dbname > backup-before-replace.sql
• Use --dry-run with WP-CLI to preview changes before committing.
• For WordPress, always prefer WP-CLI over raw SQL to avoid breaking serialized data.
• If WP-CLI is not installed, you can install it via SSH:

  curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
  chmod +x wp-cli.phar
  sudo mv wp-cli.phar /usr/local/bin/wp
  

• For non-WordPress applications, verify that the application doesn’t use serialized data before using sed or SQL REPLACE().