How to move a WordPress site to a new site

November 19th, 2014

The General Situation

You put a WordPress site, perhaps for a client, then you have to deploy it. But the site where you created it may be on a different host (event a different host provider) and it has a different domain name in its URL. I’ve had to do this a couple times, and I’ve had to work some GOTCHA’s in the process that I thought I would like to share.

I have looked on the web for tutorials to help about doing this. But they are all missing one critical step: updating the database to reflect your new site’s URLs (see 3 below). It’s not as simple as changing the site URL in the WP Admin panel.

A Specific Example

In some cases you have an old website, say www.mydomain.com and you want to replace it with a new WP site and you want minimal downtime.What I like to do is set up a parallel site on a sub-domain. So if you own “mydomain.com” then create a subdomain like “test.mydomain.com”, install WP on it and get all your content up and running on it. You are ready to cut-over, you need to need to repoint “www.mydomain.com” to the directory holding the new WP site. But before you do that, you need to update data in the WP DB to ensure it works OK.

If you don’t fix the DB, at first you won’t notice anything wrong. That is because your test site is still running. URLs to things like images that are embedded in your posts will be pointing to the test site. Menu links may take you to the test site. The rude awakening comes when you shutdown the test site and all of a sudden [GOTCHA] you have broken images, broken links and other problem on your new site that seemed to be working just great. PS. this applies if you used to be “http” but are now installing a certificate and are changing to “https

If you are already in this situation where you deployed on your production site and have these problem, my suggestion is:

  • Follow Step 2 below to export your data
  • Follow Step 3 below to fix the URLs in the export file
  • Drop all WP tables in your DB clear out the data. Or, if that seems risky, create a fresh DB instance to import into.
  • Follow Step 4 to import the corrected export file back into the DB

If you are planning to move from a test site to a production site, follow all the steps below.

1. Move the files

On the test site, get all your files. If you can get a shell, get in there and zip or tar-gz the directory into one archive file. Sample commands:

  • (Create a .zip file) zip -r mysite.zip mysite/
  • (Create a .tar.gz file) tar -cvzf mysite.tar.gz mysite/

That makes it easier to download and upload to the new site. Some host providers don’t give you a shell, but give you a lame web interface to the file structure. But some all you to unpack a .zip or .tar.gz file once you upload it.

[GOTCHA]  If you use the commands above, then your archive file has all the files under the directory “mysite”. You want that if you can get onto your new host in the parent directory and extract all the stuff under “mysite”. But with some providers, you can’t get into the parent directory. You create the “mysite” directory via their interface then can upload files into it. To avoid extracting files as “mysite/mysite” y0u should create your archive without that top level dir:

  • cd mysite/
  • zip -r mysite.zip .
  •  tar -cvzf mysite.tar.gz .

[GOTCHA]  If you stuck an Apache .htaccess file at the top of your directory structure to configure something for the site, you probably don’t want that in your zip or tarball since it won’t apply to the new site. Move it to another directory, make the zip/tarball, then move it back.

[GOTCHA] on archive files: I’ve run into an issue where every file name in an archive file got down-cased. I’m not sure how this happens. Maybe its if your old site it on Windows. Anyway, most WP files are lower case already but some plugin files may not be lowercase. When I had this problem, I would set everything up then navigate to the site in the browser and I would see PHP errors about files not found in a PHP “require” or “include” statement. Usually it was a plugin including one of its files like include_once(‘SomeFile.php’); but the problem was that “SomeFile.php” was actually “somefile.php” on the filesystem.

If you don’t have shell access to create an archive file, you can use either FTP or SCP to get the files. If FTP, you can just type in the FTP URL into your Windows Explorer window (not IE, the regular window for looking at files on your computer). Probably works on Mac the same way. And use forward slashes. Type in something like: “ftp://mysite.com” and you will get prompted to login. You should be able to set up an FTP account via your host provider console. If the host provider allows SSH/SCP, I recommend WinSCP (don’t know what the Mac equivalent is). Watch out for the “fake” download button on that page that is an ad for something else.

If you can get a Windows Explorer window showing the FTP contents of the old and new hosts, you should be able to drag-and-drop the contents from one window to the other. This is slower than if you create an archive file. If you have trouble, try drag-and-drop for old host to your computer, then from your computer to the new host.

[GOTCHA] on the file system: make sure your WordPress installation directory and files are writable by whatever process is running WP. Otherwise non-default permalinks will not work , you can not upload image files when editing a post and you cannot edit theme or plugin files via WP Admin. When I ran into this problem, I found that the process running WordPress was in the same Unix group, so running chmod -R g+w <wordpress install dir> fixed the problem.

2. Export the DB to a File

