Moving WordPress From Your Localhost To A Live Web Server using WP Sync DB

1. Install and activate WP Sync DB.
2. In your WordPress local backend, go to Tools / Migrate DB.
3. At the replace section, enter the correct values for New URL and New file path.
4. Press export database and save the sql database to your desktop.
5. Now open your server’s cpanel and create a new database.
6. Open this database through PHPMyAdmin and import the database from step 4.
7. Use fileZilla to upload your wordpress site folder online.
8. Open your local wp-config.php, fix your database credentials and upload it online.
9. Open your online backend, go to Settings / Permalinks and press save. This will you will update your online .htaccess file.

Make sure you enter the correct fields in step 3. For example:

Database Collation “utf8mb4_unicode_520_ci” Fix

In that case, it seems that your local MySQL database is newer than your live one. To do the migration:

1. Use the WP Migrate DB plugin.
2. At the Advanced options, make sure that you have checked the Compatible with older versions of MySQL (pre-5.5) checkbox.
3. Now pull your converted database using the WP Synch DB plugin to convert the local MySQL database.
4. From now on, you can push or pull with no problem!

Troubleshooting

If for some reason, the process above do not work for your situation, you can try to do it without a plugin.

For example, in one of my sites, I had WPML installed, and on step 6, I was getting the error

COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'

I did it manually and it worked!

Let’s say we want to transfer a database from a live server to a local wamp server. Here are the steps:

1. Download the database from your site’s server.
2. On your local server, open PHPMyAdmin and import the database.
3. Open the SQL tab and run the following query.  Make sure that you swap in your old and new URLs, no trailing slashes. Also if necessary change the table prefix values where applicable (ie wp_ ).

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl') WHERE post_content LIKE ( '%http://www.oldurl%') AND post_content NOT LIKE ( 'a\:%');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');

Missing expression. (near “ON” at position 25)

Open MySQL > my.ini

and change this line

max_allowed_packet = 1024M

Index column size too large. The maximum column size is 767 bytes.

Open MySQL > my.ini and find the following line:

innodb-default-row-format=compact

Now change this line to the following:

innodb-default-row-format=dynamic

For your reference, here is the localhost htaccess

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule .* - [E=HTTP_AUTHORIZATION:%{HTTP:Authorization}]
RewriteBase /local_site/
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /local_site/index.php [L]
</IfModule>