User Tools

Site Tools


mysql:changemasters

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

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

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.

Second: make the slave to change the master

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

Stop the master

Nothing new under the sun:

/etc/rc.d/mysql stop 

or:

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.

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.

mysql/changemasters.txt · Last modified: 2015/06/24 22:39 by rlunaro