3

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.

Improve this question

2 Answers 2

Reset to default
6

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 :

  1. STOP SLAVE;
  2. SHOW SLAVE STATUS \G
  3. note down Relay_Master_Log_File value (Example : mysql-bin.000007) and Exec_Master_Log_Pos value (Example : 775)
  4. CHANGE MASTER TO MASTER_HOST='<New IP of Master>', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=775;
  5. START SLAVE;
Improve this answer
1
  • Thanks you! It's working solution.
    – Rodion V
    Sep 15 at 17:20
1

As you only want to change the ip and it is the same server

On the slave you run

SHOW SLAVE STATUS

then you copy

Relay_Master_Log_File for example mysql-bin.000001
Exec_Master_Log_Pos for example 111111

then you stop the slave

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='192.168.1.1',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=111111;

START SLAVE;

192.168.1.1 is of course only a example

uf you need to change more things, please look at the manual

Improve this answer
5
  • The link you referred says that : If you specify the MASTER_HOST or MASTER_PORT option, the replica assumes that the source is different from before (even if the option value is the same as its current value.) In this case, the old values for the source's binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.. I stopped slave, then delete tables from master, then changed IP of master, then applied your method. I saw error.
    – srvnk
    Dec 5, 2020 at 16:30
  • Cause of error : Slave started with log position 4. At log position 4 those tables were exist in master. So slave tried to create those tables at slave and faced error tables are already exist.
    – srvnk
    Dec 5, 2020 at 16:32
  • i am sorry the log file and the position ahave to be read ´before you stopped it, so the default values are wrong.
    – nbk
    Dec 5, 2020 at 16:56
  • I need some elaboration on your method. I am no pro in DB. If you use Relay log then why why don't you use Relay log pos??
    – srvnk
    Dec 5, 2020 at 16:59
  • See the accepted answer - you should use Exec_Master_Log_Pos not Read_Master_Log_Pos.
    – andy250
    Apr 1 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.