Mysql – thesql prevent update other salve-master db while any record update in slave db

MySQLreplication

I have master and 3 slave (slave1,salve2,slave3) db in same host. So when I update any record in slave1 db its affected on all other master and salves db.

My Question is: How to prevent update all other db whenever I update any record in salve1 db, and whenever I update any record in master it will be updated in all db?

in short I want to replicate db in one way master to salve not slave to master..

Best Answer

If we assume you have the following databases:

Master  (DB0); 
Slave 1 (DB1); 
Slave 2 (DB2); 
Slave 3 (DB3);

The only way for a 'slave' (e.g DB1) to update the 'master' (DB0) is if the 'slave1' (DB1) is also acting as a master to the 'master' (DB0). (commonly known as Master to Master Replication)

You can check if this is the case by connecting to your 'master' (DB0) database and running SHOW SLAVE STATUS;

The result 'should' be blank, most likely it isn't.

Look for the fields: Master_Host and Master_Port - this is the database from which it is receiving data. It will probably relate to one of your 'slaves' (DB1).

Slave_IO_running and Slave_sQL_running - if these are both 'Yes' then replication is up and running, and any changes made to the database above will appear on your 'master'

The quickest way to stop it is to then issue STOP SLAVE; on your 'master' (DB0).

Depending on what version of MySQL you are running there are then some things you can do to erase the settings. https://dev.mysql.com/doc/refman/5.6/en/reset-slave.html (I recomend making a note of the settings in SHOW SLAVE STATUS before issuing any kind of RESET, just in case)

Also you may want to add: skip-slave-start to your my.cnf file on your master, which will prevent the database starting replication again if the server is restarted.

As @ypercube mentions above, you should avoid writing to a 'slave' database, unless you have specifically configured it as part of a master to master setup.

Related Question