82

I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?

Improve this question

7 Answers 7

Reset to default
104

To completely disable replication with a master-master setup, you should do the following on each slave:

  1. STOP SLAVE;
  2. RESET SLAVE; (Use RESET SLAVE ALL; for MySQL 5.5.16 and later)
  3. Edit the my.cnf and remove any information (if present) which refers to "master-..." or "replicate-..." options. You may not have anything in the my.cnf, since replication can be setup dynamically as well.
  4. Restart mysqld.
Improve this answer
3
  • 1
    if the replication is setup dynamically, step 1 and 2 should be enough.
    – tanyehzheng
    Dec 3, 2013 at 2:27
  • 6
    Until MySQL is restarted it will report the old replication information on SLAVE STATUS queries even after RESET SLAVE command is given. Important to know for monitoring or remote configuration management tools. Confirmed behavior with MySQL 5.5.38 on CentOS 6.5.
    – Chris Laskey
    Jul 16, 2014 at 17:17
  • I do the steps but why that inactive slave is still in list of mtop command? How to remove it from the list?
    – Scott Chu
    Dec 10, 2015 at 3:13
20

I know this is an old question but I found I also has to reset the slave variables. If you use "blah" like suggested, the server will try on startup to find server 'blah'.

Change MASTER to MASTER_HOST='',MASTER_USER='',MASTER_PASSWORD='';

You can verify that the machine is no longer a slave 

SHOW SLAVE STATUS \G;
Improve this answer
4
  • 7
    This no longer works. Setting CHANGE MASTER TO MASTER_HOST='' now throws an error.
    – Gray
    Jul 18, 2012 at 16:15
  • ERROR 1210 (HY000): Incorrect arguments to MASTER_HOST
    – Kazimieras Aliulis
    Jul 18, 2014 at 3:12
  • Works for me on CentOS 6 (MySQL 5.1), after this SHOW SLAVE STATUS returns an empty set, whereas after a RESET SLAVE it still showed some master info.
    – Martijn
    Feb 16, 2015 at 9:23
  • This answer is still valid for MySQL 5.1 users (there still may out there).
    – RolandoMySQLDBA
    Sep 28, 2015 at 14:52
14

On the slave server(s):

  1. Run "stop slave" to stop replication.
  2. Run "reset slave" to tell the slave server to forget it's position in the binary log retrieved from the master server.
  3. Add "skip-slave-start" to my.cnf to prevent replication from starting when you restart MySQL.

There's no need to restart MySQL on either the master or the slave. Complete documentation can be found in section 19 of the MySQL Reference Manual.

I'd recommend leaving the rest of the replication settings in place in case you decide to revert to your previous configuration. That way you'd just have to push the data over and reset the slave position (don't forget to remove skip-slave-start) rather than recreating the setup whole-cloth.

Improve this answer
10

Regardless of the MySQL version, The most complete way to do this is the following

cd /var/lib/mysql
service mysql stop
rm -f master.info relay-*`
service mysql start

This has to work for he latest version because replication setting still linger in RAM for MySQL 5.5.

I just answered a similar question on this subject : How to change a MySQL previous slave to be a master and remove slave status information?

Improve this answer
6

Editing the my.cnf file alone is not sufficient to disable replication. In fact, it is no longer the recommended way of enabling it. Putting entries in the my.cnf file are only effective for the next startup and behave as if you had entered the command into the mysql client:

mysql> change master to master_host='blah', master_user='blah', master_password='blah'...;

Both these methods will create a file in the data directory called master.info. As long as this file exists, the server will try and replicate using the details there. The "RESET SLAVE;" command listed in the first answer will get rid of the master.info file (as well as the relay-log.info file). As mentioned in the first answer, you also want to make sure that you do not have that configuration information in the my.cnf file, otherwise on the next restart of the server, logging will be re-enabled.

Improve this answer
6

I'm adding this to Harrison Fisk's answer:

If you used RESET SLAVE ALL; then restart is not necessary.

Additionally, you may wish to enable events that have been disabled on slave:

select * from information_schema.events where status = 'SLAVESIDE_DISABLED';

For each of them:

alter event <event_name> enable;
Improve this answer
5

One answer is here:

http://www.oops.net.br/~bac/bam/canopy_repl_setup.htm

*Edit the MySQL configuration file: /etc/my.cnf and remove the following 7 lines to the section titled [mysqld]:

port=3306
log-bin
server-id=1
master-host=10.0.0.2
master-user=server_1_repl
master-password=server_1_passwd
master-port=3306*

Restart MySQL.

Improve this answer