Web Dev, Linux, WordPress & More

MySQL Search & Replace Tool

by eric on November 13th, 2009 in: Open Source

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.

MySQL Search & Replace

MySQL Search & Replace

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.

The MySQL Search & Replace project is hosted on LaunchPad, where you can report bugs, request features, checkout the latest version, read FAQs, contribute, and collaborate.

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:

http://localhost/projects/sewmyheadon

Of course, once I move the site to the web, it’ll probably have an address more like:

https://sewmyheadon.com

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.

  1. 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.
  2. Download or checkout the latest version of MySQL Search & Replace
  3. Unzip the files, if necessary, and place the mysql-replace folder on your server.
  4. Double-check that you covered Step #1
  5. Visit the URL for the MySQL Search & Replace Tool in your browser.  The URL might look something like: http://localhost/mysql-replace/
  6. Fill in the necessary fields and click Start.

Get MySQL Search & Replace


5 Responses to ' MySQL Search & Replace Tool '

Subscribe to comments with RSS or TrackBack to 'MySQL Search & Replace Tool'.

  1. eric said:

    December 1st, 2009 at 13:33

    It should work with PHP 4.x or 5.x

  2. eric said:

    January 24th, 2010 at 20:06

    Howdy bcks,

    Hmm. Not sure. I haven’t run into this error.

    What are you supplying for hostname?
    Are you running this on a live production server, or a copy?
    Have you double-checked your database name and username?

  3. eric said:

    May 13th, 2010 at 16:16

    Nice to hear it worked for you moonpixel! 🙂

  4. eric said:

    August 10th, 2010 at 17:43

    Quite welcome, Naomi. 🙂 So glad you found it helpful.

    Once we get caught up on a few other projects, we plan to add some neat improvements, so if you have feature or functionality suggestions, please let us know.

  5. eric said:

    September 21st, 2010 at 10:54

    Hi Matt,

    It should work with no problem. I’d recommend backing up and giving it a try.

    Then, if you could report back and let us know how it goes, that would be wonderful.

Leave a Reply

%d bloggers like this: