Moving site: Using MySQL to search-and-replace WordPress domain name

It seems that the recommended way to change the references to the domain name in MySQL on a WordPress install is to take the whole thing offline and do it by using text tools on a database dump. Either that or change the settings in WordPress while the site is still live on the old domain.

It was too late for the latter and I could not be bothered to do the former – partly because I had just gone through the whole mysql dump routine, partly because the site I wanted to move was only one among a number of sites contained in the dump. While the web server was all set up to use the new domain name, WordPress persisted in redirecting me to the old.

So I looked at the recent database dump, figured out what tables and fields to target so that I could replace the domain name on a live install.

StackOverflow has the generic way to search and replace. After “USE databasename;”, it’s

UPDATE table_name SET field_name = REPLACE(field_name, 'foo', 'bar') WHERE INSTR(field_name, 'foo') > 0;

Here you’re replacing the text foo with the text bar in the field field_name in the table table_name.

We need to look at two tables: prefix_options and prefix_posts. ‘prefix_‘ is whatever your wp-config.php has set it to. By default it’s ‘wp_’:

 * WordPress Database Table prefix.
 * You can have multiple installations in one database if you give each
 * a unique prefix. Only numbers, letters, and underscores please!
$table_prefix  = 'wp_';

In the posts table each post has it’s unique identifier in the guid field. This will contain the full uri of the post, thus including the domain name. So I run:

UPDATE prefix_posts SET guid = REPLACE(guid, 'old_domain.', 'new_domain.') WHERE INSTR(field, 'old_domain.') > 0;

I append a point to the names because in this case I only wanted to move from one subdomain to another, i.e. from old_domain.main_domain.tld new_domain.main_domain.tld. Using a point helps avoid accidentally matching against other uses of the subdomain word.

The options table is a more diverse set of settings structured as 1) id for the setting, 2) name for the setting, 3) value for the setting and finally 4) autoload which I’m guessing is a boolean telling WordPress whether or nor to automatically read the setting in.

MariaDB [wordpress]> describe prefix_options;                                    
| Field        | Type                | Null | Key | Default | Extra          |
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(191)        | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   |     | yes     |                |
4 rows in set (0.002 sec)  

Regardless, the only place you will see the domain name here, is as the value of a setting (e.g. when you enter the domain name in ‘Settings’  / ‘General’). So I run

UPDATE prefix_options SET option_value = REPLACE(option_value, 'old_domain.', 'new_domain.') WHERE INSTR(option_value, 'old_domain.') > 0;

To be on the safe side, I restarted all services related to the site – database, PHP, web server – and the site responded without any visible errors at the new address. There may still be some old references lurking somewhere that will make themselves known over time but for a quick and dirty fix this did it for me.

Moving Truck flickr photo by Michael Coghlan shared under a Creative Commons (CC BY-SA 2.0) license

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.