thb_phpmyadmin_sql_queries

June 25, 2010

PHP, Tutorials, Wordpress

Migrating a WordPress Site – FTP, PhpMyAdmin and SQL queries

Article written by Jeeremie

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.
    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.

    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

Article written by Jeeremie:

Hi, My Name is Jeremie Tisseau. I am a French UI/UX Designer, Event Organizer and Web Entrepreneur based in Bangkok, Thailand, since January 2009. I design beautiful and functional web and mobile apps for early stage startups.

Want to become a guest author on this blog?

36 Comments

  1. Shane says:

    25 Jun, 2010

    Would it not be easier to just install a fresh copy and use the Export & Import functions built into WordPress. The only thing you would leave behind is attachments and your theme – both of which are easily transferable without messing around in phpMyAdmin.

    • Jeeremie says:

      26 Jun, 2010

      Well, first of all, the whole point of this tutorial was to see how to use PhpMyadmin and SQL queries to export/import and modify a database.

      The export & Import functions are great tools but it is important to note that when you import a XML file, category ID’s will change which can be annoying if you use a custom navigation in your theme or need to get the category by id (e.g. get_cat_id(’23′) ) somewhere else in your theme. Besides, it does not import all the admin and plugin settings (one good example is if you changed the permalink structure – that setting will not be imported to your new site)… By following this tutorial, you will get an exact copy of your website on your new host with the exact same settings, ID’s, etc.

  2. Eusebiu Blindu says:

    27 Jul, 2010

    How about file permissions. When you download/upload? Can you keep them?

    • Jeeremie says:

      27 Jul, 2010

      I can’t answer with certitude but I think this is specific to each server. I recently moved my files from one server to another and in that case permissions were resetted to the default which were 775 I think.

  3. Arnaud says:

    27 Aug, 2010

    Sawatdee khrap!
    Thank you so much for this post, it worked perfectly to move my WordPress website.
    Je suis Francais aussi, base a San Diego et j’ai comme projet de m’installer a Bangkok avec ma copine Thai. Si tu as des conseils je suis preneur :)

  4. Ries says:

    28 Aug, 2010

    thanks J, i need this tutorial and this is very help me…

  5. Zeth says:

    5 Nov, 2010

    Hey.

    Wow this is technical! Awesome! I’ve looked several places for a connection between MySQL and PhpMyAdmin. I can’t seem to figure out what’s the database and what’s ‘inside’ the database. I read this blog through, but I think I need to study it a couple of times to catch it all. Loving it! A blog full of information!

    • Jeeremie says:

      8 Nov, 2010

      You are welcome. Glad I can help.

  6. Netinial says:

    9 Nov, 2010

    Thank you very much. It saved hours of me.

  7. Matt Merkel says:

    3 Jan, 2011

    Jeeremie – I followed your tutorial to the letter and still get the following message:

    “Error establishing a database connection”

    What do I do now?

    • CHH says:

      21 Feb, 2012

      Same thing here. Please help

      • CHH says:

        21 Feb, 2012

        Sorry my fault duh needed to verify sql db password. Works perfectly thanks.

  8. Zeth says:

    4 Feb, 2011

    I actually got that too. I talked to my hosting company, since there’s a lot of setup that can screw things up. Try talking to them about it. I’m not big on this subject, so don’t go ‘all in’ on this advice :D

  9. Matthew Reyez says:

    13 Feb, 2011

    What about the permission? do I need to manually setup all the files permission again?

  10. Kreek says:

    8 Mar, 2011

    Thanks man, I use this as a reference everytime I have to move a wordpress installation locally to the www.

    Best guide I’ve yet to find.
    +1 kudo to you, from me!

  11. Liz says:

    1 Apr, 2011

    Do i need to install worpdress in the new server before i create a new database? doesn’t installing wordpress create a database already?
    sorry not sure about this

    • Jeeremie says:

      1 Apr, 2011

      You need to create a new database first on your server and then WordPress will generate the database tables when you install it.

  12. Liz says:

    13 Apr, 2011

    Hi again, so far I have gone through all the steps. But when i log in to the WP admin panel, I have to use the old password from the previous host to get in. I have run searches for the old domain name to be sure it is all replaced with the new domain name and all is good. but I can’t get in to the correct admin login area. does this make any sense? Any ideas what I missed?

  13. Abacoa says:

    17 Apr, 2011

    If I transfer and zip the entire public_html folder and transfer it to my new host, along with a back-up of the database will that include everything I need to make my site look the same on a new server?

  14. Online Strategies says:

    25 Jun, 2011

    In godaddy there is restore db feature available which is very useful in restoring the wordpress db.

  15. mmmh says:

    1 Jul, 2011

    speak german? (we leaving on the earth….)

    scusi (I) translate………………..

    dein theme für wordpress “OneRoom” ist fantastisch,
    aber warum so egoistisch, das du nur “hardcore” übersetzungen erlaubst. keine variablien anbieteset?
    sei nicht so schüchtern.

  16. Shane says:

    28 Aug, 2011

    Great post, perfect for friends that needed my help with this.

  17. sağlık says:

    14 Oct, 2011

    nice theme .. gonna use this

  18. WConklin says:

    18 Oct, 2011

    Thank you so much. I have been looking for a tutorial like this for over an hour and this was more than helpful. Cheers

  19. Eddie says:

    27 Oct, 2011

    What you didn’t talk about is how any plugins are effected. One publication plugin that we had installed is a big problem when the domain name changes.

    • Jeeremie says:

      14 Nov, 2011

      Yes, that’s true. Some plugins might contain the website url and it will have to be changed too. But I cannot cover every single plugins. if you are not sure, make a search for the url in your database in PhpMyAdmin and see which tables contain it. Then you can change the SQL query accordingly.

      • Julien says:

        23 Dec, 2011

        Would the following constants force plugins to use the new domain name?

        define(‘WP_SITEURL’, ‘http://www.example.com’);
        define(‘WP_HOME’, ‘http://www.example.com’);

        (to add in wp-config.php)

  20. Nawa says:

    7 Nov, 2011

    Good day, thanks for the good info. I’m just starting with the phpmyadmin. I created my first database. Two questions. How do I create a report? Second how can I let someone in another city use the database to register the customers?
    Thanks in advance

  21. Xander says:

    21 Jan, 2012

    Hi there,

    This is too much. Just do this:

    1. Backup database, save to file
    2. Create new database, upload saved file
    3. Copy files through FTP
    4. In wp-config update MySQL information
    5. Also in wp-config.php, add or update these lines:

    // in wp-config
    define(‘WP_SITEURL’, ‘http://www.site.nl’);
    define(‘WP_HOME’, ‘http://www.site.nl’);

    6. Edit your theme’s functions.php, add this:

    // in functions.php at the top. Remove after first run.
    update_option(‘siteurl’,'http://www.site.nl’);
    update_option(‘home’,'http://www.site.nl’);

    7. Run the site, it should work now. Don’t forget to remove the stuff you added in step 6

  22. Andy says:

    24 Jan, 2012

    Hey Amazing guide! really taught me alot about using the php admin tools, When you say upload your old wordpress files to the new site, did this mean you had to install a empty wordpress instance onto the new site and overwrite it with the old files

  23. Si says:

    30 Jan, 2012

    Hi. All worked great Thanks, great tutorial.

    The only issue I have is that I cannot login using my old login info. Says “You do not have sufficient permissions to access this page.” which is strange!

    Checked the MYSQL database for user info and the user name is right, I cannot check the password as it’s encrypted.

    Thanks in advance. Si

  24. ohnoezitasploded says:

    1 Feb, 2012

    Hey, thanks for this very helpful post.

    I followed the directions and tried it two or three different ways, but I can’t get my data to appear on the new site. It takes me to /wp-admin/install.php and gives me the installation form. Any idea why that might be?

  25. Matt says:

    3 Feb, 2012

    Hi this is great – however, when I get to the end od it all I encounter a problem (after the wp-install.php screen) that says “Table ‘xxxx_xxx.Truewp_options’ doesn’t exist”. I have done this twice now, once on a subdirectory as the ‘new domain’ (www.domainname.com/sub) and once on a subdomain (www.subdomain.domainname) – both have the same outcomes.

    Is it something to do with the wp-config file? I have used ‘localhost’ as the DB_HOST – this has worked fine for subdomains where WordPress was installed from scratch (using Fantastico)….

    Can you pont me in the right direction? Very annoying to get so far then get this!!

    Thanks
    Matt

  26. Tim says:

    19 Feb, 2012

    Hi, I just wanted to say thanks for the step by step guide you have put together here. I have been trying to make these changes for about 8 hours, with no success, and after I found your page, I have had success in about 20 minutes. So thanks again.

  27. David Apple says:

    20 Mar, 2012

    Very helpful, thank you :)

  28. Pantho Bihosh says:

    24 Jun, 2012

    Awesome! Really helpful. But is there any option of upload database via FTP? Please let me know. thank you.

Leave a Reply

Sorry, comments are closed