Delayed Replication

The terms master and slave have historically been used in replication, but the terms terms primary and replica are now preferred. The old terms are used throughout the documentation, and in MariaDB commands, although MariaDB 10.5 has begun the process of renaming. The documentation will follow over time. See MDEV-18777 to follow progress on this effort.

MariaDB starting with 10.2.3

Delayed replication was introduced in MariaDB 10.2.3.

When using older versions, 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.

Delayed replication allows specifying that a replication slave should lag behind the master by (at least) a specified amount of time (specified in seconds). Before executing an event, the slave will first wait, if necessary, until the given time has passed since the event was created on the master. The result is that the slave will reflect the state of the master 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 slave must be stopped when changing the delay value.

Three 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 slave 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. Slave_SQL_Running_State. This shows the state of the SQL driver threads, same as in SHOW PROCESSLIST. When the slave is delaying the execution of an event due to MASTER_DELAY, this fields displays: "Waiting until MASTER_DELAY seconds after master executed event".
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.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/delayed-replication/