MySQL Search & Replace Tool
This web-based tool written in PHP makes it easy to search and replace text strings in a MySQL database.
Usually, I develop WordPress sites on a local development server and, when they are ready to launch, I move them to a live web server.
For database-driven sites created with content management systems like WordPress, Drupal, and Joomla, this means moving any custom themes and plugins, and it often entails exporting data from the development server’s database and copying it to the live web server.
With small databases, your content management system’s built-in import/export functions might work, or you can even search and replace an exported .sql file using a simple text editor or IDE.
Unfortunately, this doesn’t always work with larger databases, which can often be unruly and cause programs to hang, crash, spit, choke, and curse.
I searched around and found this PHP script by Mark Jackson which makes it much faster and easier to search and replace data across an entire MySQL database.
MySQL Search & Replace Project is Born
I created a quick little front end for Mark’s script using a little XHTML markup and CSS, tweaked a little PHP, and Mark and I decided to package up this tool and release it to the community under the GPL in hopes that it might help some other folks.
Using MySQL Search & Replace with WordPress
Before attempting to search and replace, please make sure to backup your database.
While this tool is easy to use, it’s also quite powerful and could easily cause harm to your database, if you’re not careful. Please make sure to read the included readme.txt file for more detailed instructions.
Since I develop WordPress sites on my local Ubuntu machines using a local LAMP install, the URLs of my work-in-progress sites usually look like:
Of course, once I move the site to the web, it’ll probably have an address more like:
Since these URLs usually occur many times in a WordPress database, search and replace can be an invaluable tool in making sure they’re all corrected before moving the database.
Prepare a WordPress Database for a Move:
NOTE: Before running an actual replace, you can simply search to see how many times, and where, the string occurs in the database.
- Backup, backup, backup. Use phpMyAdmin or a WordPress plugin, but make sure you have a backup. You can’t say I didn’t tell you.
- Download or checkout the latest version of MySQL Search & Replace
- Unzip the files, if necessary, and place the mysql-replace folder on your server.
- Double-check that you covered Step #1
- Visit the URL for the MySQL Search & Replace Tool in your browser. The URL might look something like: http://localhost/mysql-replace/
- Fill in the necessary fields and click Start.