SureshJoshi.com ▼

WordPress to WAMP Tutorial - Databases


2013-04-20

In my previous post, I talked about migrating your server files from WordPress to WAMP via FTP. In this post, I will discuss the slightly trickier act of replicating your database.

The initial setup is identical to my earlier post about file transfer, so I will duplicate that information here:

What you’ll need to go from WordPress to WAMP

  • AMP Solution Stack: I’ll be using WAMP (as this post’s title indicates), but you could also try any set of tools that will let you host websites locally: XAMPPLAMPMAMP, etc… People seem to have had more success with XAMPP, but I have zero familiarity with it.
  • FTP Client: I use Expression Web (part of Expression Studio) for most web development, as it has some nifty features. It also happens to have a built-in FTP client (with synchronization features), so I will use that. Any FTP client will do though. Some I’ve used in the past are: FileZillaWinSCP, and Windows 7 built-in ftp.exe Update: Due to Microsoft basically giving up on parts of Expression Studio, I’ve moved most of my FTP work to WinSCP.
  • PHP Editor: As I said, I use Expression Web for all my web development. But, something as simple as Notepad will work for this part (although, I recommend Notepad++, with its built-in syntax highlighting). **
  • Usernames and passwords: This might seem obvious, but I didn’t know all my WordPress, FTP, and phpMyAdmin usernames and passwords off the top of my head - have them handy.

I’m going to start by assuming you’ve already installed WAMP (there is lots of information about this online and it’s pretty straightforward anyways). In my case, I installed it to c: and will use that for the rest of this tutorial.

BACKUP!!!

The first thing you will need to do is backup your database. For this, you can simply follow the WordPress instructions located here.

I typically follow the phpMyAdmin route, as that’s what I also use in WAMP. To get to phpMyAdmin in Dreamhost (my hosting provider), go to your cPanel, click on “MySQL Databases” which will get you to a page containing the following:

Simply click on “phpMyAdmin” and you’re ready to follow the WordPress instructions.

Alternatively, instead of selecting my database and then exporting the tables, I like to go with the re-create from scratch option… As shown in the image below, I just do a full database export, with the option to drop the database if it exists. This will probably delete any changes you’ve made locally, but I’m doing one-way synchronization (as I do all my posting and post editing online).

PhpMyAdmin

Note: Don’t select the Add DROP DATABASE if this is your first import (because the database doesn’t exist locally, so there is nothing to drop).

Once you’ve backed up your online WordPress database, it’s time to make it work locally. At this point, you should have all files associated with your WordPress install, as well as a .sql file containing your database information.

Now you’re ready to connect this database backup with your local instance of WordPress running on a WAMP server.

Steps:

  1. Start WAMP server running and go to the main index page.
  2. Go to PhpMyAdmin again
  3. Select the “Import” tab
  4. Choose your recently exported .sql or .sql.zip file to import your online data into your local database. Then press “Go”. The import should work without problems. If you had errors, make sure that you haven’t selected “Add DROP DATABASE”, if this is your first import. If you followed the WordPress database export, then quickly create a database with the appropriate name (same as your online database) and re-try the import.
  5. Go back to WAMP’s localhost start page and select your WordPress site. You should see the following error:

WAMP
error

If you see the above error, then everything is going according to plan.

Where we are so far

You’ve downloaded your website files, you’ve installed your database locally, you’re running WAMP. Great! The last thing you need to do is to update your WAMP WordPress database to let it know the new path to serve up pages from (and to give your local WordPress installation access to the new database).

Configuration time

Okay, so let’s tell WordPress how to connect to this new database.

Open up your wp-config.php file (in your main WordPress directory, not a sub-folder) and find your defines (they should be just after commented package information). They contain your MySQL database information, which is currently not correct, and is causing the DB connection problem.

There are a couple of ways you can fix this (varying levels of ‘correctness’), but for the purpose of getting you up and running, I’ll suggest just changing these to the default values that WAMP’s MySQL comes installed with. Just make sure you DON’T upload this file back to your server, otherwise you’ll screw up your correct database settings. Locally, this is fine though.

Note: Below are the values you want to change your config settings to… ‘root’, no password, and ‘localhost’.

/** MySQL database username */
define('DB_USER', 'root');

/** MySQL database password */
define('DB_PASSWORD', '');

/** MySQL hostname */
define('DB_HOST', 'localhost');

If you try your website again, the DB connection error is gone, but is probably replaced with some complaint about your browser not being able to find or connect to your site.

What has happened now is that your database driven website (ie. WordPress) is redirecting to the wrong location. This can be fixed by editing two lines in your database.

In the wp_XYZ_options table (where XYZ is different for everyone), find the option_name(s) “siteurl” and “home” (they were rows 1 and 37 for me). You’ll notice that these point to your online WordPress location, instead of your WAMP location. As I’ve done in the images below, change the location to reflect your localhost url (I’ve added port 8080 as I cannot connect to localhost otherwise, your situation may vary).

What to edit in the
homepageWhat do edit for the
site

If you reload your website again, it should (in theory) be working now… If that’s the case, great! Enjoy!

It’s almost there…

On my installation, I can see my website, but above it is a very big and visually noisy complaint/exception from my wp-super-cache plugin. The super-cache plugin is still looking in the wrong directory for its files.

This is easily fixed by going back into the wp-config.php file and looking for the WPCACHEHOME definition (should be near the top, just above the MySQL information).

Change the define to point to the correct location on your computer, noting that you should only use forward slashes.

define( 'WPCACHEHOME', 'C:/wamp/www/sureshjoshi.com/wp-content/plugins/wp-super-cache/' );

Re-load your website and it should be up and running.

It’s not working!

If your main page works, but if clicking on your posts causes errors or redirects to WAMP, there are two things you should do.

First, turn on Apache’s mod_rewrite:

  1. Click on your WAMP icon in the taskbar >> Apache >> Apache Modules >> rewrite_module
  2. If it doesn’t do it automatically, restart your WAMP services.

Links still don’t work

That may or may not fix the problem. What I also find works is if you change your WordPress permalink settings.

From the WordPress admin dashboard >> Settings >> Permalinks >> Select “Default” (which is the really ugly one that looks like ‘sureshjoshi.com/?p=123’) >> Save >> Reset permalinks to whatever you had before >> Save

Re-load your website and everything should be up and running, FINALLY!

Once more with an end-to-end summary

  1. Install WAMP and launch WAMP
  2. FTP your online WordPress installation to your local WAMP www directory (ie. C:.com)
  3. Using PhpMyAdmin, export your database, or the tables of your database.
  4. Using WAMP’s PhpMyAdmin, import your database or DB tables.
  5. Edit your wp-config.php and change the MySQL username, password, and hostname. (Alternatively, you can create a new user with the correct username and password, then add it to your database)
  6. In wp_XYZ_options table, find ‘siteurl’ and ‘home’ and change urls to localhost.
  7. Turn on Apache rewrite_module and restart WAMP services.
  8. Change WordPress permalink setting to default and then back to what you previously had.

And NOW you should have a fully operational, local installation of WordPress sitting on your WAMP server… If anyone has ways to streamline this process, I’d love to hear about them.