Updating MySQL Content with Sed in Linux Mint

My previous couple of posts had to do with migrating 10 years of blog posts to WordPress.  2003 was a long time ago in blogging years and I was just getting started, doing a lot of dumb things like wrapping Verdana font tags around paragraphs and linking to images stored on a separate site I launched in 1995 which no longer existed.

Bottom line, there was a lot of old content that needed to be updated in bulk outside of the WordPress editor.  I needed to do a global search and replace directly against the MySQL database file.  Enter Sed.

Sed is a stream editor in Unix used to perform text transformations on a file or pipeline input stream. We’re going to do transforms on the WordPress database MySQL file.

One of the things I always liked about MySQL is it’s portability. “Portability” is probably not the best word, but it’s real easy to move databases around and possible to poke around in MySQL’s human-readable dump files. With those capabilities we can use mysqldump to create a Sed file, do a transform and pipe the file back into MySQL.

Create the MySQL Dump File

~$ mysqldump -u root -ptmppassword blogdb > tmp/blogdb.sql

Perform the Sed Transform

Using sed is as easy as

~$ sed -i ‘s/old string/new string/g’ myfile.txt

where -i edits the file in-place.  The only thing that’s tricky is handling special characters. To do so, simply precede them with a “\”.

Let’s say I want to clean up those links from 2003 that linked to images an old site that no longer exists. Our bad IMG links would read

<img src=”http://mylongdeadsite.com/images/2003/someimage.jpg” alt=”” />

Fortunately, while “mylongdeadsite.com” no longer exists, some things don’t change as we’re using the same file storage logic with all images stored (surprise!) on the same blog.  We want to change the above to

<img src=”/images/2003/someimage.jpg” alt=”” />

Here’s the sed command which we will run on our MySQL blogdb.sql dump file. We’re looking for “http://mylongdeadsite.com/images” and replacing it with “/images”.

~$ sed ‘s/http:\/\/mylongdeadsite.com\/images/\/images/g’ tmp/blogdb.sql

Back to MySQL

We have a few other weird conventions to fix and then we’ll return the updated database dump file back to MySQL and enjoy the fixes instantly!

~$ mysql -u root -ptmppassword blogdb < tmp/blogdb.sql