Using standard tools such as PHPMyAdmin interface to run search and replace queries will not work in these cases as your find/replace will corrupt the serialized data and discard the whole content, leaving parts, if not all of the front end broken. This is where Interconnect/IT’s excellent “Search and Replace Script” becomes a must have tool for developers. In their own words, “Search Replace allows you to carry out database wide search/replace actions that don’t damage PHP serialized strings or objects with a user friendly interface and experience.” And it is easy to use and apply even with limit access to the server you are migrating to.
To use Search and Replace, simply:
1. Download the tool from their website: https://interconnectit.com/products/search-and-replace-for-wordpress-databases
2. Extract the archive and upload it via FTP or another preferred method to the server you are migrating to. Note that it must be put in a public facing location so that you can access the script via a browser. Never place the files directly in the root directory as the self delete script (mentioned below) could potentially delete all files when it runs. Always place it in a subfolder without any other site files.
3. Navigate to that URL in your browser. For WordPress sites, the script will automatically detect your WordPress configuration file and fill in the database connection details, but it is always best to double check that they are correct.
4. One you verify your connection details then just fill in the “search/replace” fields with the appropriate strings.
5. Under the “tables” section, “all tables” is checked by default, but you can check the “select tables” button and then define specific tables and columns you would like to run the script on.
6. Under “actions” you can choose “dry run” or “live run”. The “dry run” will go thru the search and replace process, showing you all changes, but without actually updating any data. The “live run” of course will go ahead and update the data.
7. As the script runs, it will display a list of changes that it has made so that you can review as it goes. Depending on the size of your database, it could take anywhere from several seconds to several minutes to run.
8. Once it is finished and you are satisfied with the changes the be sure to hit the “delete me” button to initiate the self delete functionality included in the script. If for some reason the script does not have permissions to delete the files, then besure to connect via FTP or another preferred method and delete the files. Leaving them on your server unprotected presents a security risk.
While the script was written largely with WordPress migrations in mind, it will work equally as well on other databases. I know I have successfully used it to migrate both Magento and Drupal databases as well. I learned the hard way about running a standard search and replace on serialized data. Hopefully this will save you the same hard lesson.
