How to upgrade MySQL Slave (Replica) to become the Master in Replication ?
This blog post describes how to change a MySQL slave to become a MySQL master and optionally to set the old MySQL master as a slave for the new MySQL master.
A typical scenario of when this is useful is if you have set up a new version of MySQL as a slave, for example for testing, and want to upgrade your master to the new version.
In MySQL replication, a slave should be of a version same or newer than the master. Because of this, one should first upgrades all slaves to the latest version before changing a slave to be a master. In some cases one can have a slave to be of an older version than the master, as long as one doesn’t execute on the master any SQL commands that the slave doesn’t understand. This is however not guaranteed between all major MySQL versions.
Stopping the Original Master
First one needs to take down the original master in such a way that the slave has all information on the master. We have to set the master to read only to ensure that there are no new updates on the master:
|
1 |
FLUSH TABLES WITH READ LOCK; |
Note: You should not disconnect this session as otherwise the read lock will disappear and you have to start from the beginning.
Then you should check the current position of the master:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mysql-bin.000003 | 781 | | | +--------------------+----------+--------------+------------------+ SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | 8-3-1 | +--------------------------+ |
Please wait until you have the same position on the slave:
|
1 2 3 4 5 6 7 8 |
SHOW SLAVE [connection_name] STATUS; +-------------------+-------------------+ Master_Log_File | mysql-bin.000003 + Read_Master_Log_Pos | 781 + Exec_Master_Log_Pos | 781 + ... Gtid_IO_Pos 8-3-1 + +-------------------+-------------------+ |
Please confirm Master_Log_File and Exec_Master_Log_Pos matches the master, it acknowledges that all transactions has been committed on the slave.
Note that Gtid_IO_Pos on slave can contain many different positions separated with ‘,’ if the slave has been connected to many different masters. What is important is that all the sequences that are on the master is also on the slave.
When slave is up to date, you can then take the MySQL MASTER down. This should be on the same connection where you executed FLUSH TABLES WITH READ LOCK.
|
1 |
systemctl stop mysql |
|
1 |
service mysql stop |
Preparing the Slave to be a Master
Stop all old connections to the old master(s) and reset read only mode, if you had it enabled. You also want to save the values of SHOW MASTER STATUS and gtid_binlog_pos, as you may need these to setup new slaves.
|
1 2 3 4 5 |
STOP ALL SLAVES; RESET SLAVE ALL; SHOW MASTER STATUS; SELECT @@global.gtid_binlog_pos; SET @@global.read_only=0; |
Reconnect Other Slaves to the New Master
On the other slaves you have point them to the new master (the slave you promoted to a master).
|
1 2 3 4 5 |
STOP SLAVE [connection_name]; CHANGE MASTER [connection_name] TO MASTER_HOST="new_master_name", MASTER_PORT=3306, MASTER_USER='root', MASTER_USE_GTID=current_pos, MASTER_LOG_FILE="XXX", MASTER_LOG_POS=XXX; START SLAVE; |
The XXX values for MASTER_LOG_FILE and MASTER_LOG_POS should be the values you got from the SHOW MASTER STATUS command you did when you finished setting up the slave.
Changing the Old Master to be a Slave
Now you can upgrade the new master to a newer version of MariaDB and then follow the same procedure to connect it as a slave.
When starting the original master, it’s good to start the mysqld executable with the –with-skip-slave-start and –read-only options to ensure that no old slave configurations could cause any conflicts.
For the same reason it’s also good to execute the following commands on the old master (same as for other slaves, but with some extra security). The read_only option below is there to ensure that old applications doesn’t by accident try to update the old master by mistake. It only affects normal connections to the slave, not changes from the new master.
|
1 2 3 4 5 6 7 8 |
set @@global.read_only=1; STOP ALL SLAVES; RESET MASTER; RESET SLAVE ALL; CHANGE MASTER [connection_name] TO MASTER_HOST="new_master_name", MASTER_PORT=3306, MASTER_USER='root', MASTER_USE_GTID=current_pos, MASTER_LOG_FILE="XXX", MASTER_LOG_POS=XXX; START SLAVE; |
References
