IMPORTANT NOTE 2021 – THIS POST IS 7 YEARS OLD POST AND MANY OF THE STEPS SUGGESTED DO NOT WORK WITH NEW VERSIONS OF WORDPRESS FOR INSTANCE:

GD PRESS TOOLS is not longer free or not available
site_url and home_url variables has been REMOVED

ONLY FOLLOW THESE STEPS IF YOU HAVE WORDPRESS VERSION 4.5.24 OR OLDER – WHICH YOU SHOULDN’T – YOU’VE BEEN WARNED.

WHAT MIGHT STILL WORKS IS REPLACING TEXT WITH MYSQL COMMANDS WHICH IS PART OF MYSQL FUNCTIONS AND HAVEN’T BEEN DEPRECATED

Hello Web Developers…

This is a extensive tutorial for those of you who develop with WordPress. WordPress is an amazing platform to build on. Either for just a section of your website or as whole CMS (Content Management System), WordPress has become the leader platform not only for web professionals, but also for adventurers, that’s right… WordPress is getting Web Developers out of job.

Do not worry, web is not only about website design… there’s a whole lot for web developers in the Cloud than that you can imagine. You only have to dig in a little deeper.

Now, if you are a WordPress developer, you might bump into a situation where you have to move it from one domain to another, even changing it to a sub folder… this also applies taking over an already established WordPress project, to create and install it into you localhost playing ground or Sandbox.

It is very simple to do, and you might be surprise how simple it is. To accomplish this task you can do it the easy way or the hard way. Let’s do it the hard way first so you can appreciate the easy way later. This means creating a local copy of the database and WordPress itself.

If you only have to change the location to a different directory under the same URL, just skip to the Easy Way (Step 5).

Importing and Modifying data storage tables on MySQL Instance.

REQUIREMENTS

MySQL already installed (I suggest to install same version as your hosting)
Apache or IIS Servers installed and running
PHP already installed (I suggest also the same version as your hosting)
FTP Access to WordPress current instance.

Before we get started, for this, you will require a couple of MySQL management tools as well.

To execute the steps I will talk about in a couple of paragraphs, you need to have access to administrative tools. You can either execute the statements I will mention on the shell (Terminal, PowerShell or Command Prompt), and also through PHPMyAdmin, if it is available to you. Also you can use SQL Management Applications like MySQL Workbench (my first option). Some hosting services allows you to connect through a shell using tools like PuTTy, a Windows Environment Unix-Base shell to execute shell commands through SSH (Secure SHell). For preference, I use BlueHost for hosting.

To connect to your Remote Host Shell In Linux or Mac OS X you can follow these tutorials: (I haven’t look at these tutorials but I assumed they are OK, for Linux and for Mac OS X) Also refer to your hosting documentation.

Of course, if you don’t have access to the database you cannot do anything with it, so I am assuming that you have administrative access to it. If you do not, you will need to recreate an SQL Structure/Data with a third-party plugin as GD Press Tools, my personal favorite or other similar plugins. GD Press Tools in no longer available as free version on the WordPress Plugins Repository, so if you are looking for free options just search ‘database backup’ under ‘Add New’ on the Plugins Dashboard.

To create a back up with GD Press Tools it is easy, first go to the Database Section under the GD Press Tools tab on your admin dashboard:

Unchecked the option ‘Save database backup as GZIP archive’ because is buggy and corrupt the file, most of the time.

Click on Backup button, when the process is finished you can download the backup file under the same section where you created it, or by the URL showed at the top of the Settings section, ‘/wp-content/gdbackup’ by default. See image above.

Note: This is a good idea also before you make a framework update, from one version to another or backing up your system for DR (Disaster Recovery). Never make updates without backing up your system, consider this before installing or updating plugins as well.

Step 1. Setting up your Sandbox (Localhost Playground):

If you were able to create the SQL back up file, loading it to your local MySQL Instance is quite simple with MySQL Workbench. You can also load it with the shell as well. But I will concentrating using a more user friendly tool, of course, the shell it is more trusted. Actually, Workbench uses the MySQL shell on the background, just making it easier for you.

MySQL Workbench is available for Windows, Linux, and Mac OS X, so you can really manage you MySQL in any platform the same way. I will not go through the process of installing Workbench, but it is straight forward. Just search for ‘Installing MySQL Workbench‘.

When you open Workbench for the first time, you will notice that you MySQL local instance will show up, so there is nothing to configure, assuming MySQL is already installed in your system. If your hosting allows you to connect to your MySQL host remotely, you can skip the backup process and the next instructions, because you can accomplish this more easily Creating a new Connection on Workbench, and request your hosting for the credential information, this might requires an SSH private key. But for now let’s keep setting up our local instance.

Click on this instance and enter the credentials you set up during installation (User/Password), you can choose to save the password if you want, but I do not recommend. I do recommend using the root user when using Workbench.

When you up and running, create a new schema with the same name as the schema of your previous WordPress installation, you can RENAME the schema later, if you want to. I would copy the wp-config.php into the new URL version.

Then you will dump all the data using the backup file you just created. On your left menu bar, click on Data Import/Restore tab.

Then checked ‘Import from Self-Contained File’, browse for the back up you just downloaded, select schema to dump the information, also in the selection box at the bottom of the screen, select ‘Dump Structure and Data’ and Start Import.

If you have the same MySQL version installed in your local environment, you should not have any problem. If you decided to update to a later version than your hosting, you might encounter DATETIME default values issues. In that case, that it is another tutorial to cover, but not here. If you need help with that now, just click here.

Step 2. Setting Up an User

You have to set an user for WordPress as well, never use your root user for WordPress, unless you do not mind an stranger snooping around your data.

