How to sync 2 wordpress sites (live + development)

Generally speaking, your custom work is generally constrained to plugin and theme files, plus certain entries in the options table. Most changes to the live site are constrained to the other tables and the uploads folder. Thus you should be able to largely merge the relevant portions to arrive at an up to date, newly themed site.

Where you run into trouble is any new pages you created. Hopefully that isn’t too extensive. There may be other minor overlaps. They should be minor enough to manage manually. For example, manually copy over your new pages before exporting production DB (except for options table) back to development. Once you export production DB, everyone needs to freeze site changes until the migration is complete. Backup your development DB before importing the production copy.

You can do a diff of the production export and development backup to identify unknown discrepancies. Look for items in development missing from production. Either copy over and make a new export, or add the data from backup after importing production. Do the opposite for options, find items in production missing in development, and resolve, ignoring logical differences like site and home URLs.

Once you are sure development is an accurate representation of production with new theme etc., then you can safely migrate to production, resolving URL differences where necessary.

In other words, there’s no easy answer, but if you are careful and methodical, cognizant of what changes are kept where, it’s manageable.

Thanks bcworkz for taking the time to write a long reply.
Basically I re-designed a lot of the existing pages in the development. I kinda understand what you said about what needs to be done. However, I am still not quite clear where to start and what needs to be done step by step. I am looking for a step-by-step guide (if exists) to guide me through this process. I know there are a lot of manual work involved.

Umm, yeah. I’m unaware of such a guide, sorry. What’s deadly is pages where both versions contain edits and you must reconcile both versions. It’s much easier if one could completely replace the other. The Linux diff tool is useful for ferreting out variances either way. There ought to be similar tools for other O/Ss.

You can export/import fragments of DBs, and the SQL file format is plain text and editable provided you conform to mySQl query syntax. By getting familiar with phpMyAdmin, you can piece together a composite import file from the two different exports. It’s more a matter of working carefully, methodically, logically, keeping progress backups along the way; than following some rote step by step.

@bcworkz, thanks again for the reply. If I understood you correctly, what you’re suggesting is to go through the DB tables in phpMyAdmin and export fragments of DBs (in sql files) of both live and development sites, then compared the sql files to figure out the discrepancy, then merge/update to one sql file to be imported?

Then, how about the website files? is it a similar manual process to figure out the differences of the files?

Thanks

Yes, exactly. Though there shouldn’t normally be differences within files that need to be resolved. Your new theme should be completely replacing the current theme. All site changes since you first cloned the development site should all be in the DB. (excepting media uploads)

It does sometimes happen that custom templates contain content that gets edited and might need to be reflected in your new theme somehow. If users are editing templates, it’s a strong indicator that the content should be coming from the DB somehow, either a custom field, post, option, etc.

You know what differences need to be resolved much more than I. It’s more important to be thorough, organized and methodical than exactly following my suggestion.

The other differences you could encounter are menus and widgets. These are saved as part of the current theme. When you change themes, these need to be re-defined where appropriate. The originals still exist, but need to be associated with the new theme.