I’ve been working a lot with MySQL lately, especially after the major theme and plugin upgrades on my blog. I was dealing with a bunch of content issues like redundant shortcodes and post meta, URL changes, images directories and more.
One simple solution would be to grab the database dump, perform various search and replace operations and then feed it back in, and my goal was to do that without data loss, without going offline, without sending files back and forth but mainly for learning purposes.
I found a UDF for MySQL called lib_mysqludf_preg and here’s how I got it to run on Ubuntu 11.04, somewhere in a temporary directory:
sudo apt-get install libpcre3-dev libmysqlclient-dev wget http://www.mysqludf.org/lib_mysqludf_preg/lib_mysqludf_preg-1.0.1.tar.gz tar -xf lib_mysqludf_preg-1.0.1.tar.gz cd lib_mysqludf_preg-1.0.1 sudo ./configure sudo make install sudo service mysql restart
So, install some libraries upon which the UDF may depend, download the UDF archive, extract it, browse to what has been extracted, configure, install and restart MySQL (just in case.) If you’re reading this and it’s not 2011, make sure you get the latest version and read the release notes too.
At this point the UDF library is installed but the functions are not available yet, so log in to your MySQL command line, preferably as root and create your new preg_replace function like this:
CREATE FUNCTION preg_replace RETURNS STRING SONAME 'lib_mysqludf_preg.so';
Voila! And here’s how I removed a shortcode from all posts and pages:
UPDATE wp_posts
SET post_content = PREG_REPLACE('/[my_shortcode*?.*?]/', '', post_content)
WHERE post_content LIKE '%[my_shortcode%';
Make sure you backup your database first though before doing any of this. I did! But everything went fine. My next goal is to get the shortcode provided by a syntax highlighting plugin replaced with a <pre> tag. Hopefully I won’t break anything so good luck to me and thank you so much for stopping by!
