How do I completely disable MySQL replication

Question

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?

Answer 1

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.

Answer 2

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

1. STOP SLAVE;
2. RESET SLAVE;
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.

Answer 3

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.

Answer 4

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.