I work with WordPress and I work with git. My code is versioned and I am happy with the workflow.

What bugs me is database synchronization.

I need to work with cloned database on my local development environment so I can easily alter any settings, publish new stuff, etc. In the meantime (say two weeks) when my work is done, I want to push all changes to the production. Git handles the files, but the database is a huge problem...

Option A: I could easily export and import my local database to production, but this would overwrite and/or remove everything that changed on production in the meantime. This is critical especially on blogs or e-shops. I would lost all new posts and orders. So, this is a no go.

Option B: I could somehow export my database data (using either WP export tool or some 3rd party utility) and then import it to production. However, I can't think of any elegant way to really export and import everything (including, new custom post types, custom fields, 3rd party plugin settings, etc.), so this is a no go as well.

I really can't think of any possible way to do this. I have been looking for a way to deal with this for days, with no luck. Even some plugins and utilities that focused on this gave up on development, or are still in early beta states after quite a few years.

WordPress runs on about 1/3 of websites as far as I know. I believe good amount of these websites are large ecommerce stores or news sites, maintained by developers with proper workflow. How do they handle database synchronization?

Edit: This question should not be a duplicate because I am aware of possible solutions mentioned in that question. I actually mentioned those workarounds in Option A and B, but I definitely would NOT consider those as a valid workflow I should be doing.

Edit2: I've come across WP CLI, which seems to have commands to export/import the database, but I didn't read about resolving conflicts at all.