MySQL database synchronization

Question

PHP

The task is to synchronize two almost identical MySQL databases (CMS). One is on a test domain, the second is on a working one. Hosting is different. Changes are made only on the test domain, then they must be sent to the working domain at the command of the admin. There are entries in the database that should be different on two different domains (domain names, paths to files on hostings).
How can you set up synchronization of such a system? It would be optimal if only changes were synchronized, and not entire dumps.

Answer 1

That is, you want to transport not only the scheme but also the data from the test to production?
It seems to me that there is only 1 option here:
Some script dumps that part of the circuit that needs to be transferred, replaces some lines as necessary, uploads the dump for prod.
In transferring only new data, the mysqldiff utility can help you.
But due to the fact that you have some data replaced, so that it does not happen that all the data is accidentally defined as "new", you will have to create an intermediate database that will contain the test database, but with names substituted from the production, and compare this database, and get diff.
Or, after you have made changes in the intermediate database, sync it with the production using the table-sync utility from the percona tools set.

Answer 2

most likely very custom scripts for you, suspended on a post-commit hook in git, although I would not automatically approve this on a production server

Answer 3

domain names, paths to files on hostings must be stored in files excluded from the version control system. such settings are not stored in the database
; the very concept of updating the database from the outside with low-level mechanisms is vicious - sooner or later it will lead to logical inconsistency of the data. so import-export at the application level