0

We have a LAMP architecture website.

Currently we have a production server running on the cloud and each developer has a complete copy of both the code and the database. Code is synced through Git and it's working fine. However, the database is synced by dumping the production server nightly and import everything into the local database. As the database grows larger, this process is taking unreasonably long time.

So I tried to set up a master slave replication where the production server is the master and my developing workstation as a slave. The problem is, during developing, we have to write to the slave for testing purpose. And some write operations causes errors during the replication such as primary key conflict, etc.

I am wondering if there is a way, other than dump/import, with master-slave replication where we can force the master to overwrite the slave in case any conflicts happens. For example, during the replication, the slave should delete anything that has conflict with the master and apply the changes happened on the master.

Or if this is not possible, is there a better way that I can refresh my local database with the server? This does not need to be 100% identical or real-time. I understand that the process I described above may lead to more discrepancies over time if the local changes do not cause conflicts. This can be solved by dump/import method as long as this does not happen too frequently.

Improve this question
3
  • Use xtrabackup instead of dump/import for faster restore. Or just do filesystem snapshot of your slave each night, then restore it after workday and let it catch up to the master and snapshot again.. Master-Slave is not designed for this so all options are jsut workarounds. Do your devs really need a copy of production db for normal daily development and testing? We have one testing copy of the DB instead which is totally separate from production.
    – jkavalik
    Commented May 17, 2016 at 20:35
  • @jkavalik Thanks for your advice. Taking file system snapshots seems to be a good idea.
    – kebugcheck
    Commented May 18, 2016 at 20:42
  • Look at pt-table-sync
    – Rick James
    Commented May 27, 2016 at 20:49

1 Answer 1

Reset to default
0

I use nightly lvm snapshots of production databases that are made static by removing the files needed for replication (master.info, relay-logs), and making that snapshot writable. You'll need to shut mysql down for a few minutes to make the snapshot and have room on the machine to hold that snapshot, but it's an easy and elegant solution.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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