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:

    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.

    if the replication is setup dynamically, step 1 and 2 should be enough.

    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 do the steps but why that inactive slave is still in list of mtop command? How to remove it from the list?

  • 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


    This no longer works. Setting `CHANGE MASTER TO MASTER_HOST=''` now throws an error.

    ERROR 1210 (HY000): Incorrect arguments to MASTER_HOST

    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.

    This answer is still valid for MySQL 5.1 users (there still may out there).

  • 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.

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

    cd /var/lib/mysql
    service mysql stop
    rm -f 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 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 file (as well as the 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]:


    Restart MySQL.

  • 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 where status = 'SLAVESIDE_DISABLED';

    For each of them:

    alter event <event_name> enable;

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM