HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Change host IP of master in mysql replication

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
replicationmysqlmasterhostchange

Problem

I have one master and one slave. Suppose IP of master is changed but the physical server is same. Now I want to update the new IP (of the master) at the slave server. At the slave server master_info_repository is set to TABLE. I want to resume the replication process from exactly the position where it has paused due to IP change. How to do this? mysql version of slave server is : 14.14 Distrib 5.7.30 and O/S is ubuntu 18.04.

Solution

After a long internet surfing I got a thing. MASTER_LOG_FILE will take Relay_Master_Log_File and MASTER_LOG_POS will take Exec_Master_Log_Pos as their respective value in CHANGE MASTER TO statement. In the Exec_Master_Log_Pos section of mysql docs it is clearly stated. I have found another article which says the same thing :

When you’re using CHANGE MASTER TO to set start position for the slave
you’re specifying position for SQL thread and so you should use
Relay_Master_Log_File:Exec_Master_Log_Pos. Otherwise you’re going to
ruin your replication.

It does not help SHOW MASTER STATUS has Master_Log_File value and
CHANGE MASTER TO accepts Master_Log_File parameter, while you actually
want to use Relay_Master_Log_File instead. This naming was left from
pre MySQL 4.0 era and it still causes confusion so many years later.

Conclusion :

  • STOP SLAVE;



  • SHOW SLAVE STATUS \G



  • note down Relay_Master_Log_File value (Example : mysql-bin.000007) and Exec_Master_Log_Pos value (Example : 775)



  • CHANGE MASTER TO MASTER_HOST='', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=775;



  • START SLAVE;

Context

StackExchange Database Administrators Q#280964, answer score: 6

Revisions (0)

No revisions yet.