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 want the master to stop replicating data to the slave. I know I can do that on the slave with STOP SLAVE;, but I wonder if there is a way to do it in the master.

One possible solution might be to change the server_id to 0, but in this case I'll have to restart mysql in the master for changes to take effect.

What I'm looking for is a statement like STOP MASTER;.

share|improve this question
    
Hm, would denying connections to the IP the slave is located work? –  ypercube Apr 21 '13 at 10:11
    
you mean by changing replication user's pw or such? –  HalaKuwatly Apr 21 '13 at 10:26
    
Why do you want the master to stop replicating data to the slave? Is it because you do not want to use replication any more, or are you trying to solve a different problem? The best answer, and a safe answer, to your question cannot be given, without that information. –  Michael - sqlbot Apr 21 '13 at 16:23
    
the slave is no longer reachable and i want to stop replication from the master so that when the slave is reachable again, replication is not continued. –  HalaKuwatly Apr 22 '13 at 6:24
add comment

2 Answers

There does not exist a STOP MASTER; command nor is there a manual mechanism from the Master to stop Replication. You would have to go to each Slave and run on of the following:

  • STOP SLAVE; (Kills the IO Thread and SQL Thread)
  • STOP SLAVE IO_THREAD; (Kills the IO Thread only)

Running either of these will get you the following:

  • Clean Recording of the Replication Coordinates in master.info
    • Master_Log_File
    • Read_Master_Log_Pos
    • Relay_Master_Log_file
    • Exec_Master_Log_Pos

The IO Thread is what communicates with the Master. Killing the IO Thread on the Master Side using the KILL command would abort the IO Thread on each Slave. That could corrupt the Clean Recording of Replication Coordinates.

share|improve this answer
add comment

The easiest way to do this online would be to revoke all REPLICATION SLAVE privileges on the master for any users who have it.

This offers the additional benefit of allowing you to be selective about which slaves you remove.

Once that's done, check for any existing replication sessions with SHOW PROCESSLIST and kill them off with the KILL command (these might drop off by themselves after you revoke the privileges, but I suspect this isn't the case).

You need to have in mind that, using this approach, the replication won't completely stop on slave, what will happen is that the SLAVE IO_THREAD won't be able to get new statements (queries) from master, but if you eventually have queries that you already get from master(these are saved on your relay log) but didn't execute yet (you can check if you have seconds behind master to see if it is the case), it won't prevent these queries to don't execute.

share|improve this answer
    
If you revoke privileges, make sure you also run an FLUSH PRIVILEGES to tell MySQL to reload the privilege table in memory. –  altmannmarcelo Apr 21 '13 at 20:51
    
You should rarely need to FLUSH PRIVILEGES unless you're modifying the permissions in the MySQL tables directly - if you drop permissions using REVOKE or DROP USER then the server will reload the grant tables automatically. –  Nathan Jolly Apr 22 '13 at 0:00
    
i'll give it a try later today –  HalaKuwatly Apr 22 '13 at 6:25
    
i tried it and actually replication continued on the slave, i had to issue stop slave; and start slave; for it to work! –  HalaKuwatly Apr 22 '13 at 6:46
    
on MySQL Flush Doc Page you can see that REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN does not flush the privileges tables from memory. After execute any of this commands, you still need to run FLUSH PRIVILEGES. –  altmannmarcelo Apr 22 '13 at 9:12
show 1 more 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.