====== Changing Masters ======

===== The problem =====

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:

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

<code>
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A -h MASTER-SERVERNAME -u root -p  > ~/dump.sql
</code>

As it is said in the document, take note of the value of MASTER_LOG_FILE and MASTER_LOG_POS:

<code>
head dump.sql -n80 | grep "MASTER_LOG_POS"
</code>

In the slave, edit the config file:

<code>
server-id               = 101
binlog-format       = mixed
log_bin                 = mysql-bin
relay-log               = mysql-relay-bin
log-slave-updates = 1
read-only               = 1
</code> 

Restart future-master. 

Import data: 

<code>
mysql -h localhost -u root -p < .\20150623-total-dump-1114.sql
</code>

Now, in the future-master server we must execute: 

<code>
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;
</code>

The server future-master will begin to replicate the pending changes from the master server. To check 
out the pending tasks it has: 

<code>
show slave status\G
</code>

<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: 

<code>
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
</code>

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:

{{ :mysql:20150623_mysql_step_1.png?200 |}}

And now we want this:

{{:mysql:20150623_mysql_step_3.png?200|}}

For that, we have to: 

  - Stop the master
  - Check that both slaves are up-to-date
  - issue a [[https://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html|change master to]] command to the old slave

==== Stop the master ====

Nothing new under the sun: 

<code>
/etc/rc.d/mysql stop 
</code>

or:

<code>
net stop mysql 
</code>

==== 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 ====

<code>
grant replication slave, replication client on *.* to YOUR-REPLICATION-USERNAME identified by 'YOUR-REPLICATION-PASSWORD';
</code>

==== 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: 

<code>
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)
</code>

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:

<code>
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;
</code>

And I've stopped and started the slave. 

===== Final check =====

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.