Home / server / Questions / 837714
In Process
neubert
Asked: 2017-03-12 11:57:56 +0800 CST

overwrite values with mysql master-slave replication

  • 772

I have a production database with master-slave replication being done on it. Production DB backups are done off of the slave server.

I'd like to make it so that an arbitrary number of development environments can be replica's of the slave. The problem is that, whereas the slave server has nothing writing to it, the dev servers might. Like even just logging in through the web interface to the dev site could trigger an INSERT into a loginLog table or some such, which could cause the slave replication to stop with errors like this:

Could not execute Write_rows event on table dbname.tablename; Duplicate entry '15610534' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log bin-log.000829, end_log_pos 8209872

My question is... is it possible to setup a slave DB such that when conflicts are found that the master DB's data overwrites the slave DB's data?

The advantage of this would mean that developers could have access to real time data in a development environment. Like if a developer is developing some sort of real time report on sales (eg. a "leader board") this real time data could be useful.

mysql mysql-replication master-slave

1 Answers

  1. No, you can't eliminate such issues with mysql replication.

    But why would key violation errors arise when developing a report?

    The big question is exactly how up to date does a development copy of your production data need to be? And how much data are we talking about?

    You can run a very large number of slaves off a single master. But maybe the right solution here is to run an additional slave as the primary volume copy of the production data then take it offline overnight to create filesystem level copies which are then mounted on new instances. This is easy to do with storage area networks. But it would also be possible with copies running on the same host, or by running the primary volume copy slave on top of a mirror of local disk and DRBD, then remapping the remote DRBD into a RAID mirror to bring a satellite copy into sync.

    Or, if its a small database, just use rsync.

    This does mean that you would lose any test data created between snapshots.

    • 1