Fix media URLs after moving WordPress into a sub-directory

I’m currently in the process of migrating a bunch of WordPress sites to a lovely new Ubuntu flavouredĀ Rackspace cloud server. On most of them it’s a pretty straight forward MySQL dump and copy wp-content folder procedure.

But on one site I wanted to move the WordPress files into a sub-directory. Rather annoyingly (in this case) WordPress uses absolute URLs when you insert images and other media into posts so updating options on the WordPress backend doesn’t change them, so we need to use a little MySQL muscle to fix things:

https://gist.github.com/1518313

The above command assumes your using the default wp_ prefix on your tables and the sub-directory you’ve moved WordPress into is named wp. Of course you should always backup your database before any tinkering like this and since all we’re doing is a find and replace you can use whatever you like in the quote marks to change other things in your posts.

You can read up more on migrating WordPress in the Codex

Thanks to calebjones on the WordPress.org forums for this snippet.