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?
Disconnect a Replication Slave
- For MySQL 5.0 and 5.1, run
STOP SLAVE,CHANGE MASTER TO MASTER_HOST=''and thenRESET SLAVE. - For MySQL 5.5 and 5.6, run
STOP SLAVEand thenRESET SLAVE ALL. - For all versions, ban
master-user,master-hostandmaster-passwordsettings 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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 statusG *************************** 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='':
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 statusG 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?
1 2 3 4 5 6 7 | # cat my.cnf [...] master-user=rsandbox master-password=rsandbox master-host=127.0.0.1 master-port=35301 [...] |
Let’s disconnect the slave:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 statusG *************************** 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 statusG *************************** 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:
1 2 3 4 5 | 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:
1 2 3 4 5 6 7 8 | 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 statusG 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+.
Thanks, it always was messed up 😉
Thanks Stephane, I’ve always wondered why RESET SLAVE worked this way, now I’ll use RESET SLAVE ALL!
But “show master status” still has entries pertaining to log position.
How do you get rid of that?
Jake, as long as binary logging is enabled, “show master status” will return something.
But this is not related to a server being a replica or not: only “show slave status” will give you that information.
Stephane,
I have 3 DB servers. The order of replication is as follows:
DB1 —->replicates to—-> DB2 —->replicates to —->DB3
DB1 is the production server. I want to promote DB2 as the production/master database (DB1 is going away), but I STILL want DB3 to replicate from DB2.
If I run RESET SLAVE ALL on DB2, I assume DB3 will still be able to replicate from DB2… Correct?
Thanks!
Werd,
Yes, correct. RESET SLAVE ALL will only make DB2 forget that it has been a slave of DB1
This might be completely unrelated. please pardon my ignorance as i am new to mysql world and learning as much as possible.
my setup is D01(M) –> D02 (S)
There are 6 databases on D01. There was an issue and replication broke in test env. before it was looked into (5 days)
Q — is there a clean way to catch up with replication for just 1 databases. please note that we are on Percona 5.6
Khan,
You can try using replication filters, something like replicate-wild-do-table=db1.%
See http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_replicate-wild-do-table and http://dev.mysql.com/doc/refman/5.6/en/replication-rules.html
Thanks for this amazing post….
Is there any easy way to tell the slave to start replicating from the current binary log position of the master. I.e. remember who the master is and replicate from where the master is currently at.
Hello We have 2 MYSQL database instances one is production and other is live replication server of the production server. I am getting an error “Could not execute Delete_rows event on table db.tablename; Can’t find record in ‘tablename’ Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000098, end_log_pos 458526” and “Slave_SQL_Running is No” .Can you help me get rid of this error?