How do I completely disable MySQL replication
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?
To completely disable replication with a master-master setup, you should do the following on each slave:
RESET SLAVE ALL;for MySQL 5.5.16 and later)
- 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.
- Restart mysqld.
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.
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'.
You can verify that the machine is no longer a slave
SHOW SLAVE STATUS \G;
This no longer works. Setting `CHANGE MASTER TO MASTER_HOST=''` now throws an error.
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.
On the slave server(s):
- Run "stop slave" to stop replication.
- Run "reset slave" to tell the slave server to forget it's position in the binary log retrieved from the master server.
- 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.
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?
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.
One answer is here:
*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*
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;