This is an old revision of the document!
Table of Contents
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
<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.
Second: make the slave to change the master
We have this:
And now we want this:
For that, we have to:
- Stop the master
- Check that both slaves are up-to-date
- issue a change master to command to the old slave
This explains how to change the master of a running mysql database by the mechanism of:
- Create a new slave wihtout downtime
- Stop the current master and start a new master (the last slave)
https://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html