How to disable MYSQL Replication?

All we need is an easy explanation of the problem, so here it is.

I have 2 databases which are replicating each other with no issues. I required a test database server and to avoid installing OS etc, I started a new image of one of the servers (with different hostname, IPs etc).

Before switching on the interfaces I would like to be sure that the new test database will not send or receive and replication data from the other live servers. One easy solution is to remove IP routing towards the live servers, but I would like to disable replication too to be 100% sure. Can someone pls guide me how to disable replication so that the test server will not send any updates and not receive any updates?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

MySQL replication does not send anything. It’s the replicas that must pull data.

This means if you haven’t configured any replicas to connect to your test server, there’s no way it can send any updates to other MySQL instances.

But to answer your question, you could:

  • Disable the binary log on the test server. Then there would be no logs for any replica to pull even if they tried. On MySQL 8.0, you would set the server option skip-log-bin in your my.cnf file before you start the instance. On older versions, there is no skip prefix supported, so if you just don’t enable log-bin in the my.cnf file (or comment it out if it’s there).

  • Identify the MySQL user that your replicas would use to connect, and use REVOKE REPLICATION SLAVE to remove the privilege from that user on your test instance. Or change the password, or just DROP USER.

That said, I also recommend configuring your network routing so test servers and live servers can’t reach each other at all. Not only for the sake of MySQL replication, but any other contact.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply