patternsqlMinor
Change host IP of master in mysql replication
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.
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 :
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_Filevalue (Example : mysql-bin.000007) andExec_Master_Log_Posvalue (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.