How to change a MySQL previous slave to be a master and remove slave status information?

Question

I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine.

What I can't seem to do is to remove the master information on the old-slave which is now the new-master. I see:

mysql> show slave status \G
*************************** 1. row ***************************
           Slave_IO_State: 
              Master_Host: 10.1.2.101
              Master_User: replicationSlave
              Master_Port: 3306
              ...
              Slave_IO_Running: No
              Slave_SQL_Running: No

I've read a lot of MySQL documentation but I still haven't found a way to clear the slave information from the new-master. I've tried:

1. RESET SLAVE which does not seem to clear those settings. [[Actually it does remove the master.info file but not the memory settings. See below.]]
2. CHANGE MASTER TO MASTER_HOST='' which just spits on an error since it was deprecated recently.
3. Checking my.cnf which does not have the master information since they were added programmatically.
4. RESET MASTER because some mysql docs recommended it. That only resets the bin logs.
5. Poking around in the internal MySQL tables to see if I can find the fields to clear.

What is the proper way to do this on MySQL ~5.5.9? Thanks for any help.

---

Edit:

So it turns out that RESET SLAVE removes the master.info file as @RolandoMySQLDBA implied. However, you still need to restart the server before the slave information is removed.

Is there any way to remove this slave information without having to restart mysqld?

Answer 1

In MySQL 5.5.16 and later, you can use RESET SLAVE ALL to do all that RESET SLAVE does and reset the connection parameters from the memory, this way it doesn't require a mysqld restart.

Answer 2

The quickest and dirtiest way to clear slave info from a MySQL instance

• Add skip-slave-start to /etc/my.cnf under [mysqld]
• service mysql stop
• rm -f /var/lib/mysql/master.info /var/lib/mysql/relay-*
• service mysql start
• Remove skip-slave-start from /etc/my.cnf

That should do it for you !!!

This would be necessary because according to MySQL Documentation on RESET SLAVE:

In MySQL 5.5 (unlike the case in MySQL 5.1 and earlier), RESET SLAVE does not change any replication connection parameters such as master host, master port, master user, or master password, which are retained in memory. This means that START SLAVE can be issued without requiring a CHANGE MASTER TO statement following RESET SLAVE.

Thus, replication info is still in memory. A mysql restart is the only way to go.

Answer 3

RESET SLAVE followed by a restart does not clear the slave info as far as phpmyadmin is concerned. You also need to set CHANGE MASTER TO MASTER_HOST=''.

Answer 4

I would recommend maintaining the skip-slave-start command in your config file (‘in /etc/my.cnf’) under your ‘mysqld’ to avoid any overriding of master-slave data. To give you an example- when working in a cloud environment, let’s say an old master crashes and then successfully restarts when you’re your provider fixes whatever issue – the old slave (now new master) will replicate from old master, overriding data before the DBA has a chance to realize this.

BTW, this is also relevant in non-cloud environment. If, let’s say, another admin brings up the old master without coordinating. Also, another issue why it's a good idea to maintain the ‘skip-slave-start’ command even if it's a slave – no automatic replication, meaning you have more control over preventing unpredictable results. :)