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
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.
- 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.
November 13th, 2009 at 17:11
[...] http://sewmyheadon.com/2009/mysql-search-replace-tool/ a few seconds ago from web [...]
November 14th, 2009 at 03:53
[...] See Eric’s revisions here: http://sewmyheadon.com/2009/mysql-search-replace-tool/ [...]
December 1st, 2009 at 13:30
What php version is this?
December 1st, 2009 at 13:33
It should work with PHP 4.x or 5.x
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?
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.
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?
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 [...]
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
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
May 13th, 2010 at 16:14
Thanks for this!!!
Works like a charm, saved my ass on a 70 MB database…
May 13th, 2010 at 16:16
Nice to hear it worked for you moonpixel!
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
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 [...]
May 31st, 2010 at 03:41
[...] ferramenta que é simples de utilizar. Falo do MySQL Search & Replace Tool disponibilizado por Eric Amundson no seu blog [...]
August 10th, 2010 at 17:11
THANK YOU!!! You just saved me from pulling my hair out. Really.
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.
September 21st, 2010 at 09:24
Hi. Would this tool work to search and replace serialized data in the database? Thanks!
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.
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)
November 28th, 2010 at 14:57
[...] 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 [...]
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.
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.
February 28th, 2011 at 00:29
Thank you so much for this guys, it works great and it will really help me a lot.
March 5th, 2011 at 05:18
It works, it works well, it saved me my most precious commodity, Time. Thanks!
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
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“