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.

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


Leave a Reply

  1. Pingback: Eric Amundson (sewmyheadon) 's status on Saturday, 14-Nov-09 00:11:28 UTC - Identi.ca

  2. Pingback: Search and Replace text in whole MySQL database | MJDIGITAL :: Mark Jackson | Digital Consultant

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

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

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

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

  5. Pingback: Mysql Search & Replace - Bir ÅŸeyler var burada!

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

  7. Pingback: Search Everywhere in MySQL – Nerdaphernalia

  8. Pingback: Ferramenta para Substituir e Pesquisar na base de dados MySQL - Wordpress Total

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

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

  9. Pingback: Find, Search, Replace, and Delete In A WordPress Database - WordCast

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

  11. 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 :-)

  12. 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“

  13. Thanks ! 😀 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.

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

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

  16. Pingback: WanSoft » Cerca e sostituisci un testo nell’intero db mysql per wordpress

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

  18. 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!

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

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

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

  22. Pingback: Moving a large WordPress database to another host and domain « Design Nudge

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

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

  25. Same error as Chris: Warning: str_repeat() [function.str-repeat]: Second argument has to be greater than or equal to 0 in /home/xxx/public_html/replace/mysql-replace.php on line 112
    Otherwise perfect, saved me a lot of time, thanks!

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