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.

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.