WordPress – Move site from prod to local – missing widgets, missing logo, missing header image?!

Sometimes it is enough to change two option values in your database wp_optoins table:

UPDATE wp_options SET option_value = 'https://yournewdomain.com' WHERE option_name = "siteurl" AND option_name = "home";

But this is not always the case.

Once you dump the SQL file, you need to make some modification to a URLs due to domain name change. Now you have few options to choice. If you do it with text editor on Windows machine, you might get encoding and sterilize data issues. Therefore, you really should to import the file and make changes through SQL query instead, but only where data is not stored as serialized object. Therefore, it is better to use tools to handle this situation properly.

Alerting site URL in WordPress database with SQL query (do not use it in production — this will break your serialized data – see the next solution)

SET @OLDURL = "site.com";
SET @NEWURL = "site.dv";

SELECT * FROM wp_options WHERE option_value LIKE CONCAT('%',@OLDURL,'%');
SELECT * FROM wp_posts WHERE guid LIKE CONCAT('%',@OLDURL,'%');
SELECT * FROM wp_posts WHERE post_content LIKE CONCAT('%',@OLDURL,'%');
SELECT * FROM wp_postmeta WHERE meta_value LIKE CONCAT('%',@OLDURL,'%');

UPDATE wp_options SET option_value = replace(option_value, @OLDURL, @NEWURL);
-- UPDATE wp_posts SET guid = replace(guid, @OLDURL, @NEWURL);
-- UPDATE wp_posts SET post_content = replace(post_content, @OLDURL, @NEWURL);
-- UPDATE wp_postmeta SET meta_value = replace(meta_value, @OLDURL, @NEWURL);

If you are sure you want to go this route and break serialized data: go ahead and uncomment the UPDATE queries. And deal with broken widgets manually.

Ps. If you get error like:

#1267 – Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation ‘like’

Add COLLATE to your variable:

SET @OLDURL = "site.com" COLLATE utf8mb3_general_ci; 
SET @NEWURL = "site.dv" COLLATE utf8mb3_general_ci;
-- NOTE! use appropriate COLLATE value to your error

Serialized data break

Once the swap is completed, you might want to navigate to theme customizer and select logo image as it might not display as expected due to modification of serialized data in database.
Reselect images for footer, header / widgets will overwrite database serialized data with valid values. But hey there is a better solution! See the section below

Solution 1 – Edit database with free open software “Database Search and Replace” by interconnectit.com

You might think that how comes that no one is actually solving this issue? Well, it is not true.
There are free tools available which address search replace in serialized database. It is not pure SQL, but with help of PHP script the job can be done, and you can finely rest.

Project name: Database Search and Replace Script in PHP
Current version: 4.1.3
Interfaces: CLI and GUI

How to use it?

Download the script. Unzip to a folder.

1. Rename the folder to something custom.
2. Move the folder via FTP/SSH to the root folder of your WordPress website.
3. Access the https://yourwebsite.com/name_of_the_uploaded_dir and the below window will show:
4. Before you use it, make sure to make database backup!
5. Now fill in the form, providing database credentials and string you want to replace:

Solution 2 – WP-CLI

WP-CLI – command line tool to search and replace all database tables at once with a single command.

If you are familiar with the command line, you can run the following command to update all URLs in the database at once.

1. First, log into your server via SSH.
2. Next, navigate to your WordPress directory. Make sure to change username to your Shell user.
   [server]$ cd /pathtosite/example.com
3. In your site’s directory, you can use wp-cli to update the URLs using the following command.
   [server]$ wp search-replace http://example.com https://example.com/blog --dry-run

The --dry-run call at the end tests the address

If the changes make sense to you, then run the above command again without the dry-run call.

Solution 3 – phpMyAdmin + “Search & Replace” WordPress plugin.

1. Zip and ftp the files.
2. Unzip the files.
3. Download database
4. Create database in new host
5. Edit wp-config.php to establish connection to new database
6. Import SQL file to new database (on new host)
7. Edit database wp_options table (update siteurl and home options values)
8. Login to the site and admin panel and install “Search & Replace” plugin.
9. Replace old URL with new URL (without braking serialized data).
10. You are done (site should now work without any issues).

Ad 7. When changing site domain name, do not manually edit the database where the content is serialized. Instead, edit only data in wp_option table and if that will not be enough for your site, use specially design tools that will recalculate serialized string length and make required adjustments.

Ad 9. Next you need to update remaining urls using “search & replace” wp plugin.

Solution 4 – Duplicator WordPress Migration Plugin

