The m is for maus~. I do what I please. About m~, The Archives and the Lifestream RSS



Migrate (WPML) WordPress

Or another method to replace old permalinks with new ones when you either have a database that’s too large or, by way of WPML, have to be sure you don’t corrupt characters. Also featured: the basic find & replace method.

This is a task that comes back again and again: you need to move a wordpress website and have to manually edit the permalinks. Which are usually hard-coded into the options, posts and post meta (menu home link, for example) tables.

[rant] Why in the world would WordPress have hard-coded slugs is beyond me. Like the home URL. Really? Orientation issues? There are, of course, half-ass explanations on why it’s built like that. Don’t believe the hype. [/rant]

[rant] I betcha my WordPress is now trying to figure out what to do with the rant shortcode. [/rant]

Back on course. So there’s two methods. The basic method and the edit-database-with-fingers-crossed (skip to code).

The basic method

It’s usually pretty straight forward.

  1. Export the WordPress database to a text file as plain SQL;
  2. Edit the SQL file by finding and replacing old domain names with the new ones (e.g. replace localhost/website with website.com). Not rocket surgery. Just mind your trailing slashes while doing the find & replace and you’re golden;
  3. Import file into the database you have on the production server;
  4. Pat on own back.

After a few successful migrations I stopped doing step 4. Doesn’t mean you should.

The huge file or preventing corrupted characters method

Also straight forward, but including a few more steps in a completely different order.

Needed when you either have a 150MB SQL file which just might freeze your text editor or you have Sinhalese characters in your tables (like people do in Sri Lanka) and they become corrupted — usually needed for a WPML website.

This is how you update permalinks in this situation:

  1. Export your database however you feel. Tar.gz for me;
  2. Import it on the production server;
  3. Do a search on the entire database for your old domain. What you want is to know exactly where the strings you need to correct can be found. They probably are in the following tables: wp_options (option values), wp_postmeta (menu items) & wp_posts (slugs and links). In the case of WPML, you’re also gonna find (a lot of) them in the wp_icl_translation_status (mapping) table and maybe in wp_icl_string_positions. So you probably know where to find them. But you should still search.
  4. Take the tables one by one and use the following (adapted for your own slugs) query: UPDATE `table_name` SET `column_name` = REPLACE(column_name, "old_domain", "new_domain"). Again: mind your trailing slashes or you might come a few hits shorter. Also, you might have more than one column per table, like in the case of the wp_posts table.
  5. Search the database again for your old domain. If you have 0 hits, you’re done;
  6. Pat on own back.

Notes

You don’t have to do updates table by table and column by column, but it gives you a better sense of security. Like if you make a mistake you’re not fucking up your entire database. If you do fuck it up, drop all tables and import again. But you knew that.

I’m assuming you know how to “get” to your databases (phpMyAdmin comes to mind). Also, a bit of SQL knowledge is required. Otherwise you might find yourself in a bit-more-than-can-chew type of situation.

You might be better off just skipping the string positions table and just re-scan your files.

Don’t forget to edit your .htaccess file to match the new permalink structure. You can, of course, delete it entirely and rebuild the permalinks, but there’s no fun in that.

The file size and language are just examples, right? There are other situations in which editing the database directly is the thing to do.

The whole thing

Since I basically wrote this for my future self, I’m feeling generous and think like I should just give the guy a break and list all the queries. Hope he won’t find it condescending… Neah, he’ll be cool. On the other hand, seeing how he’s from the future, I don’t can’t say I really know the guy. Sure hope he’ll appreciate the effort. Plus a lot of copying and pasting just blows.

# site, blog and feed URL at least
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, "old_domain", "new_domain");

# menu items
UPDATE `wp_postmeta` SET `meta_value` = REPLACE(meta_value, "old_domain", "new_domain");

# slugs
UPDATE `wp_posts` SET `guid` = REPLACE(guid, "old_domain", "new_domain");

# post to post links
UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, "old_domain", "new_domain");

# WPML mappings
UPDATE `wp_icl_translation_status` SET `translation_package` = REPLACE(translation_package, "old_domain", "new_domain");

# WPML strings.
UPDATE `wp_icl_string_positions` SET `position_in_page` = REPLACE(position_in_page, "old_domain", "new_domain");