Manual find & replace: WordPress database

Manual search-and-replace in WordPress

Last updated on December 15, 2009. Tags: MySQL database, phpMyAdmin, search and replace

In my earlier post, I discussed how to do search and replace using the plugin developed by Mark Cunningham and Frank Bueltge. Now, I will discuss how to do search and replace manually. This is useful in cases wherein you need to replace all instances of a certain string throughout the website, and in moving your site to a new domain with its own new hosting.

WARNING: Don't try this method unless you really know how to edit database.

If you want to learn how to edit database, setup another WordPress blog that you can use on experimenting with database and NEVER EVER touch the database of your websites that are already operational.

In this tutorial, I assume that you know how to export and import database using the PhpMyAdmin provided by your hosting provider. I will no longer provide a detailed explanation on how to do it as different hosting providers have different versions of PHPMyAdmin, and providing details and screenshots might further confuse those whose PHPMyAdmin interfaces do not look like the one I use. If you have any clarifications, I encourage you to let me know using the comment form at the end of this post.

Unlike the first method, you cannot select particular parts of the database on which to perform find and replace. If you want to replace the word "cabbage" with "lettuce", you need to do it across the database including the nickname of comment authors in case anyone of them goes by the nickname "cabbage". It's advantage? It does not miss any string of text, making it particularly useful if you need to migrate from one domain to another.

In your hosting manager or hosting control panel, open the PhpMyAdmin and select the database from where your WordPress-powered website retrieves data (if you don't know which database is that, opem the wp-config.php of your WordPress and you will see it there). Click on the "Export" tab and export the database as SQL file in your hard drive. Give the sql file a file name; say we name it as original.sql for the purpose of this discussion, but of course you can name it with whatever you want

Copy the SQL file and give it a new filename, say its name is new.sql. Open the new.sql using notepad (or any text editor you have or you like using). Then use CTRL + H to open the find and replace function of notepad. Type the string that you want to replace on the upper text box and the string that will replace it on the lower text box. Click the "Replace All" button.

Go back to PHPMyAdmin and open the database from where you obtained the SQL file. Click the "Import" tab and import new.sql. If you cannot overwrite the database, click on the "Structure" tab, check all tables and select "Drop" (or the red X mark to drop them one by one). After you dropped all the tables, try importing new.sql again. It should be able to import by now. If you messed up along the way, import the original.sql to reset the database in its original state.

If you are moving to a new domain and hosting, upload all the files (including WordPress files) in the new hosting, create a database in that hosting, import the edited SQL file and reconfigure the wp-config.php of WordPress (I assume you already know how to configure MySQL database user and password, if not let me know through the comment).

Alternatively, if you installed WordPress in the new hosting using autoinstall feature like Fantastico, find the database of that WordPress from the wp-config.php and overwrite it by importing the edited SQL file. If the version of this WordPress is different from the version installed in your original site, you will be prompted to "Install WordPress" upon logging-in to dashboard. Just click the install button and your website in its new hosting will work properly.

Posted by Greten on December 10, 2009 under Server Configuration, WordPress tweaks

Share and Enjoy: