MySQL – Forcing Master to Overwrite Slave in Replication

backupMySQLreplication

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.

Best Answer

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.