Changing a Replica to Become the Primary - Source

Revision: 114564
User: Michael Widenius
Date: 2022-03-13 19:44

<<include slug="replica_slave">>
<<toc>>
This article describes how to change a replica to become a primary and optionally to set the old primary as a replica for the new primary.

A typical scenario of when this is useful is if you have set up a new
version of MariaDB as a replica, for example for testing, and want to
upgrade your primary to the new version.

In MariaDB replication, a replica should be of a version same or newer than the primary. Because of this, one should first upgrades all replicas to the latest version before changing a replica to be a primary.  In some cases one can have a replica to be of an older version than the primary, as long as one doesn't execute on the primary any SQL commands that the replica doesn't understand. This is however not guaranteed between all major MariaDB versions.

Note that in the examples below, <<code>>[connection_name]<</code>> is used as the [[multi-source-replication|name of the connection]].  If you are not using named connections you can ignore this.

=== Stopping the Original Master.

First one needs to take down the original primary in such a way that the replica
has all information on the primary.

If you are using [[semisynchronous-replication|Semisynchronous Replication]] you can just stop the server with the [[shutdown|SHUTDOWN]] command as the replicas should be automatically up to date.

If you are using [[maxscale|MariaDB MaxScale proxy]], then you [[https://mariadb.com/resources/blog/mariadb-maxscale-2-2-introducing-failover-switchover-and-automatic-rejoin|can use MaxScale]] to handle the whole process of taking down the primary and replacing it with one of the replicas.

If neither of the above is true, you have to do this step manually:

==== Manually Take Down the Primary

First we have to set the primary to read only to ensure that there are no new updates on the primary:

<<code>>
FLUSH TABLES WITH READ LOCK;
<</code>>

Note that 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 primary:

And wait until you have the same position on the replica:
(The following should be excepted on the replica)

<<code>>
SHOW SLAVE [connection_name] STATUS;
+-------------------+-------------------+
Master_Log_File     | narttu-bin.000003 +
Read_Master_Log_Pos | 343               +
Exec_Master_Log_Pos | 343               +
...
Gtid_IO_Pos          0-1-2              +
+-------------------+-------------------+
<</code>>

The most important information to watch are ##Master_Log_File## and
##Exec_Master_Log_Pos## as when this matches the primary, it signals
that all transactions has been committed on the replica.

Note that ##Gtid_IO_Pos## on replica can contain many different positions
separated with ',' if the replica has been connected to many different
primaries. What is important is that all the sequences that are on the
primary is also on the replica.

When replica is up to date, you can then take the **PRIMARY** down. This should be on the same connection where you executed [[flush|FLUSH TABLES WITH READ LOCK]].

<<code>>
SHUTDOWN;
<</code>>

=== Preparing the Replica to be a Primary

Stop all old connections to the old primary(s) and reset **read only
mode**, if you had it enabled. You also want to save the values of
[[show-master-status|SHOW MASTER STATUS]] and ##gtid_binlog_pos##, as
you may need these to setup new replicas.

<<code>>
STOP ALL SLAVES;
RESET SLAVE ALL;
SHOW MASTER STATUS;
SELECT @@global.gtid_binlog_pos;
SET @@global.read_only=0;
<</code>>

=== Reconnect Other Replicas to the New Primary

On the other replicas you have point them to the new primary (the replica you promoted to a primary).

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

The <<code>>XXX<</code>> 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 replica.

=== Changing the Old Primary to be a Replica

Now you can upgrade the new primary to a newer version of MariaDB and then
follow the same procedure to connect it as a replica.

When starting the original primary, it's good to start the ##mysqld##
executable with the ##--with-skip-slave-start## and ##--read-only##
options to ensure that no old replica configurations could cause any
conflicts.

For the same reason it's also good to execute the following commands
on the old primary (same as for other replicas, 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 primary by mistake.
It only affects normal connections to the replica, not changes from the
new primary.

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

=== Moving Applications to Use New Primary

You should now point your applications to use the new primary.
If you are using the [[maxscale|MariaDB MaxScale proxy]], then you don't
have to do this step as MaxScale will take care of sending write request
to the new primary.

=== See Also
* [[change-master-to|CHANGE MASTER TO]] command
* [[https://mariadb.com/resources/blog/mariadb-maxscale-2-2-introducing-failover-switchover-and-automatic-rejoin|MaxScale Blog about using Switchover to swap a primary and replica]]
* [[https://www.percona.com/blog/2015/12/01/upgrade-master-server-minimal-downtime|Percona blog about how to upgrade replica to primary]]

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Changing a Replica to Become the Primary - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB