if I setup a mysql slave for replication, can I still adding and modifying data to the slave from another process while it copies data from master via binlog?


Although bad practice, yes you could theoretically do so, provided you follow one simple rule:


Although bad practice, I have seen clients set up development/staging databases on a slave. The master would replicate to slave's copy of production databases and SELECTs would be run from them.

Developers would read from and write to developer/staging databases at will.

As long as developers never touch the slave's copy of production databases, they usually never have a problem.

You also do not want master-master setups to have developer/staging databases. It just creates more replication traffic and binary log bloating.

staticsan and pitr have made valid points also very worthy of consideration.

+1 for both of you !!!

| improve this answer | |

Yes, but in case of conflict the replication stops, ane You will have to reinitialize it from master.

The consequence of this is, that if you have two instances of application, which are writing to many replicated databases, and application ensures avoidance of conflicts - the system will still work properly.

| improve this answer | |
  • what would be an example of a conflict? when the replicating slave updates record A and another application updates record A at the same time? – user12145 Mar 10 '11 at 0:42
  • An example is you insert row 12342 into the slave. Then the master sends an insert row 12342 later. Replication will break because of duplicate records at that point. You can however create a new database that isn't in the master and do whatever you want in that database without affecting replication. – kashani Mar 10 '11 at 0:48

It's not recommended because it can easily break replication. And then when you restore the slave from a backup off the master, you will lose whatever changes were made to it.

If you have to have modify access on the slave, what you probably want is master-master replication. This is where both databases replicate off each other. You can still hang other slaves off either master, as well. And you don't have to limit yourself to two in the ring, though that increases single points of failure.

Another alternative is selective replication, where the slave only updates some tables or databases. This works best if the app using the slave is really just using a different database in the same server instance.

| improve this answer | |

Your Answer

