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
