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 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?

share|improve this question

migrated from stackoverflow.com Jul 18 '12 at 20:56

This question came from our site for professional and enthusiast programmers.

add comment

7 Answers 7

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.
share|improve this answer
    
if the replication is setup dynamically, step 1 and 2 should be enough. –  tanyehzheng Dec 3 '13 at 2:27
    
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 at 17:17
add comment

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 start up 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;

share|improve this answer
    
This is the BEST solution imo! –  Greg Miernicki Apr 2 '12 at 17:27
4  
This no longer works. Setting CHANGE MASTER TO MASTER_HOST='' now throws an error. –  Gray Jul 18 '12 at 16:15
    
ERROR 1210 (HY000): Incorrect arguments to MASTER_HOST –  Kazimieras Aliulis Jul 18 at 3:12
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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?

share|improve this answer
add comment

I found out what the problem is..

http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/

Its "safer" to use replicate-wild-ignore-table

share|improve this answer
add comment

Your Answer

 
discard

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