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:

http://plusbryan.com/mysql-replication-without-downtime

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

Restart future-master.

Import data:

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.

We have this:

And now we want this:

For that, we have to:

1. Stop the master
2. Check that both slaves are up-to-date
3. issue a change master to command to the old slave

Nothing new under the sun:

/etc/rc.d/mysql stop 

or:

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.

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';

And I've stopped and started the slave.