Logging all changes made to MySQL database

Question

I have a web application setup where there are separate Apache-Wordpress containers for development and production. Database is hosted externally on a virtual host.

When setting up development environment, I simply take a copy of the Apache container and create a MySQL container where I import dumps from the external database.

Problem arises when I want to update the production environment by replacing the production container with the development container. Updating the development container has made changes to the testing database, for example new tables or schema changes, which are not present in the external database.

How could I update the external database with the changes made to the testing database, without losing data that have come to the production database after I took the dumps?

Answer 1

There are several tools available to serve the purpose. Following are my favorites:

1. SQLYog
2. DB Ninja

Here SQLYog is paid tool, howover it has only windows version available. If you choose this one then its following features will do the job for you:

• Schema and Data Synchronization
• Visual Data Compare

If you prefer web based interface then DBNinja is great tool. You can compare two database schema easily and fix the differences. following is the example screenshot from their official page:

Answer 2

There is a utility, which I am using now to do the similar task, I hope this will help you too.

mysqldbcompare --server1=usr:pwd@host1 --server2=usr:pwd@host2 --changes-for=server2 --run-all-tests dbname > changes.txt

This utility will help me to compare the database on two server hosts and write the differences and what needs to updated on server2 into a text file.

Check the official mysqldbcompare page for more details. Also, this utility have the option to compare data on both the servers. Go through all the parameters before using it. Thanks.