MariaDB GTID current_pos vs slave_pos

gtidmariadbreplication

In a standard master-slave setup using MariaDB (10.3 at this time) and GTID (Global Transaction ID), it is unclear wether to use current_pos or slave_pos.

Reminder from the documentation:

  • Using the value current_pos causes the slave to set its position based on the gtid_current_pos system variable. The slave takes the position given to it by the master server.
  • Using the value slave_pos causes the slave to instead use the gtid_slave_pos system variable. With this method, the slave takes into account transactions present in its own Binary Log.

I think I understand the difference but what is the recommended option?

To the extent that both exist, one might be better for one use case and the other for another use case but the documentation does not clearly state which one should be used in which case…

Which of these options would you use for a very common case of master-slave replication?

EDIT 2019-01-18: Context

The context that led me to ask myself whether I should use current_pos or slave_pos:

I recently upgraded some MariaDB clusters (master-slave setups) from MariaDB 10.1 or 10.2 to MariaDB 10.3 on Debian Stretch, using APT.

In practice, I always proceed like this:

  1. Upgrade slave(s) to 10.3 (using an apt dist-upgrade)
  2. Run mysql_upgrade
  3. Switchover master (using Signal18 Replication-manager)
  4. Upgrade the old master (that became a slave at the previous step)
  5. Switchover master again to return to the original configuration

However, during recent updates, I encountered some failures at Slave Start after running a mysql_upgrade command. It did not found binlogs using GTID current position.

This happens, I believe, when there has be No transaction on the master since the last master switchover. In this case, switching GTID mode from current_pos to slave_pos fix the problem…

Then, should I definitely use slave_pos? However, even in this case, Replication-Manager forces GTID mode to current_pos after a switchover…

Best Answer

Use current_pos to switchover master. Use slave_pos for a regular replication.

current_pos - last change in GTID domain

slave_pos - last replicated change applied by SQL thread or parallel workers on the replica.

So, imagine your ordinary replica (node2) switched to a master. slave_pos not incrementing, because changes are applied directly without any replication. Then you need to reconfigure cluster and make the new master become replica again.

What you will see on node2?

SHOW global variables like '%pos%'?

you will see, that slave_pos stopped incrementing on the moment of switchover, current_pos incrementing correctly.

In order node2 to become replica you must use current_pos, after the workload switched to a new master.

Summarizing:

slave_pos

  • use this option when you don't pretend to write to the replica
  • you don't want the transaction id part of the GTID to increment on the slave
  • you are adding a new slave to the cluster and would like to start replicating from the GTID.slave_pos

current_pos

  • use this when you do write to the slave and want to increment GTID transaction id
  • when a master need to become a new slave
Related Question