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:

http://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


48 Responses to ' MySQL Search & Replace Tool '

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

  1. [...] http://sewmyheadon.com/2009/mysql-search-replace-tool/ a few seconds ago from web [...]

  2. [...] See Eric’s revisions here: http://sewmyheadon.com/2009/mysql-search-replace-tool/ [...]

  3. Anonymous said:

    December 1st, 2009 at 13:30

    What php version is this?

  4. eric said:

    December 1st, 2009 at 13:33

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

  5. bcks said:

    January 24th, 2010 at 19:01

    I’m getting an error with this -

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

    It’s saying there is something wrong with this line: $field_r = mysql_fetch_assoc($field_q)

    Any ideas?

  6. bcks said:

    January 24th, 2010 at 19:01

    Forgot to say – I’m using Plesk, php 5.0.2.

    I have used this script before successfully just not in plesk.

  7. 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?

  8. February 11th, 2010 at 06:44

    [...] sitesi: http://sewmyheadon.com/2009/mysql-search-replace-tool/ İndirmek için: http://sewmyheadon.com/wp-content/uploads/2009/11/mysql-replace.zip Açık [...]

  9. Toby Adams said:

    April 22nd, 2010 at 01:08

    Fantastic tool, I am going to run this this afternoon to morph:

    http://lateforlunch.co.uk/wpmu

    to

    http://lateforlunch.co.uk/blogs

  10. Titus said:

    May 1st, 2010 at 10:30

    Fantastic! I am going to use this to get my blog live at http://www.titus-hanke.info!

    Thanks alot, Titus

  11. moonpixel said:

    May 13th, 2010 at 16:14

    Thanks for this!!!
    Works like a charm, saved my ass on a 70 MB database…

  12. eric said:

    May 13th, 2010 at 16:16

    Nice to hear it worked for you moonpixel! :)

  13. May 15th, 2010 at 02:52

    Hi Eric

    great tool, thanks a lot.

    I am trying to edit a line of text on the site but can only find it using the search but not replace it. Any ideas? I get the following error mesage:

    (ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘option REGEXP(‘{rokbox title=|Scientific References :: NAS 2010| text=|Scientifi’ at line 1)

    Please advice

    Best Regards
    Johan Niklasson

  14. May 17th, 2010 at 09:04

    [...] I came across a most excellent script, the MySQL Database Search & Replace Tool. It’s a PHP page that you can drop into a web site and call from your browser. It will search [...]

  15. [...] ferramenta que é simples de utilizar. Falo do MySQL Search & Replace Tool disponibilizado por Eric Amundson no seu blog [...]

  16. Naomi Niles said:

    August 10th, 2010 at 17:11

    THANK YOU!!! You just saved me from pulling my hair out. Really.

  17. 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.

  18. Matt said:

    September 21st, 2010 at 09:24

    Hi. Would this tool work to search and replace serialized data in the database? Thanks!

  19. 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.

  20. Salvatore N. said:

    September 24th, 2010 at 16:39

    Hi!
    I try to replace mydomain.sbdomain.ext to http://www.mydomain.ext and all seems to work fine but after of this, if i launch again this script, it find many other values.
    This values are mydomain@sbdomain.ext (see @ instead of dot).
    I don’t know what happens if i run again the replace (i can’t test) but i think is best to fix) ;)

  21. [...] and Replace WordPress Plugins are options to install and consider using as an alternative. The MySQL Search & Replace Tool for WordPress by SewMyHeadOn is another non-WordPress Plugin option. These are WordPress-based tools, so they would restrict [...]

  22. MrGlasspoole said:

    December 2nd, 2010 at 04:03

    Hi, also have this bug https://bugs.launchpad.net/mysql-search-replace/+bug/580890

    Somebody there gives an answhere, but don’t know what he means.

  23. cakarayam said:

    February 12th, 2011 at 18:05

    hi, thanks for this useful post, i already move my blog to another domain and realize that all images on my post still pointing to old domain, so i need to find and replace all url on my post with my new domain. Thanks.

  24. Pieter said:

    February 28th, 2011 at 00:29

    Thank you so much for this guys, it works great and it will really help me a lot.

  25. Tim said:

    March 5th, 2011 at 05:18

    It works, it works well, it saved me my most precious commodity, Time. Thanks!

  26. ovidiu said:

    March 6th, 2011 at 11:59

    works perfect. I’d just love if it told me afterwards how many occurances it changed…
    or filter the results so it doesn’t display the tables where there are no occurances when I search? talking about a HUGE db so it takes ages to scroll down and check :-)

  27. ovidiu said:

    March 19th, 2011 at 08:55

    got a small issue: it sometimes give me an error but it continuous past this error so I am not sure how critical this is:

    `– -1 `wp_341_wpsc_categorisation_groups`.`default` (ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘default = REPLACE(default,’h1550830′,’h1870666′)’ at line 1)
    OK 0 `wp_341_wpsc_coupon_codes`.`coupon_code“

  28. cYrixmorten said:

    March 24th, 2011 at 12:41

    Thanks ! :D This is just what I needed.

    Only problem I had was that my database is prefixed with an uppercase letter, which is turned into lowercase at line 53 in your script, which wouldnt work. So, I just removed the strtolower and all was fine.

  29. Sajid said:

    March 31st, 2011 at 21:16

    hi,
    Eric..
    im new to php and working on my first php project of a school website..

    its not working on my server…
    its displaying only this in result…as i search or replace please tell me what is this…

    Searching for ‘home’ in ‘jones_db’

    STATUS | ROWS CONTAINING | TABLE/FIELD (+ERROR)
    OK 0 `jss_admin`.`name`
    OK 0 `jss_admin`.`password`
    OK 0 `jss_admin`.`email`
    OK 0 `jss_gallery`.`g_title`
    OK 0 `jss_gallery`.`g_text`
    OK 0 `jss_gallery`.`g_image`
    OK 1 `jss_pages`.`page_name`
    OK 1 `jss_pages`.`page_title`
    OK 1 `jss_pages`.`page_meta`
    OK 1 `jss_pages`.`page_meta_desc`
    OK 0 `jss_pages`.`page_content`

    I shall be very thankful to you..

  30. CJF said:

    April 25th, 2011 at 07:06

    Magic. Thanks. No, I really mean that…thanks. A whole lot.

  31. Pat said:

    June 22nd, 2011 at 15:21

    Fantastic app!! I went through essentially the same process that you did in regards to searching high and low for a solution and was in the process of writing my own plugin for wordpress multisite to be able to do exactly what you have done. However, I would still like to see this app become a wordpress plugin that work from the backend of wordpress. Why? My largest wordpress install uses a multi-database setup and with this app I have to search and replace on 16 databases to fully search and replace my database. Whereas in the backend of WP I could simply use the $wpdb constant to define the database and be able to search and replace all (I hope).

    As I am a WP plugin junkie I will see what I can do to adapt your app to a plugin and provide credit back to you guys as the original developers.

    Thanks for a great app!

    Pat

  32. GJ said:

    August 22nd, 2011 at 07:23

    Thnx man! Works perfect!

  33. [...] utilità: mysql-search-replace-tool [...]

  34. DarkWOlf said:

    October 16th, 2011 at 11:29

    Hi! Great tools but:
    1) i can’t search string with “brackets” like this: [sometest]
    2) i can’t search and replace with “jolly” eg:
    search: [html randomtext]
    replace: [html]

  35. TheGeneral said:

    December 16th, 2011 at 19:21

    Eric,

    There is an above comment posted Sept 21st, 2010 from a user named Matt regarding serialized data. Your plugin would have to be specifically programmed to handle serialized data in addition to standard data (tabular). Serialized data is a real pain to deal with (it’s like all the data in one giant string with a set number of character spaces), and I am also in need of a plug-in that can do this. I have a WordPress theme that basically can’t be moved, because it’s database is serialized. Poor design for sure, but who knew. Anyway, please let us know if you ever consider adding this feature. Thanks much for your work!

  36. Dan said:

    February 7th, 2012 at 20:29

    Dude you just saved me at least half an hour. Thank you! This is definitely going in my list of essential tools.

  37. junnydc said:

    March 10th, 2012 at 19:17

    thanks man. i just bookmark your site. I need this script coz i need to replicate my online db to localhost thanks again :)

  38. Chris said:

    May 31st, 2012 at 13:54

    The script works perfect , but on every run it returns. Seems the loop doesn’t finish properly.

    Warning: str_repeat(): Second argument has to be greater than or equal to 0 in /etc/phpmyadmin/replace/mysql-replace.php on line 112

    mysql Ver 14.14 Distrib 5.5.19, for debian-linux-gnu (x86_64) using readline 6.1
    PHP Version 5.3.9-1~dotdeb.3

  39. cre8fire said:

    June 11th, 2012 at 10:30

    Wonderful script – used it on a huge Drupal database to update a 1000+ links to a server that we retired to the address on a new server, and it simply did what it was designed to do without complication or hassle. I highly recommend this script.

  40. Hernan said:

    June 28th, 2012 at 10:09

    Awesome tool! I’ve been looking for this and there you go! Thanks guys! I could replace everything nice and easy :)

  41. [...] of PHP software that would perform a find and replace from the server. Created by Mark Jackson the MySQL Database Search & Replace Tool is free to download. You simple fill in the form (see screenshot above) with your database credentials and choose your [...]

  42. August 24th, 2012 at 15:03

    So Awesome. Doing a search and replace for a domain across the whole DB broke the site, but I simply restored the version prior to my search and replace, and then exported the tables the tool identified, ran search and replace again letting the site be broken, imported those tables without the change one by one until the site became unbroken. Once I knew which table it was that changing the domain name in was breaking the site, then I could and did manually make the changes I needed in the table and voila the website is now on it’s permanent domain.

    Could not have launched the website without MySQL Search & Replace Tool.

  43. dave said:

    August 26th, 2012 at 03:41

    Hi ya,

    just shifted a WP MU network from one domain to another and your script has saved me hours, the whole database was updated with the new url in a matter of seconds.

    Big thanks to you

    Dave

  44. Patric said:

    September 6th, 2012 at 19:51

    Your script and interface are superbe! You saved my life!

    Thanks a lot

  45. xinox said:

    September 16th, 2012 at 23:54

    Nice One,

    this will be a “must have” tool for me since i saw it today.

    Greetz

  46. Alan Kmiecik said:

    October 6th, 2012 at 14:38

    THANK YOU. Where is the donate button?

  47. Raymond said:

    December 6th, 2012 at 13:07

    Fantastic works great 10/10 thanks save my bacon.

    Do you have some thing for search and replace for htm files
    have a directory with thousand of files need to be updated .
    some thing like this but not for SQL just .htm files?

    fantastic job
    Raymond

  48. December 30th, 2012 at 19:29

    Still works like a charm. Made changing to the new domain name even easier.

Leave a Reply

%d bloggers like this: