Wordpress database migration

Question

Hi Everyone! I've looked around the Wordpress forums about this and didn't find anything so I thought I might try here.

If you have a staging/dev Wordpress setup used for testing new pluging and such, how do you go about migrating the data in the staging database back to the production database? Is there a "Wordpress best practices" way to do this, or am I limited to having to manually migrate tables from one database to the other?

Answer 1

Perhaps you are just looking for the wrong thing. Wouldn't a backup plugin handle this with ease? I know they exist for all the big CMS packages...

Answer 2

The two methods would be using the export/import feature under tools or copying the database. I email myself a copy of my production database weekly using the WordPress Database Backup plugin.

The import feature can be problematic for moving a wordpress blog as you have to configure your php.ini file often because the default value of files you can upload on a hosted php implementation tends to be too small by default.

Answer 3

Everything you need to know is in the Wordpress docs:

Backing up your database

Restoring your database from backup

Answer 4

I wanted to pull the database from my production wordpress website into an offline development copy of it on my desktop machine so I could modify the site and test it with a full set of the existing blog content and history.

This proved to be problematic, as simply making an offline backup of the database and importing it into the local development database did not work.

Overcoming these problems in moving data from the production to the dev database can probably be used to go the other way as well - so I think you can just use these guidelines for what you want to do as well - just start with dev data and move it to prod.

The problems here were:

1. the permalink designations for the blog posts are all stored in the database as they would be for the online version, but my offline copy isn't at the domain address, instead it is in the localhost directory. So when I launch the site locally, although the css formatting and images are all in place (the image links being relative), the actual blog posts don't show up.
2. many of the links throughout the site link back out to the internet, so if you try to navigate to archives, or comments, or categories, or the main posts, you get sent back out to the internet instead of staying in the database on the local machine.

To make sure I was doing this right, I blew away the wordpress install I had on my local machine and restarted from scratch.

Once I had a clean, new wordpress install and brand new default freshly created local database for it, I opened up the database in phpMyAdmin and took a look at the wp_posts

table. Inside there, each record (in other words, each post) has a column titled "guid", which shows the location of that post. For example, the first one in a fresh, default

install contains this "guid" value:

http://localhost/wordpress/?p=1

If you look in the wp_posts table of your online version, you'll see instead in this location the url to your site online.

You can't just import the tables wholesale into your local install, because you'll be importing all these outside references. It will make your local version impossible to navigate locally.

So, I created a backup copy of my online site's database and saved it locally as a .sql file. I then opened that file in a text editor (I used notepad++, a great piece of free software, but you could use any text editor). Things I needed to look out for:

• For whatever reason, the tables on my online site aren't just, for example, "wp_posts" - they are "wp_something_posts"... there are some extra letters in there in the table names.
• Any references to http://... that contain my online url instead of localhost/wordpress

To keep it simple let's just do only the posts. In the backup copy of the .sql you've made of your online database, find the beginning of the wp_posts table. It will look something like this:

--

-- Table structure for table `wp_posts`

--



DROP TABLE IF EXISTS `wp_posts`;

CREATE TABLE `wp_posts` (

...and so on. Highlight everything above that up to just below the comment marking the beginning of the database at the top of the file (it will say -- Database: 'your database name') and delete it. Then go to the end of your wp_posts table, and delete everything after then end of it down to the bottom of the file. Now your file only contains your posts, and nothing else.

Save this as a separate document. Call it posts.sql or something like that.

Now, in this posts.sql file, you need to do two find/replaces actions.

1. Find every instance of the name of the table wp_something_posts and replace it with wp_posts. You only need to do this if your backup copy of your online database doesn't match your clean local install as far as the table names go. You want whatever the table name is in this file to match what your locally installed wordpress database has as this table name. If you don't make these names match, you are just going to end up importing the posts into a new, differently named table, which will be of no use to you at all.
2. Find every instance of http://... (replace the elipsis with your url) and replace it with http://localhost/wordpress (or whatever the local url to your dev version of the site is)

Now save this file again, to make sure you've got these changes set.

Now that you've done that, use phpMyAdmin to get into the wordpress database on your local machine, select the "import" tab and navigate the selector to the posts.sql file you just made, and then import it. This will pull all the data in that file into your local wp_posts table.

When that finishes, browse your local wordpress site. You'll see all your posts in there now. Hooray!

You may need to do something similar for a few other tables if you want to bring in your comments, tags, categories, and static pages you've created, etc.

I realize this is a convoluted process. There is probably a tool out there somewhere that makes this activity easier, and if someone knows of one I'd love to find out about it. If someone knows of a better way to do this manually than what I've described, I'd love to know that as well!

Until then, this is the way I figured out how to do it. Hopefully it helps get you going in the right direction.

Answer 5

I'm not having problems with my permalinks... I change my host file so that isn't an issue (i'm using Apache on my dev box too). So can I just replace all but the posts, comments, etc. tables and be ok?

(backing up and restoring isn't the answer... the live server has been in use while in dev and I can revert the database to a previous conditions and loose new activity.)

Answer 6

I have a script that mysqldumps a copy of my production Wordpress DB, restores it over my test Wordpress install & then corrects all the "production" settings & urls in the test DB.

Both my production & test databases live on the same server, but you could change the mysqldump settings to dump from a remote mysql server & restore to a local server quite easily.

Here are my scripts:

overwrite_test.coach_db_with_coache_db.sh

#!/bin/bash 
dbUser="co*******"
dbPassword="*****"
dbSource="coach_production"
dbDest="coach_test"
tmpDumpFile="/tmp/$dbSource.sql"

mysqldump --add-drop-table --extended-insert --user=$dbUser --password=$dbPassword --routines --result-file=$tmpDumpFile $dbSource
mysql --user=$dbUser --password=$dbPassword $dbDest < $tmpDumpFile
mysql --user=$dbUser --password=$dbPassword $dbDest < /AdminScripts/change_coach_to_test.coach.sql

change_coach_to_test.coach.sql

-- Change all db references from @oldDomain to @newDomain

SET @oldDomain = 'coach.co.za';
SET @newDomain = 'test.coach.co.za';
SET @testUsersPassword = 'password';

UPDATE `wp_1_options` SET `option_value` = REPLACE(`option_value`,@oldDomain,@newDomain) WHERE `option_name` IN ('siteurl','home','fileupload_url');
UPDATE `wp_1_posts` SET `post_content` = REPLACE(`post_content`,@oldDomain,@newDomain);
UPDATE `wp_1_posts` SET `guid` = REPLACE(`guid`,@oldDomain,@newDomain);
UPDATE `wp_blogs` SET `domain` = @newDomain WHERE `domain` = @oldDomain;
UPDATE `wp_users` SET `user_pass` = MD5( @testUsersPassword );

-- Only valid for main wpmu site
UPDATE `wp_site` SET `domain` = @newDomain WHERE `domain` = @oldDomain;