DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Zero Trust Network for Microservices With Istio
  • How To Integrate Microsoft Team With Cypress Cloud
  • Micro Frontends on Monorepo With Remote State Management
  • CDNs: Speed Up Performance by Reducing Latency
  1. DZone
  2. Data Engineering
  3. Databases
  4. Disconnecting a replication slave is easier with MySQL 5.5+

Disconnecting a replication slave is easier with MySQL 5.5+

Peter Zaitsev user avatar by
Peter Zaitsev
·
Apr. 21, 13 · Interview
Like (0)
Save
Tweet
Share
7.52K Views

Join the DZone community and get the full member experience.

Join For Free
This post comes from Stephane Combaudon at the MySQL Performance Blog.

It’s not uncommon to promote a server from slave to master. One of the key things to protect your data integrity is to make sure that the promoted slave is permanently disconnected from its old master. If not, it may get writes from the old master, which can cause all kinds of data corruption. MySQL provides the handy RESET SLAVE command. But as we’ll see, its behavior has changed along with the MySQL versions and it’s easy to shoot yourself in the foot if you use it incorrectly. So how do you safely disconnect a replication slave?

In short

  • For MySQL 5.0 and 5.1, run STOP SLAVE, CHANGE MASTER TO MASTER_HOST='' and then RESET SLAVE.
  • For MySQL 5.5 and 5.6, run STOP SLAVE and then RESET SLAVE ALL.
  • For all versions, ban master-user, master-host and master-password settings in my.cnf, this may cause huge problems (it’s anyway no longer supported from MySQL 5.5).

If you want to know more details, please read on!

MySQL 5.0/5.1

First let’s consider MySQL 5.0 and 5.1. RESET SLAVE will remove the master.info and relay-log.info files as well as all the relay log files. This looks great, but does it ensure the replica is disconnected from its master?
Let’s try:

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 127.0.0.1
                Master_User: test
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File:
        Read_Master_Log_Pos: 4
             Relay_Log_File: mysql_sandbox35302-relay-bin.000001
              Relay_Log_Pos: 4
      Relay_Master_Log_File:
           Slave_IO_Running: No
          Slave_SQL_Running: No
          		[...]

This is not expected: instead of removing all settings, some of them are reset to default values. This means that if you run START SLAVE (or if it’s done automatically, for instance when restarting the server without the skip-slave-start option), replication may start again. But as the master position has been deleted, replication will restart at the beginning of the first available binary log, which is very likely to corrupt your data by reexecuting some queries.

Here’s a trick to make RESET SLAVE work as expected: use CHANGE MASTER TO MASTER_HOST='':

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='';
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G
Empty set (0.00 sec)
mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Much better! If we try to restart replication, it fails. However, I don’t like the error message, specifically the ‘fix in config file’ part. What happens if we specify the master-user, master-password, master-host and master-port in the my.cnf file?

# cat my.cnf
[...]
master-user=rsandbox
master-password=rsandbox
master-host=127.0.0.1
master-port=35301
[...]

Let’s disconnect the slave:

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='';
Query OK, 0 rows affected (0.03 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 127.0.0.1
                Master_User: rsandbox
                Master_Port: 35301
              Connect_Retry: 60
            Master_Log_File:
        Read_Master_Log_Pos: 4
             Relay_Log_File: mysql_sandbox35302-relay-bin.000001
              Relay_Log_Pos: 4
      Relay_Master_Log_File:
           Slave_IO_Running: No
          Slave_SQL_Running: No
          [...]

Connection settings are automatically restored, which makes disconnecting the replica impossible. And again, if you restart replication, it will read events from the first available binary log file on the master, which is probably not what you want. So never set master-xxx variables in my.cnf!

From MySQL 5.5

Starting with MySQL 5.5, the situation has slightly changed. First the master-xxx variables are no longer supported, which is a great improvement. But the RESET SLAVE statement also behaves differently:

mysql> stop slave;
Query OK, 0 rows affected (0,01 sec)
mysql > reset slave;
Query OK, 0 rows affected (0,11 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 18675
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql_sandbox18676-relay-bin.000001
                Relay_Log_Pos: 4
        [...]

As stated in the documentation, the connection parameters are still held in memory. In any case, you will be able to restart replication, but again as no replication coordinate is specified, replication will start at the beginning of the first available binary log file, with all the nasty consequences we can imagine.

Even worse, the CHANGE MASTER TO MASTER_HOST='' trick no longer works:

mysql> stop slave;
Query OK, 0 rows affected (0,01 sec)
mysql> change master to master_host='';
ERROR 1210 (HY000): Incorrect arguments to MASTER_HOST

Fortunately, the documentation also specifies that we can use RESET SLAVE ALL to remove all replication-related configuration:

mysql> stop slave;
Query OK, 0 rows affected (0,00 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0,04 sec)
mysql> show slave status\G
Empty set (0,00 sec)

Very good! The command does work as expected without any additional tricks. As soon as you are aware of the difference between RESET SLAVE and RESET SLAVE ALL, disconnecting a replication slave is much easier with MySQL 5.5+.




 

MySQL Replication (computing)

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Zero Trust Network for Microservices With Istio
  • How To Integrate Microsoft Team With Cypress Cloud
  • Micro Frontends on Monorepo With Remote State Management
  • CDNs: Speed Up Performance by Reducing Latency

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: