Table of Contents
I have a mysql master database with an slave. I want to change the master server. Downtime it is not a problem, however, I will try to reduce downtime.
My servers, for the sake of this example will be named: master, future-master, slave.
First: make future-master to become an slave of master
For that, I've found this:
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A -h MASTER-SERVERNAME -u root -p > ~/dump.sql
As it is said in the document, take note of the value of MASTER_LOG_FILE and MASTER_LOG_POS:
head dump.sql -n80 | grep "MASTER_LOG_POS"
In the slave, edit the config file:
server-id = 101 binlog-format = mixed log_bin = mysql-bin relay-log = mysql-relay-bin log-slave-updates = 1 read-only = 1
mysql -h localhost -u root -p < .\20150623-total-dump-1114.sql
Now, in the future-master server we must execute:
CHANGE MASTER TO MASTER_HOST='MASTER',MASTER_USER='replicant',MASTER_PASSWORD='<<slave-server-password>>', MASTER_LOG_FILE='<<value from above>>', MASTER_LOG_POS=<<value from above>>; START SLAVE;
The server future-master will begin to replicate the pending changes from the master server. To check out the pending tasks it has:
show slave status\G
<WRAP center round important 60%> It's important that that \G must be written as-is: an uppercase G and no space between the \ and the last character of the command, because otherwise it won't be understood. </WRAP>
The synchronization will be finished when –for instance– this value shows this:
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
I've compared the value
Read_Master_Log_Pos with other slave just to
make sure all the changes were updated.
Second: make the slave to change the master
We have this:
And now we want this:
For that, we have to:
- Stop the master
- Check that both slaves are up-to-date
- issue a change master to command to the old slave
Stop the master
Nothing new under the sun:
net stop mysql
Check that both slaves are up-to-date
Among the information that
show slave status\G displays, I've chosen Read_Master_Log_Pos to make sure
both database servers are in the same position.
Create (if it does not exist) a replication user in future-master
grant replication slave, replication client on *.* to YOUR-REPLICATION-USERNAME identified by 'YOUR-REPLICATION-PASSWORD';
Check master status an take note of the master log file and master log position
We have to issue a
show master status command to get the file and log position of our new master:
mysql> show master status\G *************************** 1. row *************************** File: YOUR-MASTER-LOG-FILE Position: YOUR-MASTER-LOG-POS Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
With this two values, YOUR-MASTER-LOG-FILE and YOUR-MASTER-LOG-POS, we can move to the slave and issue a change master to command.
Issue a change master command
In the slave server I've issued a
change master to command:
stop slave; change master to master_host='future-master', master_port=3306, master_user='YOUR-REPLICATION-USERNAME', master_password='YOUR-REPLICATION-PASSWORD', master_log_file = 'YOUR-MASTER-LOG-FILE', master_log_pos = YOUR-MASTER-LOG-POSITION; start slave;
And I've stopped and started the slave.
You can double check that your slave is working with the new configuration just by the method of looking one change made in the new master.