In this post I’ll share some lessons learned finding and replacing a large number of broken links (over 13,000) in the mySQL database of a WordPress installation. After trying several different methods I finally found the free PHP utility “Search Replace Database by interconnectit,” which worked beautifully. Hopefully you won’t ever need this information, but if you find yourself in a WordPress pinch like I’ve been in the past year, these lessons and links might be helpful. (Please the end of this post for warnings / disclaimers.)
Ten months ago, I finished the painful process of migrating all my WordPress sites (30+ installs) from the web host WPEngine to my current web host, Site5. I wrote about the reasons for that switch and my lessons learned during the process in the January 2014 post, “Why I Switched My WordPress Web Host from WP Engine to Site5.” Unfortunately during that switchover, a large number of links in my site (which has almost 6000 posts now, and had over 5,500 then) were broken. This is because WPEngine used another URL as an “alias” for my website. That web address worked as an alternative to my own domain. As long as I had a WPEngine account, the website “wfryer.wpengine.com” was another way to see posts on the domain I own, “www.speedofcreativity.org.” Somehow when my site was migrated from WPEngine a LOT of links got changed inside my WordPress MySQL database. After canceling my WPEngine account, however, all those links in my WordPress installation which had the prefix “wfryer.wpengine.com” became broken. The free WordPress plugin “Broken Link Checker” found of almost 59,000 links included on my site, almost 15,000 of them were broken. It turns out all but about 2000 of those links (around 13,000) were broken because of this WPEngine domain URL change. Yikes.
This situation was bad for several reasons. First of all, when people visited my blog and tried to click an older link, it was VERY likely they would get a 404 error. (file not found) Second of all, Google and other search engines base their rankings (to a large extent) on the links websites contain and sites which link back. Since I started my WordPress site in 2005, I’d developed a very good Google SEO (search engine optimization) rating. I’m positive having so many broken links in my website hurt my SEO. I’ve been blogging less frequently in the past year, but my Google Analytics reports showed that my monthly web traffic has been going down to about half of its previous “normal” levels. That had been 50K – 60K unique visitors per month, which is pretty high for an educational blog.
Since mySQL WordPress changes can be dangerous and technically challenging to fix, this was also a bad situation for me. Fixing this required making changes to the MySQL database connected to my WordPress installation. While I have learned to make minor changes to my database using phpMyAdmin in CPANEL, the administrative website manager provided by my web host, (like removing database “overhead”) this was a “fix” I’d never attempted previously and I didn’t want to mess it up.
To prepare to make these changes to my mySQL WordPress database, I created two current backups. One was a full site backup using BackupBuddy, an invaluable plugin from iThemes. The second was a compressed export of my mySQL database, which I created using phpMyAdmin. After making these backups, I was ready to attempt the find and replace steps required to fix these link problems.
I found several blog posts (like this one) which described the steps of doing a “global find and replace” using phpMyAdmin. I was very reluctant to do this, however, since it requires precise syntax, and I know from past experiences that one misplaced quotation mark or other character can spell disaster. As an alternative, I found the free “Search and Replace” WordPress plugin. I tried to use it, but unfortunately (even though it could find thousands of instances of “wfryer.wpengine.com” in my database) it couldn’t repair them correctly. In fact, it made my situation worse: Somehow it replaced that domain with a series of numbers and dots which looked a little like an IP address but didn’t function as valid URLs.
As a result of this mess up, I had to either delete and restore my entire mySQL database using phpMyAdmin, or restore my entire website using my BackupBuddy backup. I used phpMyAdmin to “drop” (delete) all my WordPress tables in mySQL, and then tried to upload the backup. The first attempt only partially finished, resulting in a broken / non-functioning WordPress site. I dropped all the tables again, this time choosing to de-select the option for the import that supported interrupted uploads. This time it worked! My site was restored to its previous, 14,000+ broken link condition.
More Googling for solutions turned up the free PHP utility “Search Replace Database by interconnectit,” via this post from @wpmudev. That company is (I think) the same Australian WordPress gurus behind EduBlogs, an awesome WordPress hosting platform I’m very familiar with. With fingers crossed, I uploaded the PHP script as a ZIP file using the File Manager in my CPANEL and uncompressed it. I renamed it with an arcane title, and gave it a try. Whoa la! Victory! It successfully changed over 20,000 different cells my WordPress mySQL database in about a minute. To express my thanks, I bought a couple items on the Amazon wish list of the project lead for the “Search and Replace DB” php script, David Coveney.
As I wrote at the outset, I hope you don’t need this advice or to use the tools referenced in this post. If you do, keep in mind the warnings you’ll read on all the referenced sites about modifying mySQL for WordPress: It’s a dangerous business and it’s easy to mess things up! First make multiple backups of your database and WordPress site, so you can restore to your starting point if things go bad. If you attempt to fix things yourself, good luck! If you’re wary of these warnings and very hesitant to try this on your own, get some professional help! I hereby legally disclaim any responsibility for actions you may take using advice shared in this post! If you choose to move forward on your own, hopefully you’ll get your mySQL WordPress link problems resolved quicker than I did using the tips and links from this post!
If this helps you, please let me know via a comment or tweet to @wfryer.
If you enjoyed this post and found it useful, subscribe to Wes' free newsletter. Check out Wes' video tutorial library, "Playing with Media." Information about more ways to learn with Dr. Wesley Fryer are available on wesfryer.com/after.
On this day..
- Lessons Learned from Image Attribution & Tweetribution Confusion - 2011
- Microfiche, Copyrights, Open Licensing and Academic Journal Paywalls - 2010
- Dreaming of iPad Presentation Apps for the classroom - 2010
- Vote for Our Class Flip Video Scavenger Hunt Entries! - 2010
- Panoramic iPhone images from Hangzhou, China - 2009
- Help evaluate the 2008 K-12 Online Conference - 2008
- K12Online08 in the palm of my hand - 2008
- A Magnificent Moment in Lubbock - 2008
- 21st Century Cartographers by David Jakes - 2007
- One to One Initiatives (roundtable at TechForum07) - 2007