MySQL Replication and IP Address change
- Jonathan Schmale
If you need to change the IP address of a MySQL master server the following should be helpful. Please read through the whole document to get a complete view of the process.
Summary of Steps
- Stop IIS on main server
- Stop players and readers
- Stop slave process
- Let them update the IP addresses
- Update the slave with the masters new IP address (details below)
- Update the players and readers with the new IP
- Restart
- players
- readers
- IIS
Master Database Setup
Replication User
Make sure that replication user will be accessible to the slave machines after the IP address change. Check the definition of the user (default is blbackup) for any host limitations that would now prevent access to it.
SHOW GRANTS FOR 'blbackup';
A sample result will be:
GRANT SELECT, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'blbackup'@'%' IDENTIFIED BY PASSWORD '...'
In this example the blbackup user is not restricted but instead uses the % wildcard allowing access from any host IP address so no change to this example user will be required.
Slave Database Setup
You need to stop replication and identify the details needed to identify where to resume replication after updating the slave with the new master IP.
STOP SLAVE; SHOW SLAVE STATUS;
In the results of the slave status command look for 2 settings:
- Relay_Master_Log_File (example: mysql-bin.000023)
- Exec_Master_Log_Pos (example: 11622542)
We use the values of these fields in the next statement like this: (where the master_host value is the new IP address of the master)
CHANGE MASTER TO MASTER_HOST='10.0.1.15', MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=11622542; START SLAVE;
The final step should be to issue a SHOW SLAVE STATUS command again (even a couple times) to verify that the replication has resumed successfully.
SHOW SLAVE STATUS;
References:
