How to find and replace in MySQL with phpMyAdmin

mysqlSometimes you need to replace a string in your database with another string, and it can be rather tedious to plough through a large table manually. Thankfully MySQL can execute raw queries such as find and replace.

This comes in handy if you’ve moved a WordPress installation to another URL: you only need to tweak two values in the options table, but there may be countless image references and links in the posts and options table too. That’s where find and replace can come in handy.

You can execute the following statement either on the MySQL command line, or use phpMyAdmin’s Raw SQL option:

Screen Shot 2014-12-05 at 15.42.10

That big text field is where we’ll use the following code. Before we do however, make a backup of your database because there is NO UNDO FUNCTION in MySQL. A cute typo can break things beyond repair!

Here’s what the find and replace statement looks like in principle:

update table_name set field_name = replace(
field_name, 'original text',
'replacement text');

For WordPress specifically, if you’d like to replace text strings inside posts and pages, then wp_posts would be your table, and field_name is the column of that table. So for wp_posts this will be post_content. You can see the field labels at the top of each column when you select a table.

To replace a URL in all posts and pages the statement would look like this:

update wp_posts set post_content = replace(
post_content, 'http://oldurl.com/',
'http://newdomain.com/subfolder/');

As soon as you hit GO, MySQL will go to work and show you a success or failure message. The above would replace all image references and links from your old domain to the new one, where WordPress is installed in a subfolder.

Make a note of your table prefix and replace it accordingly. wp_ is the default, but this can easily be changed into something else for security reasons. Be cautious of trailing slashes when you’re replacing URLs.

Also note that a small letter “l” and a capital “I” look surprisingly similar in the phpMyAdmin! If you keep getting errors like “this table does not exist”, it’s something to watch out for before questioning your sanity again 😉

 

Replacing URL strings in WordPress

I use this technique when I need to replace URLs across an entire WordPress installation. Those can hide not only in posts, but also in widgets and menus. Here’s a list of places to hunt for them:

  • wp_posts table, in the posts_content field (links inside posts and pages)
  • wp_links table, in the link_url field (the old Link Manager)
  • wp_postmeta table, in the meta_value field (URLs of Custom Menu items)
  • wp_options table, in the option_value field (anything saved by themes and plugins)
  • wp_comments table, in the comment_content field (URLs inside comments)

And while we’re talking about replacing URLs: if you need to change the root URL of a WordPress installation, this is done in wp_options too. Look for two values called siteurl and home.

 

Further Reading





Jay is the CEO and founder of WP Hosting, a boutique style managed WordPress hosting and support service. He has been working with Plesk since version 9 and is a qualified Parallels Automation Professional. In his spare time he likes to develop iOS apps and WordPress plugins, or draw on tablet devices. He blogs about his coding journey at http://wpguru.co.uk and http://pinkstone.co.uk.