What is a good way to set up a dev, staging and production workflow with wordpress
Wordpress presents some challenges since it tends to keep too much in the database, making it hard to move from server to server.
What are some other issues to look out for?
What was your workflow like?
Answer
I have a single WordPress install set up to power multiple domains on my development server. Plugin and theme files are also shared, so upgrading is a one-click process for all blogs.
I use Apache VirtualHosts to map multiple domains to the same document root, and sprinkle a little magic in the main wp-config.php to dynamically set DB_NAME, based on the current host (I can post code if you like).
For working locally, I just have a MySQL user with root privileges, and use it for all my databases (not recommended on a production server!).
My local domains are named appropriate to the real domains, but with a fake TLD. So working with example.com, I set up a VirtualHost example.dev.
When I'm ready to go live, I use HeidiSQL to make a copy of the development database, then replace all occurrences of example.dev with example.com.
The copied database is now ready for the production install. Mirror your local WordPress install on the production server (copying over plugins, uploads and themes), and use either HeidiSQL (recommended) or phpMyAdmin to import the prepared database.
UPDATE
Naturally, if you make changes to one, and then copy everything to the other, then you will lose any changes you had made on the other. This goes not just for WordPress, but for almost anything else in life itself!
If I ever need to make major changes once the site is live (and by major, I mean changes that should not be carried out on a production server), I do the reverse process of the above (copy everything from production to dev), make the changes, then do the reverse again.