We have many websites with Development, Staging and Production servers. We have many developers for many projects. We need a solution to synchronize the developer and staging databases. After that one is worksing we can move to the live database.

That one needs to be fully automatically synchronized so that developers don't need to run that tool each and every time.

  • 1
    1. I did not understand your question, what you want is data from Dev_server to Stagging_Server, does the same apply to the other way? Stagging_Server to Dev_server ? 2. when you finish your development you want to have the data from Stagging_Server to be loaded on Production server once, is that correct? – altmannmarcelo May 15 '13 at 10:18
  • You can synchronize through "SQLYog Ultimate Tool" Tools->SQLyog Database Synchronization Wizard-> (source) & (Target)->One-way-synchronization or two-way-synchronization – Md Haidar Ali Khan Feb 1 '15 at 12:31
  • what do you mean by "We have many developers for many projects". To assist you , we need more details , otherwise this question does not make any sense . – Ankit Kapoor Apr 25 '17 at 5:40

use replication master=> slave ? be carefull of conflict

  • 1
    You should add more detail. Perhaps how to do the replication at a high level, or point to some documentation sources, etc. – Max Vernon Apr 15 '13 at 14:20
  • How can they do that? Developers machine are supposed to change their data, making them constantly inconsistent with the masters, and breaking replication incredibly often. – Federico Razzoli Mar 1 '18 at 8:33

Replication cannot work in your case, because developers databases will often change, and conflicts with the master break replication (by design). Also, developers cannot rely on data that are constantly changing.

You need to take some form of backup and send it to developers machines. Here are the best solutions in my opinion:

  • ZFS snapshots. You'll need a MySQL instance in production running on ZFS. It should be a slave, not all your MySQL servers, because ZFS latency is higher than standard filesystems. You'll also need to use ZFS on development and staging machines. Periodically, the masters will take a snapshot and send it to staging and development. These snapshot will be incremental. This also works as a backup, because all servers running ZFS will be able to rollback to the latest snapshot if needed. I suppose it is only doable if your developers use Vagrant machines or containers that you can modify.
  • XtraBackup. This won't require the use of a particular filesystem. XtraBackup can make backups on a server while it is running, and almost without locks. This is a great advantage. These backups can be sent to staging and development for restoring (you simply replace the data directory with the backup). These backups can be encrypted, if needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.