I’m upgrading our MySQL master/slave setup and am moving it to new (virtual) hardware in our cloud environment. One of the things I did last night was moving the MySQL slaves to a new master that I had prepared in the new environment. This post describes how I connected the slaves to their new master in the cloud.
First, you’ll need to make sure the new master has the same data as the old one.
1. Make sure no more updates occur on the old master
2. Create a sql dump of the master using mysqldump
3. Import that dump into the new master using mysql cmd line tool
At this point both masters should have the same data.
4. Now, shut down the old master as it can be retired 😉
5. Before allowing write access to the new master, note it’s position by executing this query:
mysql> show master status\G;
File: mn-bin.000005
Position: 11365777
Binlog_Do_DB: database_name
Binlog_Ignore_DB:
1 row in set (0.00 sec)
We’ll need this information later on when instructing the slaves to connect to their new master.
6. It’s now safe to allow write access again to the new master
7. Do this on any slave, it will connect it to the new master:
CHANGE MASTER TO
master_host=’master_hostname’,
master_user=’replicate_user’,
master_password=’password’,
master_log_file=’log-bin.000005‘,
master_log_pos= 11365777
Note the ‘master_log_file’ and ‘master_log_pos’. Their values are the ones we selected from the master at step 5. Then check if it worked (allow a few seconds to connect):
mysql> show slave status\G;
Look for these lines, they should say ‘Yes’:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
And the status should be:
Slave_IO_State: Waiting for master to send event
That’s it, the slave is now connected to a new master. Test it by updating the master, and checking whether the slave receives the update too.
Remi Bergsma's blog 