Migrating a WordPress Site - FTP, PhpMyAdmin and SQL queries

In this tutorial we are going to explore how to migrate your WordPress site from one host to another, using FTP and PhpMyAdmin.

Overview

We will cover everything from exporting/importing a database in PhpMyAdmin to creating a new one, creating a new user, updating user privileges… And last but not least, I will show you how to update your old domain name URIs (e.g. http://old-domain.com) to your new domain name (e.g. http://new-domain.com) using SQL Queries in PhpMyAdmin.

SQL Query is a powerful tool that you can use within your MySQL database: you can create, drop or update one of your tables, select or delete a specific row… and much more. So as always, be sure to make a backup of your database before playing with this tool; if not, you could seriously screw it up. I always recommend to save your backups in at least two different places. That could be on your computer, external hard drive or in the cloud (Dropbox, Mozy Backup…).

For this tutorial, I will assume you have an Apache server and PhpMyAdmin installed. PhpMyAdmin is easier to use than it might seem at first glance so don t let this scare you. This step by step tutorial should hopefully be simple enough even for a first time user.

If you are a more experienced user, you may want to skip some sections and go straight to “Step 6: SQL Queries“.

Let s get started!

Step 1 - Download/Upload all your WordPress files Via FTP

First things first, you will need to download ALL your files from your old host and upload them to your new host.

• Connect via FTP or SFTP to your old host with your favorite FTP client. Mine is Filezilla but WinSCP is also a good alternative for Windows. To configure your FTP client, refer to your host documentation. If you can not find it, contact a technician. Hosting companies usually offer a 24/7 support and they should be able to provide an answer within a few hours or less.

  Note: SFTP stands for SSH File Transfer Protocol. Not all hosts allow you to transfer files via SFTP but if they do you should favor this protocol over FTP. That basically means that all your files will be encrypted during transfer (read “more secure”).

• Once connected, browse to your WordPress folder and download all your files locally onto your desktop or into a specific folder of your choice. This includes files from your themes folder and plugins folder. Make sure to save the .htaccess file at the root of your WordPress site. Neglecting to save your .htaccess file will result in broken permalinks. That is of course if you are using permalinks.
• Now that you have made a backup of your WordPress site, you will need to upload ALL your WordPress files to the new host. Configure your FTP client to connect to it.
• Browse to your new site directory and transfer all the files from your machine to the server.

Step 2 - Export your database

Connect to PhpMyAdmin on your old host. Most hosting companies have Cpanel or a similar control panel. On the main screen, you should see a bunch of icons. Scroll down to the Databases section and log into PhpMyAdmin.

• Once you have reached PhpMyAdmin, you will see all your databases on the right hand side of the panel. Select the database that correspond to your WordPress install. It will load all your tables on the next screen. Click the export tab on the top.
  
• On the next screen, select all the tables.
  
• Scroll to the bottom and check “Save as file”. Leave the default valued (_DB_) or give it a custom name. If you have a large database you might want to zip it. Click Go. It will ask you where you want to save the file. Select a folder and click save.
  

Step 3 - Create a new database

If you have Cpanel, you can directly create a new database right from the control panel (Cpanel -> Databases -> MySQL Databases -> Create Database) but since we are talking about PhpMyAdmin in this tutorial, I am going to show you how to do so in PhpMyAdmin.

• Connect to PhpMyAdmin on your new host. On the home page, you should see a field to create a new database. Enter the name of your database and click “Create”.

  

  If everything has worked correctly, you should see this message:

  

Step 4 - Create new user in PhpMyAdmin

Once you created a new database, the next step is to create a user to administrate this database.

• Click on the privileges tab on top.

  

• It will take you to this page:
  
• Click on “Add a new user”. On the next page, choose your username and then select “localhost” in the Host section. For password, you can either enter your own or generate one.
• Now Scroll down to Global privileges section. Leave it blank at the moment as shown in the screenshot below. Click on the “Go” button.
  
• If everything worked fine, you should see this dialogue:
  
• On the same page, scroll down to the “Database-specific privileges” section and select the database you previously created.
  
• It will take you to this screen. I usually check all the boxes and leave the admin section unchecked. Click “Go” when you are done.
  
• You should see another message:
  

Step 5 - Import your database

• Still in PhpMyAdmin, click the import tab (make sure your new database is selected on the right).
  
• On the next page, select the database you exported in step 2. Click “Go”.
  
• You should see this message if everything worked fine.
  

Step 6 - SQL Queries

Now that we have successfully imported the database onto the new server, we need to replace the old website URI (e.g. ‘http://www.old-domain.com ) with the new URI (e.g. ‘http://www.new-domain.com ).

(Note: This step is actually optional. In most cases, the domain name will remain the same. If so, you can just skip this section.)

In a default WordPress install, you only need to replace the URI in 3 tables: wp_posts, wp_postmeta and wp_options.

• To run a SQL Query, click on the SQL tab on top
  
• This will take you to this screen.
  
• Now enter the following queries one by one and click go for each. Don t forget to change http://www.old-domain.com and http://www.new-domain.com by your old domain name and new one respectively. Also, if you changed the default table prefix (e.g. “wp_”) to something else, make sure to update it in the queries below.
  ^?View Code PHP

  1 2 3 4

  UPDATE wp_posts SET guid = replace(guid, 'http://www.old-domain.com','http://www.new-domain.com'); UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com'); UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://www.old-domain.com', 'http://www.new-domain.com'); UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

  You probably noticed that the last query is a little bit different from the others. What we do here is to replace the old domain name with the new one only for “home” and “siteurl” in the option_name column. Here s how it looks in PhpMyAdmin when you browse the wp_options table:

  

• If your query was successfully executed you should see a dialog like the one below with the number of raw affected.
  

(Note: Some plugins need the site URI. You might need to update that as well. The easiest way to check if another table contain the old domain name is to do a search in PhpMyAdmin (your database -> Search tab -> Select all the tables). It will return all the tables that contain the old site URI and you can then change accordingly as explained above.)

Step 7 - Edit wp-config.php

• Open and edit wp-config.php in your favorite web/text editor (Dreamweaver, Aptana, Notepad…).

  Replace the old settings with your new database name, username and password you created in steps 3 and 4.

  ^?View Code PHP

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  <?php /** The name of the database for WordPress */ define('DB_NAME', 'new-site');   /** MySQL database username */ define('DB_USER', 'yourusernamehere');   /** MySQL database password */ define('DB_PASSWORD', 'yourpasswordhere');   /** MySQL hostname */ define('DB_HOST', 'localhost');   /** Database Charset to use in creating database tables. */ define('DB_CHARSET', 'utf8');   /** The Database Collate type. Don't change this if in doubt. */ define('DB_COLLATE', '');

• Save the document and upload it to your server via FTP.

Conclusion

That s it! Load the site in your browser and if everything went well, you should see it as it was on the old host. I hope you enjoyed this tutorial. If you have any question, leave a comment below and I will do my best to answer you.

If you want to learn more about SQL query, read this excellent article from Onextrapixel.com: 13 Useful WordPress SQL Queries You Wish You Knew Earlier.

Do you know any other SQL query tricks? Share them with us!

The Author

Want to become a guest author on this blog?