I have been tasked with creating a Development mysql instance, We currently have a 7 node Xtradb cluster, is there any way to have this node a few hours behind, or not allow replication back to the rest of the cluster? I would love for it to update from the other nodes, but not allow replication back.
I worry about someone dropping a table, or doing not so pure things, and bringing the cluster down.
any ideas would be much appreciated!
Just have your development server connect using normal replication, not join the cluster. Standard replication is one way (unless you set it up on both sides). Your dev server would pull the binlogs from the master it's slaving from. That's all the communication that would happen between the dev instance and your cluster.
I don't think there would be an easy way to do this with a node that's actually part of the cluster. The whole point of the cluster is to not have the data out of sync.
If you're mind is set on doing it via replication delay, take a look at pt-slave-delay if you're running 5.5 or lower. If you're on 5.6 then that functionality is already built in.
The way I would do this would not involve replication at all. You should be taking proper backups (I'm a fan of Percona Xtrabackup), then you can simply restore that backup to your dev instance. Do whatever you want, then restore it again.
Doing it via replication will cause you more errors than you'll probably want to deal with. If someone does a drop table on the dev host, replication just broke the next time the main cluster tries to update that file. You'll have to go in manually to restart it (or run a script that does it for you).
