Remove master role from MySQL server

Question

I have two MySQL servers, one is the master and other one is slave, that's all good.

The problem I have is that the slave is also set up as a master. I would like to disable or remove the master role from the slave server.I have commented everything out in my.ini that relates to master.

The way we set this up was a copy of a database which was a master and copied it across to the new server. So the master configuration was already there. I set up slave replication and that all works, it talks to the original server.

Set up is like this: Server 1 is a master. Server 2 is the slave. Server 2 replicates with server 1. Server 2 also has Master replication configured. Is it possible to remove or disable the master configuration on server 2?

The server is Windows 2008 R2. The data sits on WAMP server with phpmyadmin.

Answer 1

I'll get to the answer shortly, but first I should say I'm not sure why it is that you are trying to remove a "master role" from a host. Basically, a host is not configured "as master". Slaves just connect to it, which makes it a master.

I'm just curious: is this a security issue or something, that you want to forcibly prevent slaves from connecting to this host?

Anyway, one very easy way to do this is to remove the log-bin parameter, and restart MySQL. This will prevent MySQL from generating binary logs. No binary logs means slaves have nothing to get from this host, hence it cannot be a master.

Otherwise you may try and control that in terms of privileges. I don't think this is a good idea because this slave of yours will normally replicate everything from its master, including the privileges set on the master. So setting the slave to have a different (stricter?) set of privileges means it's not entirely in sync with its master.

Oh, and you may just grant the replication privilege on a pre-defined set of hosts, that would do the trick as well:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.2.101' IDENTIFIED BY '123456';

Answer 2

I assume this setup you have

Server A : Master

Server B : Slave

B is replicating A and you do not want B to be replicated to either A or any other Server say C.

In this case you have to remove MASTER related entries from all other servers (from Server A, Server C) which are connecting to Server B (Master server).

Or

You can set server-id of master (Server B) to 0 for disabling replication from master side

Answer 3

You need do following things on slave 2:

1. Do RESET MASTER

   It Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. This statement is intended to be used only when the master is started for the first time.

   Important The effects of RESET MASTER differ from those of PURGE BINARY LOGS in 2 key ways:

   RESET MASTER removes all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric suffix of .000001, whereas the numbering is not reset by PURGE BINARY LOGS.

   RESET MASTER is not intended to be used while any replication slaves are running. The behavior of RESET MASTER when used while slaves are running is undefined (and thus unsupported), whereas PURGE BINARY LOGS may be safely used while replication slaves are running.

   Have a look at documentation for RESET MASTER.

2. Disable binary logging on slaves.

   In your my.ini, find and comment out all lines starting with log-bin or expire_logs_days to turn off binary logging.

3. Restart MySQL Service