Replace strings MySQL

If you ever need to make big changes to your site, such as moving directory or changing the domain name you’ll know that you’ll have to make changes to the database for that to take affect.

First of all, before you make any adhoc changes like these to your database, ALWAYS take a backup.

Many of us use wordpress, and what you sometimes find is that if you move your blog into a subdirectory your content (such as images) will not point to the right place.

So, here’s what I did to fix that:

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘../wp-content/’, ‘/blog/wp-content/’);

This will update the wp_posts table, changing all occurrences of “../wp-content/” to the new “/blog/wp-content/” folder in the post_content field.

Pretty nifty.

If you’re using Drupal, and have changed the domain of your site, here’s what you need to do:

UPDATE node_revisions SET body = REPLACE(body, ‘old.example.com’, ‘new.example.com’);

UPDATE node_revisions SET teaser = REPLACE(teaser, ‘old.example.com’, ‘new.example.com’);

Obviously “old.example.com” is your old domain, and “new.example.com” is your new domain.

If you do change the domain of your site, you should also put a 301 URL redirect in place too.

This method of replacing strings in MySQL can also be useful if you are changing the directory of your Drupal installation.

If you need any help, just drop me a comment below.