2

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.

0

3 Answers 3

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';
1
  • The server set up as slave talks to a different host.Server 1 is master, Server 2 is slave, but server 2 also has master configuration which i would like to disable or remove. Server 2 talks to Server 1.
    – Ray
    Jan 14, 2014 at 5:56
1

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

1
  • from the documentation "If the server ID is set to 0, binary logging takes place, but a source with a server ID of 0 refuses any connections from replicas, and a replica with a server ID of 0 refuses to connect to a source" so it seems setting server-id to 0 would disable replication if set on either master or slave..
    – l0ft13
    Sep 14, 2021 at 7:17
1

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

1
  • I did all this and the binary logging has an ID of mysql-bin.000001 and i commented anything out in the my.ini file. One question, when i look under replication tab on the slave server it still says the This server is configured as master in a replication process. Should that be there?.
    – Ray
    Jan 15, 2014 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.