Another way of migrating the site could be a plugin. If your site is up and running. You could use duplicator plugin. It has some limitation, but you could overcome the size limitation if… you move files through ftp (best as a compressed file – if you have ability to uncompress the file in your destination server – you probably could use hosting file manager to compress and decompress your server files) first and then move the database using duplicator plugin.

Step by step migration with duplicator plugin (no limitation size)

The following procedure was developed for use tightly restricted hosts that prevent a zip archive from getting created. Note that this operation is valid for both Duplicator and Duplicator Pro but is usually not needed in Pro due to its support of the “DupArchive” format. DupArchive was specifically designed to get around limitations imposed by hosts related to zip.

PART 1: Manually copy WordPress files – Zip and unzip files through hosting provided file manager if possible, then move compressed file manually through ftp client.

If you can not zip and unzip, just move files through ftp without compression (takes longer).

These steps show how to manually copy from source site to destination.cPanel (Faster)

1. Login to the source site’s cPanel, click “File Manager” and enter the site’s directory^*
2. Select all files, right click, select “Compress”, choose zip and set name to mysite.zip.
3. Download mysite.zip to your local computer
4. Upload mysite.zip to the new location^*
5. Extract mysite.zip and remove the mysite.zip file

FTP

1. Login to an FTP Client
2. Copy the files from the source site to your computer^*
3. Copy files from your computer up to the destination site^*

*Website directories will likely be public_html, or public_html/sitename. If unsure about where site files are located talk to your host support.

Copy the database with Duplicator plugin

After you have all the files uploaded correctly to your new location, you will then run all these steps to copy over the database.1. Create DB Only Package

1. Goto Duplicator ❯ Packages ❯ click “Create New” button
2. On Step 1 Setup ❯ Archive ❯ check “Archive Only the Database”
3. Build package and download package (installer.php and archive.zip/daf) files

2. Transfer Package to Target

1. Login to target location via FTP, cPanel or your host’s control panel tools.
2. Transfer installer and archive to target location
3. If a wp-config.php file exists in this location remove or rename it.
4. Open web browser and browse to http://yoursite.com/installer.php

3. Run Installer

1. Installer should have ‘Database Only Mode’ in upper right corner.
2. On Step 1 ❯ Click the next button
3. On Step 2 ❯ Enter database setup info ❯ test connection ❯ click nextFor the database setup enter in information on an empty database. You can easily create a new one or simply contact your hosting provider to have them set it up for you and have them give you the setup info. Some hosting providers have specific instructions for connecting to their databases, so be sure ask them what to use.
4. On Step 3 ❯ Click the next button
5. On Step 4 ❯ Login remove installer files
6. Only the database will be updated

After all steps are complete your site should be fully migrated. These two parts together allow you to move your WordPress site in a two stage process.

More on the Problem of editing database with in serialized data

If you think that, you could replace the site URL using sed a command on the Unix system, think twice. This will replace the string, but will not solve the issue with serialize data checksum. It could also create some encoding issues.

cd toSQLdir
sed -i 's/site.com/site.dv/g' *.sql

Notice that you might run in to problems when importing SQL which comes from mysqldump command.

It all happens because during the mysqldump some chars are added to serialized data {s:\" vs {s:" do not reflect the original string length and if they will not get read corectly when uploading the data the serialized checksum will also break. Values that come from php_serialize() are binary there fore it should not be stored in a MySQL database as plain text in VARCHAR, CHAR or TEXT field. It should be stored in binary format such as BLOB or VARBINARY.

The above might not be an issue for you if after import the additional chars will not be present in database. However what might cause the issue is the change of the original url length. So if you move your site to another domain with different length or you change http to https or domain name change / domain extension stored within serialized data, make sure to also update the string length. Example. If the site URL length was equal to (s:15) and you and a letter “s” to http which become https then also have to change the length of the string, so it becomes (s:16) (and so on).

Storing PHP serialized object in database is not forbidden, and it’s wildly used in PHP development, but it comes with several issues. When dumping a MySQL database, mysqldump escape serialized data, which might prevent it to load correctly when importing back to the database.

https://help.dreamhost.com/hc/en-us/articles/214580498-How-do-I-change-the-WordPress-Site-URL-
https://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic
https://wordpress.stackexchange.com/questions/7693/what-sql-query-to-do-a-simple-find-and-replace
https://interconnectit.com/search-and-replace-for-wordpress-databases/
https://code.tutsplus.com/tutorials/migrating-wordpress-across-hosts-servers-and-urls–wp-20104
https://interconnectit.com/news/2009/10/07/migrating-a-wordpresswpmubuddypress-website/