Question: 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: 
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.
This about sums up the problems with the wordpress core architecture... but I wrote a plugin that solves the problems with domain names and absolute urls being stored in the database:

<http://wordpress.org/extend/plugins/root-relative-urls/>

This will solve the problems outlined by @oddbill. Though don't worry too much about the url being in the GUID column as that field is never used for link generation.

@markratledge provides a couple links to some lengthy documents that basically say this:

//export

`mysqldump -u[username] -p[password] [database] > backup.sql`

//import

`mysql -u[username] -p[password] [database] < backup.sql`

You'll want to exclude the comments/comments\_meta tables if you push to production from staging so you don't lose all of your comments and trackbacks (@DavidLaing's approach will wipe those out.) And this assumes you only make content changes in your staging environment. If you want to make changes in production and your staging environment, you'll need to write scripts that sync the data instead of wholesale overwrite it... good luck on that task, may I suggest adding create & modified timestamp columns before you invest too much time with the current schema.

And finally, @RussellStuever's approach is suitable in most circumstances, just be sure to know when you are browsing your host mapped site versus your production site. And really be sure about it, because some browsers cache dns lookups for days until you physically close them and start a new process. At which point switching hosts may take some time, and switching frequently may get frustrating. And if you need to test with an iPhone, you'll need to publish the site live first, or use a good router that can remap outbound internet requests to local servers because you cannot modify hosts files on most mobile devices.

My plugin lets you develop and test from <http://localhost/> or <http://staging.server.local/> or <http://www.production.com> without any of the usual pitfalls. And then to migrate data, it's as simple as exporting and importing the data, no search & replace step or database setting tweaks necessary.

And don't rely on the import/export tool, it doesn't capture everything in typical wordpress installations, and still requires a needless search & replace step.