Hi I have just started reading about this replication concept and one thing I want to ask is that when we change something in the slave db how is it going to effect the master db? Is there a sync algo that will change it in master db as well? and another thing is if there are multiple databases can we apply replication on them as well? like multiple databases in masters server replicated into multiple databases in slave server. Thanks
2 Answers 2
How replication happens:
DMLs on Master are written to Binary logs.
Slave connects to master from it's IO thread and reads the binary logs. Save them as Relay log.
Slave's SQL Thread execute the Relay log SQLs to apply the changes.
Now, if you need slave's changes to be reflected on master, you need your master to be replicating from your slave as well. That's known as master-master replication. You can find plenty of posts around that.
By default, all the databases are replicated. If you want to replicate only few of the objects (DB / tables) there are replication filters for that.
"...when we change something in the slave" STOP. Do not do that!
Or do you want "dual master", which is two servers, each acting like both Master and Slave. But that can be a nightmare. Duplicate keys. AUTO_INCREMENT issues. Etc.
Using binlog/replicate_do/ignore_* (in my.cnf) you can control what is (or is not) sent out of the Master (binlog_) and what is not (or is) ignored by the Slave (replicate_).