The Ultimate Guide to High Availability with MariaDB

Delayed Replication

You are viewing an old version of this article. View the current version here.

The terms master and slave have historically been used in replication, and MariaDB has begun the process of adding primary and replica synonyms. The old terms will continue to be used to maintain backward compatibility - see MDEV-18777 to follow progress on this effort.

Delayed replication allows specifying that a replica should lag behind the primary by (at least) a specified amount of time (specified in seconds). Before executing an event, the replica will first wait, if necessary, until the given time has passed since the event was created on the primary. The result is that the replica will reflect the state of the primary some time back in the past.

The default is zero, or no delay, and the maximum value is 2147483647, or about 68 years.

Delayed replication is enabled using the MASTER_DELAY option to CHANGE MASTER:

  CHANGE MASTER TO master_delay=3600;

A zero delay disables delayed replication. The replica must be stopped when changing the delay value.

Fields in SHOW SLAVE STATUS are associated with delayed replication:

  1. SQL_Delay: This is the value specified by MASTER_DELAY in CHANGE MASTER (or 0 if none).
  2. SQL_Remaining_Delay: When the replica is delaying the execution of an event due to MASTER_DELAY, this is the number of seconds of delay remaining before the event will be applied. Otherwise, the value is NULL.
  3. Seconds_Behind_Master:
    • If replication is not active, the value is NULL.
    • If all events has been executed and slave is now idle (waiting for new events), the value is 0.
    • If using parallel replication:
      • When starting to execute a new delayed event on an idle slave the value is clock_time_on_slave - clock_when_started_execution_of_the_event_on_master - clock_difference_between_master_and_slave
      • When event is committed the value is updated to: clock_time_on_slave - clock_when_ending_execution_of_the_event_on_master - clock_difference_between_master_and_slave
      • As the used times are only updated while execution an event, the value of seconds_behind_master can be much bigger than SQL_Delay. For example if SQL_delay is 10 hours and the master sends a new event after not having executed anything for 20 hours, Seconds_behind_master will be 30 hours. When event starts to execute, Seconds_behind_master will be changed to 10 hours. After execution Seconds_behind_master will be set to the value above. This value will be growing until next event is executed and Seconds_behind_master is updated again.
    • If not using parallel replication the value is updated when starting to execute an event. The value is set to: clock_time_on_slave - clock_when_started_execution_of_the_event_on_master - clock_difference_between_master_and_slave. Note that in this case the clock is at start of event, not at end of event like in parallel replication.
  4. Slave_SQL_Running_State. This shows the state of the SQL driver threads, same as in SHOW PROCESSLIST. When the replica is delaying the execution of an event due to MASTER_DELAY, this fields displays: "Waiting until MASTER_DELAY seconds after master executed event".

When using older versions prior to MariaDB 10.2.3, a 3rd party tool called pt-slave-delay can be used. It is part of the Percona Toolkit. Note that pt-slave-delay does not support MariaDB multi-channel replication syntax.

Comments

Comments loading...
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.