[FIXED] How to merge local and remote databases?

Issue

I am working with a live site that is dependent on a database (which I have access to). I have copied the site locally and am running it with MAMP. All of the files from the server are version-controlled using git, so the files on my localhost are always up to date with the ones on the server.

However, the database doesn't work as well as the files. To merge the changes from the live database I have to export it as a .sql file, then import it into my localhost. However, if the local database changes, those aren't reflected when I re-import. How can I merge the changes so that I keep my local database updates, but "pull" the remote changes so it is up to date?

Solution

Well, you have multiple options here.

You can use FEDERATED Storage Engine and can query the remote database locally. FEDERATED Storage Engine is kind of same as Linked Server in MS SQL Server

You can as well set up Replication between the servers (Transactional Replication). In which case, depending on the setup data from primary will get synced with the secondary server; if not instantly but over a period of time.

You can as well User MySQL BackUp and Restore procedure. I mean specifically, Differential/Incremental Backup

Answered By - Rahul