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.
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
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.
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.
We have this:
And now we want this:
For that, we have to:
Nothing new under the sun:
net stop mysql
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.
grant replication slave, replication client on *.* to YOUR-REPLICATION-USERNAME identified by 'YOUR-REPLICATION-PASSWORD';
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.
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.