I assume you can set up a MySQL DB on the new host and get to a phpMyAdmin panel for both the old and new hosts. We need to export the data from the old host and import into the new host.

On the old host phpMyAdmin, go to the Export tab at the top.

Follow the official steps for Backing Up Your Database, I have summarized them here.

In phpMyAdmin:

  • Click on the Database name link
  • Click on the Export tab
  • Click “Select All” Make sure you have all tables selected. (If you don’t see all the tables, click on the Database name link)
  • Check SQL
  • Under “Structure” check each:
    • Add DROP TABLE
    • Add IF NOT EXISTS
    • Add AUTO_INCREMENT values
    • Enclose table and field names with backquotes
  • I think you can leave SQL export compatibility = NONE since we are going from MySQL to MySQL
  • Under Data:
    • Complete inserts
    • Use hexadecimal for binary fields
  • Export type: INSERT
  • Check Save as file
  • Compression: None
  • Go

Please comment if you think I’ve missed something here. That should download some <dbname>.sql file.

3. Fix the URLs in the DB Export File

[GOTCHA] If you import the data file into the new database, you will have the old URL in various columns in the DB. This means issues like your menu links and src URLs for IMG tags point to the old server instead of the new one. You can find these URLs in the DB in wp_options.option_value, wp_post.post_content, wp_postmeta.meta_value, wp_comments.comment_content, wp_comments.comment_author_url, wp_links.link_url. But then there are some in tables created and used by plugins that may contain them as well. At first I tried to run queries to update the data but I always seemed to miss something. Then I realized, all I needed to do was do a search-replace in the DB export file before I import into the new DB. That seems to work like a charm.

  • Open the export SQL file in a text editor
  • Replace all instances of your old domain/URL (like “test.mydomain.com”) with the new one (like “www.mydomain.com”)
  • Save & Quit

4. Import the DB File

In the new host phpMyAdmin, go to the Import tab at the top.

  • Choose File: your corrected exported file
  • Check Allow the interruption of an import in case the script detects it is close to the PHP timeout limit. This might be good way to import large files, however it can break transactions
  • Format of imported file: SQL
  • check: Do not use AUTO_INCREMENT for zero values (I’m not sure on this, but is the default)
  • Go
[GOTCHA] On some systems I had trouble importing the file. Sometimes the export file was too big and it timed-out before it finished. Other times it would seem to work, but actually did not import all the post data and I found there was a lot of stuff missing. When I tried using mysql at the command line, it told me the connection closed during the import. This was a real headache. I finally figured out that the MySQL configuration had to be changed to increase its “max_allowed_packet” setting. Assuming your login has permission to edit the MySQL my.cnf file, I added these line (with greatly increased values over the defaults) under the [mysqld] heading:
1
2
interactive_timeout=100000
max_allowed_packet=128M

Then my imports worked.

Aside: Alternative To Export/Import DB Data

This is an aside. If you are using the same database but just changing the domain name in links, you can take the approach of trying to use SQL to update data in the database in-place. This can also be useful if you can’t get the import to work right and you want to use the same database. You can use the MySQL “REPLACE” function to replace old links with new ones. The hard part is that you need a SQL statement for each individual database column that might contain the URLs you want to fix. This includes tables created by plugins. There is a chance you might miss some.
Example SQL replace statements for core WP tables are:
1
2
3
4
5
6
7
8
9
10
11
UPDATE wp_calendar SET event_link = REPLACE(event_link, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_calendar SET event_desc = REPLACE(event_desc, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_commentmeta SET meta_value = REPLACE(meta_value, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_links SET link_url = REPLACE(link_url, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_options SET option_value = REPLACE(option_value, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_usermeta SET meta_value = REPLACE(meta_value, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_users SET user_url = REPLACE(user_url, 'test.mydomain.com', 'www.mydomain.com');
Note: do NOT change wp_posts.guid. See note on GUID.
Example SQL replace statements for Contact Form 7 Plugin tables are:
1
2
3
UPDATE wp_contact_form_7 SET form = REPLACE(form, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_contact_form_7 SET messages = REPLACE(messages, 'test.mydomain.com', 'www.mydomain.com');
UPDATE wp_contact_form_7 SET additional_settings = REPLACE(additional_settings, 'test.mydomain.com', 'www.mydomain.com');

5. Set the DB Connection info for WordPress

In the install directory of the new host, edit the wp-config.php file (or copy it to your local machine, edit it, then copy it back when done). Fix these values to point to the MySQL on the new host:

/** MySQL database username */
define(‘DB_USER’, ‘xxxxxxx‘);

/** MySQL database password */
define(‘DB_PASSWORD’, ‘xxxxxxx‘);

/** MySQL hostname */
define(‘DB_HOST’, ‘xxxxxxx‘);


Comments are closed.  Go To Support Forum