Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 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 the my.ini that relates to master.

The server is Win2008 R2, The data sits on WAMP server with phpmyadmin.

share|improve this question
    
Can you please explain, How your slave is working as master ? Are you using Master-Master Replication –  Abdul Manaf Jan 14 at 5:32
    
Hi, 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 configuation on server 2. –  Ray Jan 14 at 5:37
add comment

3 Answers

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';
share|improve this answer
    
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 at 5:56
add comment

As per your comment you need do following things on slave 2

Step 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 of RESET MASTER.

Step 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.

Step 3 ) Restart MySQL Service

share|improve this answer
    
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 at 2:51
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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