On the left menu click on ‘User and Privileges’, once clicked, click on the button at the bottom center of the screen that’s labeled ‘Add User’ and you will create an user with the same information you have in the current installation of WordPress wp-config file:

For the moment, make sure you Limit to Hosts ‘localhost’, do not user 127.0.0.1 or % wildcard, for any reason, this does not work, and it is not safe either. Set the Authentication Type to Standard, which is what you need for host based authentication.

When done, press Apply, and when it is processed and registered, go to ‘Schema Privileges’ Tab. Where you will select the schema you created for WP and grant all privileges to that schema only.

Click OK, and the check all privileges check boxes. Make sure to check the ‘GRANT OPTION’ check box as well or WP will not work.

Once this is done… your are ready to install WordPress.

Step 3. Installing WordPress on your Local Environment

Now you need to install WordPress locally by downloading the files to your server with and FTP Client (Filezilla or WinSCP). If you are on Windows, I recommend the later.

I am assuming you know how to use the FTP Client and how to download the files, if not check this tutorial or using FileZilla. Once these is done and if you kept the credentials the same as it was in the previous installation, you do not need to do anything else. But if you are going to use Microsoft IIS (Internet Information Services) Server on Windows as the local server you need to grant WordPress writing privileges only to it’s directories, most importantly the /wp-content/uploads. If you are in Apache you can skip to Step 3.

To grant updating and writing authorization to WordPress from the Dashboard, so you can be able to install plugins and make updates, you have to grant this privileges as follow. Locate the WordPress directory and right click for the option menu and select ‘Properties’.

On the Security Tab, click on Edit and add two user, one at a time, IUSR and IIS_IUSRS, and set permissions to Full Control if available or select Modify, Write and Read & Execute:

Hit Apply or OK if you are done.

Warning: This will expose your IIS Server as it expose any Web Server to WordPress, which means, you need to understand web security threat issues. If you think you are safe because you use Linux or Mac OS X, you are fried, read this… No one is exempt of cyber attacks, so take all measures possible to avoid being exposed or attacked.

UPDATE: If you still have some problems trying to update or install plugins with a Fatal Error saying it couldn’t find an SSL certificate of the sort, download the Mozilla certificate here (right click and save as), save it anywhere you want. If you have your PHP Manager for IIS… would look something like this

Double click it, and look for the php.ini file link:

If you don’t, create a phpinfo.php file and save it somewhere inside your local website with the following code:

<?php
    echo phpinfo();
?>

Navigate to the php file you just created in the browser and check the location of the php.ini file IIS is reading to render PHP.

The location of your php.ini file might be in a different location, once you spot it, look for the file and run your favorite text editor as administrator, and open the php.ini file.

Then look for the curl.cainfo statement, uncomment it and add the URL where you saved the cacert.pem for example:

Remember to enter the location where you saved your file, the image above it is just an example.

Save it and restart IIS, normally changes made on the setting file is automatically populated when using PHP Manager, but just in case reset, I prefer to do it on PowerShell, it’s as simple as typing iisreset:

Remember to run powerShell as administrator. Now you should be able to get rid of the pesky Fatal Error.

Now that you have your data storage synchronized and the new instance of WordPress installed in your local folder, let’s update the domain the hard way… But if you prefer skip to the easy way on Step 5.

Step 4: Understanding and Executing String Replacement on SQL. Updating the URL the Hard way.

As in many developing languages, SQL let you replace a string with another string by using the REPLACE() method or function. How it works is as follow:

UPDATE `table` SET `column_to_be_affected` = REPLACE(`column_to_be_affected` , 'http://current_url', 'http://newurl') WHERE `column_to_be_affected` LIKE '%http://current_url%';

This statement has to be executed on the OPTIONS database, by default wp_options. Also, you might want to execute it on other tables if you have links pointing to the old url, for example: wp_comments, wp_posts, wp_links. But let me warned you, make sure you replace the url that start with ‘http://’ prefix, if not you will be replacing other things you might not expect, like emails. So be careful.

Once you have executed this successfully on your localhost is time to do the same in your hosting. (Again, you need access to PHPMyAdmin or the ability to import data into your production server to do this. If not, the hosting administrator have to do it for you.)

Step 5: Updating the URL the easy way.

In your favorite text editor (Notepad++, Brackets, Visual Studio Code) or IDE (Netbeans or Visual Studio Community), my favorites being Notepad++, Brackets and Netbeans*, although Visual Studio Code has a neat integration with GitHub versioning.

Open your theme function file, normally ‘functions.php’ and enter the following statements before every other statement:
update_option('siteurl', 'http://domain_location'); update_option('home', 'http://domain_location');

Obviously, replace http://domain_location with your new DNS or URL location. Save the changes and run the WordPress web application once. That’s it, just make sure the home button link and other links reflect the new location. You can to this by logging in to the administrative area, if you are taken to the old domain, changes are not reflected. Go back to the local instance or the new domain, refresh the page with function key five (F5). If still nothing happens, clear your browser cache.

If still this does not do it, you might need to go the hard way, try by adding the following line into your wp-config.php file, located in the root directory of WordPress:
define('WP_HOME','http://domain_location'); define('WP_SITEURL', 'http://domain_location');

Same as with the function file, you will replace the http://domain_location with your actual location, if is your localhost would be “http://localhost” or “127.0.0.1”, assuming you left everything set as default.

If none of these methods work, unfortunately you will have to go the hard way. Sorry.

Conclusion

Well, that’s all. What make this tutorial longer was moving your WordPress to a new domain or creating a testing environment. Of course, I am not dealing with specific challenges that you can find installing WordPress under IIS or Xampp, neither installing MySQL, Apache/IIS and PHP on your test environment. There are plenty tutorials around to accomplish this tasks.

But it is simple to update the URL path if it is just changing the directory under the same domain.

Happy Coding.