Replication is a feature allowing the contents of one or more servers (called primaries) to be mirrored on one or more servers (called replicas).

You can exert control over which data to replicate. All databases, one or more databases, or tables within a database can each be selectively replicated.

The main mechanism used in replication is the binary log. If binary logging is enabled, all updates to the database (data manipulation and data definition) are written into the binary log as binlog events. Replicas read the binary log from each primary in order to access the data to replicate. A relay log is created on the replica, using the same format as the binary log, and this is used to perform the replication. Old relay log files are removed when no longer needed.

A replica server keeps track of the position in the primary's binlog of the last event applied on the replica. This allows the replica server to re-connect and resume from where it left off after replication has been temporarily stopped. It also allows a replica to disconnect, be cloned and then have the new replica resume replication from the same primary.

Primaries and replicas do not need to be in constant communication with each other. It's quite possible to take servers offline or disconnect from the network, and when they come back, replication will continue where it left off.

Replication Uses

Replication is used in a number of common scenarios. Uses include:

• Scalability. By having one or more replicas, reads can be spread over multiple servers, reducing the load on the primary. The most common scenario for a high-read, low-write environment is to have one primary, where all the writes occur, replicating to multiple replicas, which handle most of the reads.

• Data analysis. Analyzing data may have too much of an impact on a primary server, and this can similarly be handled on a replica, while the primary continues unaffected by the extra load.

• Backup assistance. Backups can more easily be run if a server is not actively changing the data. A common scenario is to replicate the data to a replica, which is then disconnected from the primary with the data in a stable state. Backup is then performed from this server. See Replication as a Backup Solution.

• Distribution of data. Instead of being connected to a remote primary, it's possible to replicate the data locally and work from this data instead.

Common Replication Setups

Standard Replication

• Provides infinite read scale out.

• Provides high-availability by upgrading replica to primary.

• Setting up standard replication

Ring Replication

• Provides read and write scaling.

• Doesn’t handle conflicts.

• If one primary fails, replication stops.

• More about Multi-master ring replication

Ring Replication with slaves

• Provides read and write scaling.

• Doesn’t handle conflicts.

• If one primary fails, replication stops.

• More about Multi-master ring replication

Ring Replication with replication through slaves

• Provides read and write scaling.

• Doesn’t handle conflicts.

• If one primary fails, replication stops.

• More about Multi-master ring replication

Star Replication

• Provides read and write scaling.

• Doesn’t handle conflicts.

• Have to use replication filters to avoid duplication of data.

• MariaDB Galera Cluster, which is a virtually synchronous multi-primary (multi-master) cluster for MariaDB, has a similar configuration and can handle conflicts.

Multi-Source Replication

• Allows you to combine data from different sources.

• Different domains executed independently in parallel on all replicas.

• More about Multi-Source replication

Cross-Version Replication Compatibility

The following table describes replication compatibility between different MariaDB Server versions. In general, the replica should be of the same or a later version. The constraint also applies to minor/patch releases:

• ✅: This combination is supported.

• ⛔: This combination is not supported.

Note: where it is not officially supported to replicate to a server with a lesser minor version, replication can still be safe for:

• DMLs logged in ROW binlog_format, and

• DMLS logged in STATEMENT format and DDLs where neither use features that do not yet exist on the replica

provided the configurations for each server allow for consistent behavior in the execution of the events (i.e. the execution of the event should not be reliant on newer configuration variables, character sets/collations, etc, that don't exist on the replica). Additionally note, if binlog_format=MIXED, it may be possible that the higher-versioned server (primary) may consider it safe to log a transaction using STATEMENT binlog format, while the older-versioned replica categorizes it as unsafe, which will result in an error while the replica tries to execute the transaction. See this page for more details on unsafe statements.

For replication compatibility details between MariaDB and MySQL, see MariaDB versus MySQL - Compatibility: Replication Compatibility.

See Also

• Setting Up Replication

• Replication Compatibility Between MariaDB and MySQL

• MariaDB Galera Cluster and M/S replication (video)

_{This page is licensed: CC BY-SA / Gnu FDL}

Replication SQL statements can be found in the Reference section.

Getting replication working involves steps on both the master server/s and steps on the replica server/s.

Setting up a Replication Replica with MariaDB-Backup

If you would like to use mariadb-backup to set up a replication slave, then you might find the information at Setting up a Replication Replica with MariaDB-Backup helpful.

Versions

In general, when replicating across different versions of MariaDB, it is best that the master is an older version than the slave. MariaDB versions are usually backward compatible, while of course older versions cannot always be forward compatible. See also Replicating from MySQL Master to MariaDB Replica.

Configuring the Master

• Enable binary logging if it's not already enabled. See Activating the Binary Log and Binary log formats for details.

• Give the master a unique server_id. All slaves must also be given a server_id. This can be a number from 1 to 232-1, and must be unique for each server in the replicating group.

• Specify a unique name for your replication logs with --log-basename. If this is not specified your host name will be used and there will be problems if the hostname ever changes.

• Slaves will need permission to connect and start replicating from a server. Usually this is done by creating a dedicated slave user, and granting that user permission only to replicate (REPLICATION SLAVE permission).

Example Enabling Replication for MariaDB

Add the following into your my.cnf file and restart the database.

[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed

The server id is a unique number for each MariaDB/MySQL server in your network.binlog-format specifies how your statements are logged. This mainly affects the size of the binary log that is sent between the Master and the Replicas.

Then execute the following SQL with the mysql command line client:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

Example Enabling Replication for MySQL

If you want to enable replication from MySQL 5.7 or earlier to MariaDB, you can do it in almost the same way as between MariaDB servers. The main difference is that MySQL doesn't support log-basename.

[mysqld]
log-bin
server_id=1

For replication from MySQL 8.0 to MariaDB requires slight more configurations.

Settings to Check

There are a number of options that may impact or break replication. Check the following settings to avoid problems.

• skip-networking. If skip-networking=1, the server will limit connections to localhost only, and prevent all remote slaves from connecting.

• bind-address. Similarly, if the address the server listens for TCP/IP connections is 127.0.0.1 (localhost), remote slaves connections will fail.

Configuring the Replica

• Give the slave a unique server_id. All servers, whether masters or replicas, are given a server_id. This can be a number from 1 to 232-1, and must be unique for each server in the replicating group. The server will need to be restarted in order for a change in this option to take effect.

Getting the Master's Binary Log Co-ordinates

Now you need prevent any changes to the data while you view the binary log position. You'll use this to tell the slave at exactly which point it should start replicating from.

• On the master, flush and lock all tables by running FLUSH TABLES WITH READ LOCK. Keep this session running - exiting it will release the lock.

• Get the current position in the binary log by running SHOW MASTER STATUS:

SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000096 |      568 |              |                  |
+--------------------+----------+--------------+------------------+

• Record the File and Position details. If binary logging has just been enabled, these will be blank.

• Now, with the lock still in place, copy the data from the master to the slave. See Backup, Restore and Import for details on how to do this.

• Note for live databases: You just need to make a local copy of the data, you don't need to keep the master locked until the slave has imported the data.

• Once the data has been copied, you can release the lock on the master by running UNLOCK TABLES.

UNLOCK TABLES;

Start the Slave

• Once the data has been imported, you are ready to start replicating. Begin by running a CHANGE MASTER TO, making sure that MASTER_LOG_FILE matches the file and MASTER_LOG_POS the position returned by the earlier SHOW MASTER STATUS. For example:

CHANGE MASTER TO
  MASTER_HOST='master.domain.com',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master1-bin.000096',
  MASTER_LOG_POS=568,
  MASTER_CONNECT_RETRY=10;

If you are starting a slave against a fresh master that was configured for replication from the start, then you don't have to specify MASTER_LOG_FILE and MASTER_LOG_POS.

Use Global Transaction Id (GTID)

It is generally recommended to use (GTIDs), as it has a number of benefits. All that is needed is to add the MASTER_USE_GTID option to the CHANGE MASTER statement, for example:

CHANGE MASTER TO MASTER_USE_GTID = slave_pos

See Global Transaction ID for a full description. <>

• Now start the slave with the START SLAVE command:

START SLAVE;

• Check that the replication is working by executing the SHOW SLAVE STATUS command:

SHOW SLAVE STATUS \G

• If replication is working correctly, both the values of Slave_IO_Running and Slave_SQL_Running should be Yes:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Replicating from MySQL Master to MariaDB Replica

• Replicating from MySQL 5.5 to MariaDB should just work. When using a MariaDB as a replica, it may be necessary to set binlog_checksum to NONE.

• Replicating from MySQL 5.6 without GTID to MariaDB 10+ should work.

• Replication from MySQL 5.6 with GTID, binlog_rows_query_log_events and ignorable events works. In this case MariaDB will remove the MySQL GTIDs and other unneeded events and instead adds its own GTIDs.

• Replication from MySQL 8 to MariaDB requires MariaDB 11.4.5 or newer.

See Also

• Differences between Statement-based, mixed and row logging

• Replication and Foreign Keys

• Replication as a Backup Solution

• Multi-source Replication

• Global Transaction ID

• Parallel Replication

• Replication and Binary Log System Variables

• Replication and Binary Log Status Variables

• Semisynchronous Replication

• Delayed Replication

• Replication Compatibility Between MariaDB and MySQL

_{This page is licensed: CC BY-SA / Gnu FDL}

Note that MariaDB and MySQL have different GTID implementations, and that these are not compatible with each other. MariaDB can be a replica for a MySQL primary but MySQL cannot be a replica for a MariaDB primary.

Overview

MariaDB replication in general works as follows (see Replication overview for more information):

On a master server, all updates to the database (DML and DDL) are written into the binary log as binlog events. A replica server connects to the primary and reads the binlog events, then applies the events locally to replicate the same changes as done on the primary. A server can be both a primary and a replica at the same time, and it is thus possible for binlog events to be replicated through multiple levels of servers.

A replica server keeps track of the position in the primary's binlog of the last event applied on the replica. This allows the replica server to re-connect and resume from where it left off after replication has been temporarily stopped. It also allows a replica to disconnect, be cloned and then have the new replica resume replication from the same primary.

Global transaction ID (GTID) introduces a new event attached to each event group in the binlog. An event group is a collection of events that are always applied as a unit. They are best thought of as a "transaction", though they also include non-transactional DML statements, as well as DDL. When an event group is replicated from primary server to replica server, the global transaction ID is preserved. The GTID is globally unique across an entire group of servers, making it easy to uniquely identify the same binlog events on different servers that replicate each other. GTIDs are generated for all event groups, independent of binlog_format (i.e. ROW, STATEMENT, andMIXED formats are all supported).

Benefits

Using global transaction ID provides two main benefits:

1. Easy to change a replica server to connect to and replicate from a different primary server.

The replica remembers the global transaction ID of the last event group applied from the old primary. This makes it easy to know where to resume replication on the new primary, since the global transaction IDs are known throughout the entire replication hierarchy. This is not the case when using old-style replication; in this case the replica knows only the specific file name and offset of the old primary server of the last event applied. There is no simple way to guess from this the correct file name and offset on a new primary.

1. The state of the replica is recorded in a crash-safe way.

The replica keeps track of its current position (the global transaction ID of the last transaction applied) in the mysql.gtid_slave_pos system table. If this table is using a transactional storage engine (such as InnoDB, which is the default), then updates to the state are done in the same transaction as the updates to the data. This makes the state crash-safe; if the replica server crashes, crash recovery on restart will make sure that the recorded replication position matches the changes that were actually replicated. This is not the case for old-style replication, where the state is recorded in a file relay-log.info, which is updated independently of the actual data changes and can easily get out of sync if the replica server crashes. (This works for DML to transactional tables; non-transactional tables and DDL in general are not crash-safe in MariaDB.)

Because of these two benefits, it is generally recommended to use global transaction ID for any replication setups based on MariaDB 10.0.2 or later. However, old-style replication continues to work as always, so there is no pressing need to change existing setups. Global transaction ID integrates smoothly with old-style replication, and the two can be used freely together in the same replication hierarchy. There is no special configuration needed of the server to start using global transaction ID. However, it must be explicitly set for a replica server with the appropriate CHANGE MASTER option; by default old-style replication is used by a replication replica, to maintain backwards compatibility.

Implementation

A global transaction ID, or GTID for short, consists of three numbers separated with dashes '-'. For example:

0-1-10

• The first number 0 is the domain ID, which is specific for global transaction ID (more on this below). It is a 32-bit unsigned integer.

• The second number is the server ID, the same as is also used in old-style replication. It is a 32-bit unsigned integer.

• The third number is the sequence number. This is a 64-bit unsigned integer that is monotonically increasing for each new event group logged into the binlog.

The server ID is set to the server ID of the server where the event group is first logged into the binlog. The sequence number is increased on a server for every event group logged. Since server IDs must be unique for every server, this makes the (server_id, sequence_number) pair, and hence the whole GTID, globally unique.

Using a 64-bit number provides ample range that there should be no risk of it overflowing in the foreseeable future. However, one should not artificially (by setting gtid_seq_no) inject a GTID with a very high sequence number close to the limit of 64-bit.

The Domain ID

When events are replicated from a primary server to a replica server, the events are always logged into the replica's binlog in the same order that they were read from the primary's binlog. Thus, if there is only ever a single primary server receiving (non-replication) updates at a time, then the binlog order will be identical on every server in the replication hierarchy.

This consistent binlog order is used by the replica to keep track of its current position in the replication. Basically, the replica remembers the GTID of the last event group replicated from the primary. When reconnecting to a primary, whether the same one or a new one, it sends this GTID position to the primary, and the primary starts sending events from the first event after the corresponding event group.

However, if user updates are done independently on multiple servers at the same time, then in general it is not possible for binlog order to be identical across all servers. This can happen when using multi-source replication, with multi-primary ring topologies, or just if manual updates are done on a replica that is replicating from active primary. If the binlog order is different on the new primary from the order on the old primary, then it is not sufficient for the replica to keep track of a single GTID to completely record the current state.

The domain ID, the first component of the GTID, is used to handle this.

In general, the binlog is not a single ordered stream. Rather, it consists of a number of different streams, each one identified by its own domain ID. Within each stream, GTIDs always have the same order in every server binlog. However, different streams can be interleaved in different ways on different servers.

A replica server then keeps track of its replication position by recording the last GTID applied within each replication stream. When connecting to a new primary, the replica can start replication from a different point in the binlog for each domain ID.

For more details on using multi-primary setups and multiple domain IDs, see Use with multi-source replication and other multi-primary setups.

Simple replication setups only have a single primary being updated by the application at any one time. In such setups, there is only a single replication stream needed. Then domain ID can be ignored, and left as the default of 0 on all servers.

Using Global Transaction IDs

Global transaction ID is enabled automatically. Each event group logged to the binlog receives a GTID event, as can be seen with mariadb-binlog or SHOW BINLOG EVENTS.

The replica automatically keeps track of the GTID of the last applied event group, as can be seen from the gtid_slave_pos variable:

SELECT @@GLOBAL.gtid_slave_pos
0-1-1

When a replica connects to a primary, it can use either global transaction ID or old-style filename/offset to decide where in the primary binlogs to start replicating from. To use global transaction ID, use the CHANGE MASTER master_use_gtid option:

CHANGE MASTER TO master_use_gtid = { slave_pos | current_pos | no }

A replica is configured to use GTID by CHANGE MASTER TO master_use_gtid=slave_pos. When the replica connects to the primary, it will start replication at the position of the last GTID replicated to the replica, which can be seen in the variable gtid_slave_pos. Since GTIDs are the same across all replication servers, the replica can then be pointed to a different primary, and the correct position will be determined automatically.

But suppose that we set up two servers A and B and let A be the primary and B the replica. It runs for a while. Then at some point we take down A, and B becomes the new primary. Then later we want to add A back, this time as a replica.

Since A was never a replica before, it does not have any prior replicated GTIDs, and gtid_slave_pos will be empty. To allow A to be added as a replica automatically,master_use_gtid=current_pos can be used. This will connect using the value of the variable gtid_current_pos instead ofgtid_slave_pos, which also takes into account GTIDs written into the binlog when the server was a primary.

When using master_use_gtid=current_pos there is no need to consider whether a server was a primary or a replica prior to using CHANGE MASTER. But care must be taken not to inject extra transactions into the binlog on the replica server that are not intended to be replicated to other servers. If such an extra transaction is the most recent when the replica starts, it will be used as the starting point of replication. This will probably fail because that transaction is not present on the primary. To avoid local changes on a replica server to go into the binlog, set sql_log_bin to 0.

If it is undesirable that changes to the binlog on the replica affects the GTID replication position, then master_use_gtid=slave_pos should be used. Then the replica will always connect to the primary at the position of the last replicated GTID. This may avoid some surprises for users that expect behavior consistent with traditional replication, where the replication position is never changed by local changes done on a server.

When GTID strict mode is enabled (by setting@@GLOBAL.gtid_strict_mode to 1), it is normally best to usecurrent_pos. In strict mode, extra transactions on the Replica are disallowed as they would generate a local gtid. The local gtid would contain the current seqno the Replica is at incremented by 1, at the next transaction that will come from the Primary, the Replica would find such seqno already used by its own local transaction and it will stop replicating for safety until the situation is assessed.

If a replica is configured with the binlog disabled,current_pos and slave_pos are equivalent.

Even when a replica is configured to connect with the old-style binlog filename and offset (CHANGE MASTER TO master_log_file=..., master_log_pos=...), it will still keep track of the current GTID position in @@GLOBAL.gtid_slave_pos. This means that an existing replica previously configured and running can be changed to connect with GTID (to the same or a new master) simply with:

CHANGE MASTER TO master_use_gtid = slave_pos

The replica remembers that master_use_gtid=slave_pos|master_pos was specified and will use it also for subsequent connects, until it is explicitly changed by specifyingmaster_log_file/pos=... ormaster_use_gtid=no. The current value can be seen as the field Using_Gtid of SHOW SLAVE STATUS:

SHOW SLAVE STATUS\G
...
Using_Gtid: Slave_pos

The replica server internally uses the mysql.gtid_slave_pos table to store the GTID position (and so preserve the value of @@GLOBAL.gtid_slave_pos across server restarts). After upgrading a server to 10.0, it is necessary to run mysql_upgrade (as always) to get the table created.

In order to be crash-safe, this table must use a transactional storage engine such as InnoDB. When MariaDB is first installed (or upgraded to 10.0.2+) the table is created using the default storage engine - which itself defaults to InnoDB. If there is a need to change the storage engine for this table (to make it transactional on a system configured with MyISAM as the default storage engine, for example), use ALTER TABLE:

ALTER TABLE mysql.gtid_slave_pos ENGINE = InnoDB

The mysql.gtid_slave_pos table should not be modified in any other way. In particular, do not try to update the rows in the table to change the replica's idea of the current GTID position; instead use

SET GLOBAL gtid_slave_pos = '0-1-1'

Starting from MariaDB 10.3.1, the server variable gtid_pos_auto_engines can preferably be set to make the server handle this automatically. See the description of the mysql.gtid_slave_pos table for details.

Using current_pos vs. slave_pos

When setting the MASTER_USE_GTID replication parameter, you have the option of enabling Global Transaction IDs to use either the current_pos or slave_pos values.

Using the value current_pos causes the replica to set its position based on the gtid_current_pos system variable, which is a union of gtid_binlog_pos and gtid_slave_pos. Using the value slave_pos causes the replica to instead set its position based on the gtid_slave_pos system variable.

You may run into issues when you use the value current_pos if you write any local transactions on the replica. For instance, if you issue an INSERT statement or otherwise write to a table while the replica threads are stopped, then new local GTIDs may be generated in gtid_binlog_pos, which will affect the replica's value of gtid_current_pos. This may cause errors when the replica threads are restarted, since the local GTIDs will be absent from the primary.

You can correct this issue by setting the MASTER_USE_GTID replication parameter to slave_pos instead of current_pos. For example:

CHANGE MASTER TO MASTER_USE_GTID = slave_pos;
START SLAVE;

Using GTIDs with Parallel Replication

If parallel replication is in use, then events that were logged with GTIDs with different gtid_domain_id values can be applied in parallel in an out-of-order manner.

Using GTIDs with MariaDB Galera Cluster

Starting with MariaDB 10.1.4, MariaDB Galera Cluster has limited support for GTIDs. See Using MariaDB GTIDs with MariaDB Galera Cluster for more information.

Setting up a New Replica Server with Global Transaction ID

Setting up a new replica server with global transaction ID is not much different from setting up an old-style replica. The basic steps are:

1. Setup the new server and load it with the initial data.

2. Start the replica replicating from the appropriate point in the primary's binlog.

Setting up a New Replica with an Empty Server

The simplest way for testing purposes is probably to setup a new, empty replica server and replicate all of the primary's binlogs from the start (this is usually not feasible in a realistic production setup, as the initial binlog files will probably have been purged or take too long to apply).

The replica server is installed in the normal way. By default, the GTID position for a newly installed server is empty, which makes the replica replicate from the start of the primary's binlogs. But if the replica was used for other purposes before, the initial position can be explicitly set to empty first:

SET GLOBAL gtid_slave_pos = "";

Next, point the replica to the master with CHANGE MASTER. Specify master_host etc. as usual. But instead of specifying master_log_file and master_log_pos manually, use master_use_gtid=current_pos (orslave_pos to have GTID do it automatically:

CHANGE MASTER TO 
 master_host="127.0.0.1", 
 master_port=3310, 
 master_user="root", 
 master_use_gtid=current_pos;
START SLAVE;

Setting up a New Replica From a Backup

The normal way to set up a new replication replica is to take a backup from an existing server (either a primary or replica in the replication topology), and then restore that backup on the server acting as the new replica, and the configure it to start replicating from the appropriate position in the primary's binary log.

It is important that the position at which replication is started corresponds exactly to the state of the data at the point in time that the backup was taken. Otherwise, the replica can end up with different data than the primary because of missing or duplicated transactions. Of course, if there are no writes to the server being backed up during the backup process, then a simple SHOW MASTER STATUS will give the correct position.

See the description of the specific backup tool to determine how to get the binary log position that corresponds to the backup.

Once the current binary log position for the backup has been obtained, in the form of a binary log file name and position, the corresponding GTID position can be obtained from BINLOG_GTID_POS() on the server that was backed up:

SELECT BINLOG_GTID_POS("master-bin.000001", 600);

The new replica can then start replicating from the primary by setting the correct value forgtid_slave_pos, and then executing CHANGE MASTER with the relevant values for the primary, and then starting the replica threads by executing START SLAVE. For example:

SET GLOBAL gtid_slave_pos = "0-1-2";
CHANGE MASTER TO 
 master_host="127.0.0.1", 
 master_port=3310, 
 master_user="root", 
 master_use_gtid=slave_pos;
START SLAVE;

This method is particularly useful when setting up a new replica from a backup of the primary. Remember to ensure that the value of server_id configured on the new replica is different from that of any other server in the replication topology.

If the backup was taken of an existing replica server, then the new replica should already have the correct GTID position stored in the mysql.gtid_slave_pos table. This is assuming that this table was backed up and that it was backed up in a consistent manner with changes to other tables. In this case, there is no need to explicitly look up the GTID position on the old server and set it on the new replica - it will be already correctly loaded from the mysql.gtid_slave_pos table. This however does not work if the backup was taken from the primary - because then the current GTID position is contained in the binary log, not in the mysql.gtid_slave_pos table or any other table.

Setting up a New Replica with mariadb-backup

A new replica can easily be set up with mariadb-backup, which is a fork of Percona XtraBackup. See Setting up a Replica with mariadb-backup for more information.

Setting up a New Replica with mariadb-dump

A new replica can also be set up with mariadb-dump.

mariadb-dump automatically includes the GTID position as a comment in the backup file if either the --master-data or --dump-slave option is used. It also automatically includes the commands to set gtid_slave_pos and execute CHANGE MASTER in the backup file if the --gtid option is used with either the --master-data or --dump-slave option.

Switching An Existing Old-Style Replica To Use GTID.

If there is already an existing replica running using old-style binlog filename/offset position, then this can be changed to use GTID directly. This can be useful for upgrades for example, or where there are already tools to setup new replica using old-style binlog positions.

When a replica connects to a primary using old-style binlog positions, and the primary supports GTID (i.e. is MariaDB 10.0.2 or later), then the replica automatically downloads the GTID position at connect and updates it during replication. Thus, once a replica has connected to the GTID-aware primary at least once, it can be switched to using GTID without any other actions needed;

STOP SLAVE;
CHANGE MASTER TO 
 master_host="127.0.0.1", 
 master_port=3310, 
 master_user="root", 
 master_use_gtid=current_pos;
START SLAVE;

(A later version will probably add a way to setup the replica so that it will connect with old-style binlog file/offset the first time, and automatically switch to using GTID on subsequent connects.)

Changing a Replica to Replicate From a Different Primary

Once replication is running with GTID (master_use_gtid=current_pos|slave_pos), the replica can be pointed to a new primary simply by specifying in CHANGE MASTER the new master_host (and if required master_port, master_user, and master_password):

STOP SLAVE;
CHANGE MASTER TO 
 master_host='127.0.0.1', 
 master_port=3312;
START SLAVE;

The replica has a record of the GTID of the last applied transaction from the old primary, and since GTIDs are identical across all servers in a replication hierarchy, the replica will just continue from the appropriate point in the new primary's binlog.

It is important to understand how this change of primary work. The binlog is an ordered stream of events (or multiple streams, one per replication domain, (see Use with multi-source replication and other multi-primary setups). Events within the stream are always applied in the same order on every replica that replicates it. The MariaDB GTID relies on this ordering, so that it is sufficient to remember just a single point within the stream. Since event order is the same on every server, switching to the point of the same GTID in the binlog of another server will give the same result.

This translates into some responsibility for the user. The MariaDB GTID replication is fully asynchronous, and fully flexible in how it can be configured. This makes it possible to use it in ways where the assumption that binlog sequence is the same on all servers is violated. In such cases, when changing primary, GTID will still attempt to continue at the point of current GTID in the new binlog.

The most common way that binlog sequence gets different between servers is when the user/DBA does updates directly on a replica server (and these updates are written into the replica's binlog). This results in events in the replica's binlog that are not present on the primary or any other replicas. This can be avoided by setting the session variable sql_log_bin false while doing such updates, so they do not go into the binlog.

It is normally best to avoid any differences in binlogs between servers. That being said, MariaDB replication is designed for maximum flexibility, and there can be valid reasons for introducing such differences from time to time. It this case, it just needs to be understood that the GTID position is a single point in each binlog stream (one per replication domain), and how this affects the users particular setup.

Differences can also occur when two primary are active at the same time in a replication hierarchy. This happens when using a multi-primary ring. But it can also occur in a simple primary-replica setup, during switch to a new primary, if changes on the old primary is not allowed to fully replicate to all replica servers before switching primary. Normally, to switch primary, first writes to the old primary should be stopped, then one should wait for all changes to be replicated to the new primary, and only then should writes begin on the new primary. Deliberately using multiple active primary is also supported, this is described in the next section.

The GTID strict mode can be used to enforce identical binlogs across servers. When it is enabled, most actions that would cause differences are rejected with an error.

Use With Multi-Source Replication and Other Multi-Primary Setups

MariaDB global transaction ID supports having multiple primarys active at the same time. Typically this happens with either multi-source replication or multi-primary ring setups.

In such setups, each active primary must be configured with its own distinct replication domain ID, gtid_domain_id. The binlog will then in effect consists of multiple independent streams, one per active primary. Within one replication domain, binlog order is always the same on every server. But two different streams can be interleaved differently in different server binlogs.

The GTID position of a given replica is then not a single GTID. Rather, it becomes the GTID of the last event group applied for each value of domain ID, in effect the position reached in each binlog stream. When the replica connects to a primary, it can continue from one stream in a different binlog position than another stream. Since order within one stream is consistent across all servers, this is sufficient to always be able to continue replication at the correct point in any new primary server(s).

Domain IDs are assigned by the DBA, according to the need of the application. The default value of @@GLOBAL.gtid_domain_id is 0. This is appropriate for most replication setups, where only a single primary is active at a time. The MariaDB server will never by itself introduce new domain_id values into the binlog.

When using multi-source replication, where a single replica connects to multiple primaries at the same time, each such primary should be configured with its own distinct domain ID.

Similarly, in a multi-primary ring topology, where all primary in the ring are updated by the application concurrently (with some mechanism to avoid conflicts), a distinct domain ID should be configured for each server (In a multi-primary ring where the application is careful to only do updates on one primary at a time, a single domain ID is sufficient).

Normally, a replica server should not receive direct updates (as this creates binlog differences compared to the primary). Thus it does not matter what value of gtid_domain_id is set on a replica, though it may make sense to make it the same as the primary (if not using multi-primary) to make it easy to promote the replica as a new primary. Of course, if a replica is itself an active primary, as in a multi-primary ring topology, the domain ID should be set according to the server's role as active primary.

Note that domain ID and server ID are distinct concepts. It is possible to use a different domain ID on each server, but this is normally not desirable. It makes the current GTID position (@@global.gtid_slave_pos) more complicated to understand and work with, and loses the concept of a single ordered binlog stream across all servers. It is recommended only to configure as many domain IDs as there are primary servers actively being updated by the application at the same time.

It is not an error in itself to configure domain IDs incorrectly (for example, not configuring them at all). For example, this will be typical in an upgrade scenario where a multi-primary ring using 5.5 is upgraded to 10.0. The ring will continue to work as before even though everything is configured to use the default domain ID 0. It is even possible to use GTID for replication between the servers. However, care must be taken when switching a replica to a different primary. If the binlog order between the old and the new primary differs, then a single GTID position to start replication from in the new primary's binlog may not be sufficient.

Multiple Redundant Replication Paths

Using GTID with multi-source replication, it is possible to set up multiple redundant replication paths. For example:

M1 <-> M2
  M1 -> S1
  M1 -> S2
  M2 -> S1
  M2 -> S2

Here, M1 and M2 are setup in a master-master ring. S1 and S2 both replicate from each of M1 and M2. Each event generated on M1 will now arrive twice at S1, through the paths M1->S1 and M1->M2->S1. This way, if the network connection between M1 and S1 is broken, the replication can continue uninterrupted through the alternate path through M2. Note that this is an advanced setup, and good familiarity with MariaDB replication is recommended to successfully operate it.

The option --gtid-ignore-duplicates must be enabled to use multiple redundant replication paths. This is necessary to avoid each event being applied twice on the replica as it arrives through each path. The GTID of every event will be compared against the sequence number of the current GTID replica position (within each domain), and will be skipped if less than or equal. Thus it is required that sequence numbers are strictly increasing within each domain for --gtid-ignore-duplicates to function correctly, and setting --gtid-strict-mode=1 to help enforce this is recommended.

The --gtid-ignore-duplicates options also relaxes the requirement for connection to the master. In the above example, when S1 connects to M2, it may connect at a GTID position from M1 that has not yet been applied on M2.

When --gtid-ignore-duplicates is enabled, the connection will be allowed, and S1 will start receiving events from M2 once the GTID has been replicated from M1 to M2. This can also be used to use replication filters in parts of a replication topology, to allow a replica to connect to a GTID position which was filtered on a master. When --gtid-ignore-duplicates is enabled, the connecting replica will start receiving events from the master at the first GTID sequence number that is larger than the connect-position.

Deleting Unused Domains

FLUSH BINARY LOGS DELETE_DOMAIN_ID=(list-of-domains) can be used to discard obsolete GTID domains from the server's binary log state. In order for this to be successful, no event group from the listed GTID domains can be present in existing binary log files. If some still exist, then they must be purged prior to executing this command.

If the command completes successfully, then it also rotates the binary log.

The old domains will still appear in gtid_io_pos. To get rid of these, you can stop the replica and execute on the replica:

SET gtid_slave_pos="<position WITH domains removed>"

Additional Syntax For Global Transaction ID

CHANGE MASTER

CHANGE MASTER has an option, master_use_gtid=[current_pos|slave_pos|no]. When enabled (set tocurrent_pos or slave_pos), the replica will connect to the master using the GTID position. When disabled (set to "no"), the old-style binlog filename/offset position is used to decide where to start replicating when connecting. Unlike in the old-style, when GTID is enabled, the values of the MASTER_LOG_FILE and MASTER_LOG_POS options are not updated per received event in master_info_file file.

The value of master_use_gtid is saved across server restarts (in master.info). The current value can be seen as the field Using_Gtid in the output of SHOW SLAVE STATUS.

For a detailed look at the difference between the current_pos and slave_pos options, see Using global transaction IDs

START SLAVE UNTIL master_gtid_pos=xxx

When starting replication with START SLAVE, it is possible to request the replica to run only until a specific GTID position is reached. Once that position is reached, the replica will stop.

The syntax for this is:

START SLAVE UNTIL master_gtid_pos = <GTID position>

The replica will start replication from the current GTID position, run up to and including the event with the GTID specified, and then stop. Note that this stops both the IO thread and the SQL thread (unlike START SLAVE UNTIL MASTER_LOG_FILE/MASTER_LOG_POS, which stops only the SQL thread).

If multiple GTIDs are specified, then they must be with distinct replication domain ID, for example:

START SLAVE UNTIL master_gtid_pos = "1-11-100,2-21-50"

With multiple domains in the UNTIL condition, each domain runs only up to and including the specified position, so it is possible for different domains to stop at different places in the binlog (each domain will resume from the stopped position when the replica is started the next time).

Not specifying a replication domain at all in the UNTIL condition means that the domain is stopped immediately, nothing is replicated from that domain. In particular, specifying the empty string will stop the replica immediately.

When using START SLAVE UNTIL master_gtid_pos = XXX, if the UNTIL position is present in the primary's binlog then it is permissible for the start position to be missing on the primary. In this case, replication for the associated domains stop immediately.

Both replica threads must be already stopped when using UNTIL master_gtid_pos, otherwise an error occurs. It is also an error if the replica is not configured to use GTID (CHANGE MASTER TO master_use_gtid=current_pos|slave_pos). And both threads must be started at the same time, the IO_THREAD or SQL_THREAD options can not be used to start only one of them.

START SLAVE UNTIL master_gtid_pos=XXX is particularly useful for promoting a new primary among a set of replicas when the old master goes away and replicas may have reached different positions in the old primary's binlog. The new primary needs to be ahead of all the other replicas to avoid losing events. This can be achieved by picking one server, say S1, and replicating any missing events from each other server S2, S3, ..., Sn:

CHANGE MASTER TO master_host="S2";
    START SLAVE UNTIL master_gtid_pos = "<S2 GTID position>";
    ...
    CHANGE MASTER TO master_host="Sn";
    START SLAVE UNTIL master_gtid_pos = "<Sn GTID position>";

Once this is completed, S1 will have all events present on any of the servers. It can now be selected as the new primary, and all the other servers set to replicate from it.

MariaDB starting with 11.3.0

SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS

MariaDB 11.3 extended the START SLAVE UNTIL command with the options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS to allow control of whether the replica stops before or after a provided GTID state. Its syntax is:

START SLAVE UNTIL (SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS)="<gtid_list>"

When providing SQL_BEFORE_GTIDS=”<gtid_list>”, the replica will execute all transactions up to the first GTID found in the provided list, and stop immediately. In contrast to the default behavior of UNTIL, this will execute transactions from all domains on the primary until the replica stops due to seeing a GTID on the list. START SLAVE UNTIL SQL_AFTER_GTIDS=”<gtid_list>” is an alias to the default behavior of START SLAVE UNTIL master_gtid_pos=”<gtid_list>”. That is, the replica will only execute transactions originating from domain ids provided in the list, and will stop once all transactions provided in the UNTIL list have all been executed.

Example

If a primary server has a binary log consisting of the following GTIDs:

• 0-1-1

• 1-1-1

• 0-1-2

• 1-1-2

• 0-1-3

• 1-1-3

If a fresh replica (i.e. one with an empty GTID position, @@gtid_slave_pos='') is started with SQL_BEFORE_GTIDS, i.e. START SLAVE UNTIL SQL_BEFORE_GTIDS=”1-1-2”, the resulting gtid_slave_pos of the replica will be “0-1-2,1-1-1”. This is because the replica will execute all events until it sees the transaction with GTID 1-1-2 and immediately stop without executing it. However, if a replica is started with SQL_AFTER_GTIDS, i.e. START SLAVE UNTIL SQL_AFTER_GTIDS=”1-1-2” then the resulting gtid_slave_pos of the replica will be “1-1-2”. This is because it will only execute events from domain 1 until it has executed the provided GTID.

BINLOG_GTID_POS().

The BINLOG_GTID_POS() function takes as input an old-style binary log position in the form of a file name and a file offset. It looks up the position in the current binlog, and returns a string representation of the corresponding GTID position. If the position is not found in the current binlog, NULL is returned.

MASTER_GTID_WAIT

The MASTER_GTID_WAIT function is useful in replication for controlling primary/replica synchronization, and blocks until the replica has read and applied all updates up to the specified position in the primary log. See MASTER_GTID_WAIT for details.

Binlog Indexing

MariaDB starting with 11.4

Prior to MariaDB 11.4, when a replica connects, MariaDB needs to scan binlog files from the beginning in order to find the place to start replicating. If replica reconnects are frequent, this can be slow.MariaDB 11.4 introduces indexing on the binlog files, allowing GTIDs to be quickly found. This also detects if old-style replication tries to connect at an incorrect file offset (eg. in the middle of an event), avoiding sending potentially corrupted events. The feature is enabled by default. The size of the binlog index file (.idx) is generally less than 1% the size of the binlog, so should not have any negative impacts and should not normally need tuning. However, the feature can be disabled or managed with the following system variables:

• binlog_gtid_index - enable/disable the feature

• binlog_gtid_index_page_size - adjust the size of the pages

• binlog_gtid_index_span_min - adjust the sparseness of the index

There are two status variables that can be used to monitor the effectiveness of the index:

• binlog_gtid_index_hit - incremented for each successful lookup in a GTID index.

• binlog_gtid_index_miss - incremented when a GTID index lookup is not possible, which indicates that the index file is missing (eg. binlog written by old server version without GTID index support), or corrupt.

System Variables

binlog_gtid_index

Enables/disables binlog indexing.

binlog_gtid_index_page_size

Adjusts the size of the pages

binlog_gtid_index_span_min

Adjusts the sparseness of the index

gtid_slave_pos

This system variable contains the GTID of the last transaction applied to the database by the server's replica threads for each replication domain. This system variable's value is automatically updated whenever a replica thread applies an event group. This system variable's value can also be manually changed by users, so that the user can change the GTID position of the replica threads.

When using multi-source replication, the same GTID position is shared by all replica connections. In this case, different primaries should use different replication domains by configuring different gtid_domain_id values. If one primary was using a gtid_domain_id value of 1, and if another primary was using a gtid_domain_id value of 2, then any replicas replicating from both primaries would have GTIDs with both gtid_domain_id values in gtid_slave_pos.

This system variable's value can be manually changed by executing SET GLOBAL, but all replica threads to be stopped with STOP SLAVE first. For example:

STOP ALL SLAVES;
SET GLOBAL gtid_slave_pos = "1-10-100,2-20-500";
START ALL SLAVES;

This system variable's value can be reset by manually changing its value to the empty string. For example:

SET GLOBAL gtid_slave_pos = '';

The GTID position defined by gtid_slave_pos can be used as a replica's starting replication position by setting MASTER_USE_GTID=slave_pos when the replica is configured with the CHANGE MASTER TO statement. As an alternative, the gtid_current_pos system variable can also be used as a replica's starting replication position.

If a user sets the value of the gtid_slave_pos system variable, and gtid_binlog_pos contains later GTIDs for certain replication domains, then gtid_current_pos will contain the GTIDs from gtid_binlog_pos for those replication domains. To protect users in this scenario, if a user sets the gtid_slave_pos system variable to a GTID position that is behind the GTID position in gtid_binlog_pos, then the server will give the user a warning.

This can help protect the user when the replica is configured to use gtid_current_pos as its replication position. This can also help protect the user when a server has been rolled back to restart replication from an earlier point in time, but the user has forgotten to reset gtid_binlog_pos with RESET MASTER.

The mysql.gtid_slave_pos system table is used to store the contents of global.gtid_slave_pos and preserve it over restarts.

• Commandline: None

• Scope: Global

• Dynamic: Yes

• Data Type: string

• Default: Null

gtid_binlog_pos

This variable is the GTID of the last event group written to the binary log, for each replication domain.

Note that when the binlog is empty (such as on a fresh install with --skip-test-db, or after RESET MASTER), there are no event groups written in any replication domain, so in this case the value of gtid_binlog_pos will be the empty string.

The value is read-only, but it is updated whenever a DML or DDL statement is written to the binary log. The value can be reset by executing RESET MASTER, which will also delete all binary logs. However, note that RESET MASTER does not also reset gtid_slave_pos. Since gtid_current_pos is the union of gtid_slave_pos and gtid_binlog_pos, that means that new GTIDs added to gtid_binlog_pos can lag behind those in gtid_current_pos if gtid_slave_pos contains GTIDs in the same domain with higher sequence numbers. If you want to reset gtid_current_pos for a specific GTID domain in cases like this, then you will also have to change gtid_slave_pos in addition to executing RESET MASTER. See gtid_slave_pos for notes on how to change its value.

• Commandline: None

• Scope: Global

• Dynamic: Read-only

• Data Type: string

• Default: Null

gtid_binlog_state

The variable gtid_binlog_state holds the internal state of the binlog. The state consists of the last GTID ever logged to the binary log for every combination of domain_id and server_id. This information is used by the primary to determine whether a given GTID has been logged to the binlog in the past, even if it has later been deleted due to binlog purge. For each domain_id, the last entry in @@gtid_binlog_state is the last GTID logged into binlog, ie. this is the value that appears in @@gtid_binlog_pos.

Normally this internal state is not needed by users, as @@gtid_binlog_pos is more useful in most cases. The main usage of @@gtid_binlog_state is to restore the state of the binlog after RESET MASTER (or equivalently if the binlog files are lost). If the value of @@gtid_binlog_state is saved before RESET MASTER and restored afterwards, the primary will retain information about past history, same as if PURGE BINARY LOGS had been used (of course the actual events in the binary logs are still deleted).

Note that to set the value of @@gtid_binlog_state, the binary log must be empty, that is it must not contain any GTID events and the previous value of @@gtid_binlog_state must be the empty string. If not, then RESET MASTER must be used first to erase the binary log first.

The value of @@gtid_binlog_state is preserved by the server across restarts by writing a file MASTER-BIN.state, where MASTER-BIN is the base name of the binlog set with the --log-bin option. This file is written at server shutdown, and re-read at next server start. (In case of a server crash, the data in the MASTER-BIN.state is not correct, and the server instead recovers the correct value during binlog crash recovery by scanning the binlog files and recording each GTID found).

For completeness, note that setting @@gtid_binlog_state internally executes a RESET MASTER. This is normally not noticeable as it can only be changed when the binlog is empty of GTID events. However, if executed e.g. immediately after upgrading to MariaDB 10, it is possible that the binlog is non-empty but without any GTID events, in which case all such events will be deleted, just as if RESET MASTER had been run.

• Commandline: None

• Scope: Global

• Dynamic: Yes

• Data Type: string

• Default: Null

gtid_current_pos

This system variable contains the GTID of the last transaction applied to the database for each replication domain.

The value of this system variable is constructed from the values of the gtid_binlog_pos and gtid_slave_pos system variables. It gets GTIDs of transactions executed locally from the value of the gtid_binlog_pos system variable. It gets GTIDs of replicated transactions from the value of the gtid_slave_pos system variable.

For each replication domain, if the server_id of the corresponding GTID ingtid_binlog_pos is equal to the servers own server_id,and the sequence number is higher than the corresponding GTID ingtid_slave_pos, then the GTID fromgtid_binlog_pos will be used. Otherwise the GTID fromgtid_slave_pos will be used for that domain.

GTIDs from gtid_binlog_pos in which the server_id of the GTID is not equal to the server's own server_id are effectively ignored. If gtid_binlog_pos contains a GTID for a given replication domain, but the server_id of the GTID is not equal to the server's own server_id, and gtid_slave_pos does not contain a GTID for that given replication domain, then gtid_current_pos will not contain any GTID for that replication domain.

Thus, gtid_current_pos contains the most recent GTID executed on the server, whether this was done as a primary or as a replica.

The GTID position defined by gtid_current_pos can be used as a replica's starting replication position by setting MASTER_USE_GTID=current_pos when the replica is configured with the CHANGE MASTER TO statement. As an alternative, the gtid_slave_pos system variable can also be used as a replica's starting replication position.

The value of gtid_current_pos is read-only, but it is updated whenever a transaction is written to the binary log and/or replicated by a replica thread, and that transaction's GTID is considered newer than the current GTID for that domain. See above for the rules on how to determine if a GTID would be considered newer.

If you need to reset the value, see the notes on resetting gtid_slave_pos and gtid_binlog_pos, since gtid_current_pos is formed from the values of those variables.

• Commandline: None

• Scope: Global

• Dynamic: Read-only

• Data Type: string

• Default: Null

gtid_strict_mode

The GTID strict mode is an optional setting that can be used to help the DBA enforce a strict discipline about keeping binlogs identical across multiple servers replicating using global transaction ID.

When GTID strict mode is enabled, some additional errors are enabled for situations that could otherwise cause differences between binlogs on different servers in a replication hierarchy:

1. If a replica server tries to replicate a GTID with a sequence number lower than what is already in the binlog for that replication domain, the SQL thread stops with an error (this indicates an extra transaction in the replica binlog not present on the primary).

2. Similarly, an attempt to manually binlog a GTID with a lower sequence number (by setting @@SESSION.gtid_seq_no) is rejected with an error.

3. If the replica tries to connect starting at a GTID that is missing in the primary's binlog, this is an error in GTID strict mode even if a GTID exists with a higher sequence number (this indicates a GTID on the replica missing on the primary). Note that this error is controlled by the setting of GTID strict mode on the connecting replica server.

GTID mode is off by default; this is needed to preserve backwards compatibility with existing replication setups (older versions of the server did not enforce any strict mode for binlog order). Global transaction ID is designed to work correctly even when strict mode is not enabled. However, with strict mode enforced, the semantics is simpler and thus easier to understand, because binlog order is always identical across servers and sequence numbers are always strictly increasing within each replication domain. This can also make automated scripting of large replication setups easier to implement correctly.

When GTID strict mode is enabled, the replica will stop with an error when a problem is encountered. This allows the DBA to become aware of the problem and take corrective actions to avoid similar issues in the future. One way to recover from such an error is to temporarily disable GTID strict mode on the offending replica, to be able to replicate past the problem point (perhaps usingSTART SLAVE UNTIL master_gtid_pos=XXX).

• Commandline: --gtid-strict-mode[={0|1}]

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default: Off

gtid_domain_id

• Description: This variable is used to decide which replication domain new GTIDs are logged in for a primary server. See Use with multi-source replication and other multi-primary setups for details. This variable can also be set on the session level by a user with the SUPER privilege. This is used by mariadb-binlog to preserve the domain ID of GTID events.

• Commandline: --gtid-domain-id=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric (32-bit unsigned integer)

• Default Value: 0

• Range: 0 to 4294967295

last_gtid

• Description: Holds the GTID that was assigned to the last transaction, or statement that was logged to the binary log. If the binary log is disabled, or if no transaction or statement was executed in the session yet, then the value is an empty string.

• Scope: Session

• Dynamic: Read-only

• Data Type: string

server_id

• Description: Server_id can be set on the session level to change which server_id value is logged in binlog events (both GTID and other events). This is used by mariadb-binlog to preserve the server ID of GTID events.

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric (32-bit unsigned integer)

gtid_seq_no

• Description: gtid_seq_no can be set on the session level to change which sequence number is logged in the following GTID event. The variable, along with @@gtid_domain_id and @@server_id, is typically used by mariadb-binlog to set up the gtid value of the transaction being decoded into the output.

• Commandline: None

• Scope: Session

• Dynamic: Yes

• Data Type: numeric (64-bit unsigned integer)

• Default: Null

gtid_ignore_duplicates

• Description: When set, different primary connections in multi-source replication are allowed to receive and process event groups with the same GTID (when using GTID mode). Only one will be applied, any others will be ignored. Within a given replication domain, just the sequence number will be used to decide whether a given GTID has been already applied; this means it is the responsibility of the user to ensure that GTID sequence numbers are strictly increasing. With gtid_ignore_duplicates=OFF, a duplicate event based on domain id and sequence number, will be executed. When --gtid-ignore-duplicate is set, a replica is allowed to connect at a GTID position that does not exist on the primary. The replica will start receiving events once a GTID with a higher sequence number is available on the primary (within that domain). This can be used to allow a replica to connect at a GTID position that was filtered on the primary, eg. using --replicate-ignore-table. See also Multiple Redundant Replication Paths

• Commandline: --gtid-ignore-duplicates=#

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default: OFF

gtid_pos_auto_engines

This variable is used to enable multiple versions of the mysql.gtid_slave_pos table, one for each transactional storage engine in use. This can improve replication performance if a server is using multiple different storage engines in different transactions.

The value is a list of engine names, separated by commas (','). Replication of transactions using these engines will automatically create new versions of the mysql.gtid_slave_pos table in the same engine and use that for future transactions (table creation takes place in a background thread). This avoids introducing a cross-engine transaction to update the GTID position. Only transactional storage engines are supported for gtid_pos_auto_engines (this currently means InnoDB, TokuDB, or MyRocks).

The variable can be changed dynamically, but replica SQL threads should be stopped when changing it, and it will take effect when the replicas are running again.

When setting the variable on the command line or in a configuration file, it is possible to specify engines that are not enabled in the server. The server will then still start if, for example, that engine is no longer used. Attempting to set a non-enabled engine dynamically in a running server (with SET GLOBAL gtid_pos_auto_engines) will still result in an error.

Removing a storage engine from the variable will have no effect once the new tables have been created - as long as these tables are detected, they will be used.

• Commandline: --gtid-pos-auto-engines=value

• Scope: Global

• Dynamic: Yes

• Data Type: string (comma-separated list of engine names)

• Default: empty

gtid_cleanup_batch_size

• Description: Normally does not need tuning. How many old rows must accumulate in the mysql.gtid_slave_pos table before a background job will be run to delete them. Can be increased to reduce number of commits if using many different engines with gtid_pos_auto_engines, or to reduce CPU overhead if using a huge number of different gtid_domain_ids. Can be decreased to reduce number of old rows in the table.

• Commandline: --gtid-cleanup-batch-size=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default: 64

• Range: 0 to 2147483647

• Introduced: MariaDB 10.4.1

See Also

• FLUSH binary logs

_{This page is licensed: CC BY-SA / Gnu FDL}

A common replication setup is to have the replicasread-only to ensure that no one accidentally updates them. If the replica has binary logging enabled and gtid_strict_mode is used, then any update that causes changes to the binary log will stop replication.

When the variable read_only is set to 1, no updates are permitted except from users with the SUPER privilege (<= MariaDB 10.5.1) or READ ONLY ADMIN privilege (>= MariaDB 10.5.2) or replica servers updating from a primary. Inserting rows to log tables, updates to temporary tables and OPTIMIZE TABLE or ANALYZE TABLE statements on temporary tables are excluded from this limitation.

If read_only is set to 1, then the SET PASSWORD statement is limited only to users with the SUPER privilege (<= MariaDB 10.5.1) or READ ONLY ADMIN privilege (>= MariaDB 10.5.2).

Attempting to set the read_only variable to 1 will fail if the current session has table locks or transactions pending.

The statement will wait for other sessions that hold table locks. While the attempt to set read_only is waiting, other requests for table locks or transactions will also wait until read_only has been set.

From MariaDB 10.3.19, some issues related to read only replicas are fixed:

• CREATE, DROP, ALTER, INSERT and DELETE of temporary tables are not logged to binary log, even in statement or mixed mode. With earlier MariaDB versions, one can avoid the problem with temporary tables by using binlog_format=ROW in which cases temporary tables are never logged.

• Changes to temporary tables created during read_only will not be logged even after read_only mode is disabled (for example if the replica is promoted to a primary).

• The admin statements ANALYZE, CHECK, OPTIMIZE and REPAIR will not be logged to the binary log under read-only.

Older MariaDB Versions

If you are using an older MariaDB version with read-only replicas and binary logging enabled on the replica, and you need to do some changes but don't want to have them logged to the binary log, the easiest way to avoid the logging is to disable binary logging while running as root during maintenance:

SET sql_log_bin=0;
ALTER TABLE test ENGINE=rocksdb;

The above changes the test table on the replica to rocksdb without registering the change in the binary log.

_{This page is licensed: CC BY-SA / Gnu FDL}

Multi-source replication means that one server has many primaries from which it replicates.

New Syntax

You specify which primary connection you want to work with by either specifying the connection name in the command or settingdefault_master_connection to the connection you want to work with.

The connection name may include any characters and should be less than 64 characters. Connection names are compared without regard to case (case insensitive). You should preferably keep the connection name short as it will be used as a suffix for relay logs and primary info index files.

The new syntax introduced to handle many connections:

• CHANGE MASTER ['connection_name'] TO .... This creates or modifies a connection to a primary.

• FLUSH RELAY LOGS ['connection_name']

• MASTER_POS_WAIT(....,['connection_name'])

• [RESET SLAVE ['connection_name'] [ALL](../../../reference/sql-statements-and-structure/sql-statements/administrative-sql-statements/replication-statements/reset-replica.md)]. This is used to reset a replica's replication position or to remove a replica permanently.

• SHOW RELAYLOG ['connection_name'] EVENTS

• SHOW SLAVE ['connection_name'] STATUS

• SHOW ALL SLAVES STATUS

• [START SLAVE ['connection_name'](../../../reference/sql-statements-and-structure/sql-statements/administrative-sql-statements/replication-statements/start-replica.md)...]]

• START ALL SLAVES ...

• STOP SLAVE ['connection_name'] ...

• STOP ALL SLAVES ...

The original old-style connection is an empty string ''. You don't have to use this connection if you don't want to.

You create new primary connections with CHANGE MASTER. You delete the connection permanently with RESET SLAVE 'connection_name' ALL.

Replication Variables for Multi-Source

The new replication variable default_master_connection specifies which connection will be used for commands and variables if you don't specify a connection. By default this is '' (the default connection name).

The following replication variables are local for the connection. (In other words, they show the value for the@@default_master_connection connection). We are working on making all the important ones local for the connection.

You can access all of the above variables with eitherSESSION or GLOBAL.

Note that in contrast to MySQL, all variables always show the correct active value!

Example:

set @@default_master_connection='';
show status like 'Slave_running';
set @@default_master_connection='other_connection';
show status like 'Slave_running';

If @@default_master_connection contains a non existing name, you will get a warning.

All other primary-related variables are global and affect either only the '' connections or all connections. For example,Slave_retried_transactions now shows the total number of retried transactions over all replicas.

If you need to set gtid_slave_pos you need to set this for all primaries at the same time.

New status variables:

SHOW ALL SLAVES STATUS has the following new columns:

New Files

The basic principle of the new files used by multi source replication is that they have the same name as the original relay log files suffixed withconnection_name before the extension. The main exception is the file that holds all connection is named as the normalmaster-info-file with a multi- prefix.

When you are using multi source, the following new files are created:

When creating the file, the connection name is converted to lower case and all special characters in the connection name are converted, the same way as MySQL table names are converted. This is done to make the file name portable across different systems.

Hint:

Instead of specifying names for mysqld with --relay-log, --relay-log-index, --general-log-file, --slow-query-log-file,--log-bin and --log-bin-index, you can just specify --log-basename and all the other variables are set with this as a prefix.

Other Things

• All error messages from a replica with a connection name, that are written to the error log, are prefixed with Master 'connection_name':. This makes it easy to see from where an error originated.

• Errors ER_MASTER_INFO and WARN_NO_MASTER_INFO now includes connection_name.

• There is no conflict resolution. The assumption is that there are no conflicts in data between the different primaries.

• All executed commands are stored in the normal binary log (nothing new here).

• If the server variable log_warnings > 1 then you will get some information in the log about how the multi-master-info file is updated (mainly for debugging).

• The output of SHOW ALL SLAVES STATUS has one more column than SHOW SLAVE STATUS, since it includes the connection_name column.

• RESET SLAVE now deletes all relay-log files.

replicate-... Variables

• One can set the values for the replicate-... variables from the command line or in my.cnf for a given connection by prefixing the variable with the connection name.

• If one doesn't use any connection name prefix for a replicate.. variable, then the value will be used as the default value for all connections that don't have a value set for this variable.

Example:

mysqld --main_connection.replicate_do_db=main_database --replicate_do_db=other_database

The have sets the replicate_do_db variable to main_database for the connection named main_connection. All other connections will use the value other_database.

One can also use this syntax to set replicate-rewrite-db for a given connection.

Typical Use Cases

• You are partitioning your data over many primaries and would like to get it all together on one machine to do analytical queries on all data.

• You have many databases spread over many MariaDB/MySQL servers and would like to have all of them on one machine as an extra backup.

• In a Galera cluster the default replication filter rules like replicate-do-db do not apply to replication connections, but also to Galera write set applier threads. By using a named multi-primary replication connection instead, even when replicating from just one primary into the cluster, the primary-replica replication rules can be kept separate from the Galera intra-node replication traffic.

Limitations

• Each active connection will create 2 threads (as is normal for MariaDB replication).

• You should ensure that all primaries have different server-id's. If you don't do this, you will get into trouble if you try to replicate from the multi-source replica back to your primaries.

• One can change max_relay_log_size for any active connection, but new connections will always use the server startup value for max_relay_log_size, which can't be changed at runtime.

• Option innodb-recovery-update-relay-log (xtradb feature to store and restore relay log position for replicas) only works for the default connection ''. As this option is not really safe and can easily cause loss of data if you use storage engines other than InnoDB, we don't recommend this option be used.

• slave_net_timeout affects all connections. We don't check anymore if it's less than Slave_heartbeat_period, as this doesn't make sense in a multi-source setup.

Incompatibilities with MariaDB/MySQL 5.5

• max_relay_log_size is now (almost) a normal variable and not automatically changed if max_binlog_size is changed. To keep things compatible with old config files, we set it to max_binlog_size at startup if its value is 0.

• You can now access replication variables that depend on the active connection with either GLOBAL or SESSION.

• We only write information about relay log positions for recovery if innodb-recovery-update-relay-log is set.

• Slave_retried_transactions now shows the total count of retried transactions over all replicas.

• The status variable Com_slave_start is replaced with Com_start_slave.

• The status variable Com_slave_stop is replaced with Com_stop_slave.

• FLUSH RELAY LOGS are not replicated anymore. This is not safe as connection names may be different on the replica.

See Also

• Multi-master ring replication

• Using multi-source with global transaction id

• The work in MariaDB is based on the project description at MDEV-253.

• The original code base comes from Taobao, developed by Peng Lixun. A big thanks to them for this important feature!

_{This page is licensed: CC BY-SA / Gnu FDL}

What is Multi-Master Ring Replication

Multi-Master "Ring" Replication means that you have two or more masters where each master is replicating its data to another master asynchronously. This is not to be confused with MariaDB Galera Cluster which is a virtually synchronous multi-primary cluster for MariaDB.

The benefit of asynchronous replication compared to Galera Cluster, is that Ring Replication is resilient against bad network connections between the master servers. If a connection goes down, all masters will continue to serve its clients locally and data will automatically be synced when the link is available again.

The following picture shows one of the more advanced Multi-Master setups that is resilient against any master going down but can also handle 'human failures', like an accidental drop table, thanks to the addition of delayed slaves.

One should setup replication on each master like one does in standard MariaDB replication. The replication setup among the masters should be a ring. In other words, each master should replicate to one other master and each master should only have one other master as a slave.

Each master can of course have one or more normal slaves. A master could also be a slave of another master that is not in the ring setup.

All MariaDB servers support Multi-Master Ring Replication. In fact, when MySQL replication was originally designed around the year 2000, it was to be a Multi-Master Ring Replication solution for Yahoo to replicate from the East Coast to the West Coast.

Configuring the Masters

First, follow the instructions in setup replication. The main thing to remember is to use the master_use_gtid=current_pos option for CHANGE MASTER.

The main things that are different for Multi-Master Ring Replication are:

• Give every master and slave in the replication setup a unique server_id. This can be a number from 1 to 4294967295 or 1-255 if one is using uuid_short(). It is a good practice to ensure that you do not have any servers in your system with the same server_id!

• Use global transaction id (as described above)

• Give each master a unique gtid_domain_id. This will allow replication to apply transactions from a different master in parallel independent from other masters.

Add the following into your my.cnf file for all masters and restart the servers.

[mariadb]
# Replace the following with a unique ID. All slaves of this master should have the same
# gtid_domain_id to allow easy failover to a slave if needed
gtid_domain_id=1
#
# Let us assume there will never be more than 10 masters in a Multi-Master ring setup
auto_increment_increment=10
# Set this to a different value 1-10 for each master. Could be the same as gtid_domain_id
# This is to ensure that all masters generate different values for AUTO_INCREMENT keys.
auto_increment_offset=1
#
# The following is needed to ensure the ALTER TABLE on another master will not
# break ring replication
slave_type_conversions=ALL_NON_LOSSY,ALL_LOSSY
#
# We cannot use semi-sync in Ring Replication as the masters need to be resilient against
# bad connections
rpl_semi_sync_master_enabled=0
#
# We have to log updates from other masters to the binary log.
log_slave_updates

Limitations when using Ring Replication

• MariaDB does not yet support conflict resolution for conflicting changes. It is up to the application to ensure that there is never a conflicting insert/update/delete between the masters. The easiest setup is having each master server work on a different database or table. If not, one must:

  • Ensure you have an id (master-unique-id) for each row that unequally identifies the master who is responsible for this row. This should preferably be short and part of the primary key in each table. A good value for this would be the gtid_domain_id as this is unique for each local cluster.

  • Never insert rows with PRIMARY KEY or UNIQUE KEY values that can be same on another master. This can be avoided by

    • Have the master-unique-id part of all primary and unique keys.

    • In case of AUTO_INCREMENT keys, have a different value for auto_increment_offset on each master.

    • Use uuid_short() to generate unique values, like in create table t1 (a bigint unsigned default(uuid_short()) primary key). Note that if one is using uuid_short() in Multi-Master ring replication, one can only use server_id in the range 1-255!

  • Ensure that UPDATE and DELETE on each master only update rows generated by this master.

• If several masters are constantly generating and updating rows for common tables, one has to be extra careful with ALTER TABLE to ensure that any change one does will not cause conflicts when the ALTER TABLE is replicated to other servers. In particular one has to ensure that all masters and their slaves are configured with slave_type_conversions=ALL_NON_LOSSY,ALL_LOSSY.

• The server_id should be unique for each server. One should not change the server_id of an active master, as the ID is used by the master to recognize its own events and stop them from replicating endlessly around the ring (see replicate_same_server_id).

How does Multi-Master Ring Replication work

• The main difference between Multi-Master Ring Replication and normal replication is that a change done by a master will eventually replicate back to it. When this happens, the master will see that the binary log event has the same server_id as the master has and will ignore the event. This is why it is critical to ensure that all server_id's are unique and that one does not change the server id.

• When doing ALTER TABLE in a Multi-Master Ring replication setup, you should be aware the while you are running an ALTER TABLE on one master, another master can generate events that uses the old table definition. You should especially take care to not drop columns that could be in use by any application or still available in the upcoming replication stream.

How to resolve things if they go wrong in Multi-Master Ring Replication

When used correctly, Multi-Master Ring Replication is as resilient to errors as normal MariaDB master-slave replication. If the connection goes down, the replication will stop and will automatically continue when the connection resumes.

What to do when one of the masters dies and has to be replaced by a slave.

• Ensure that the slave is up to date (has digested all relay events).

• Check if there are any events on the old master that have not been sent to the slave. You can use mariadb-binlog to read the old master binary log files and apply them to the slave.

• You can now treat the slave as a new master and put it back in the replication ring. The new master will use its replication GTID position to continue replication from the other master in the ring.

If the slave is not up to date and one cannot access any information of the old master, then one can continue the following way:

• Enable the option --gtid-ignore-duplicates on the servers.

• Add the slave to the replication ring.

• The two masters (one of which is the old slave now added to the ring) will each replicate the events they are missing from one another. The --gtid-ignore-duplicates option is needed to allow the two masters in the ring to start replicating from each other when each server is ahead of the other in one domain and behind in another.

Error applying events

As long as each master handles their own set of data, as described above, there should not be any conflicting data coming from the other master.

If there are conflicts, one should resolve them as one resolves issues with normal replication. The most common way to solve issues is to skip the conflicting log events with SET GLOBAL SQL_SLAVE_SKIP_COUNTER.

Handling duplicate key errors and other conflicts

If things are setup correctly as described earlier, one should never get duplicate key errors in Multi-Master ring replication. Any duplicate key error or data mismatch is usually an application error where it inserts/updates or deletes something it should not have the right to do.

To fix this:

• Use SET GLOBAL SQL_SLAVE_SKIP_COUNTER to skip the error.

• Use `mariadb-binlog --base64-output=decode-rows --verbose --start-position=

binlog-name` to see what is missing and apply the changes missing on the server (minus the conflict).

Multi-Master Ring Replication through slaves

An alternative setup to use for Multi-master ring replications is to replicate to the other masters through slaves. The following setup shows how this can be done.

Benefits of replication through slaves

• The slave will never be out of sync compared to other master. This makes failover from master to slave much easier as one does not have to check if slave has all data.

• Slightly less load on the master

• One can use semisynchronous-replication between master and slave.

• Slave configuration is consolidated to one place. That is, if a master's immediate slave and replicating master should share the same configuration (e.g. rewrite rules, filters, etc), only the immediate slave needs them configured, as the replicating master will pull in the effects inherently.

Disadvantages of replication through slaves

• There will be a slightly longer delay for the data to hit the next master as it has to go trough the slave. This can be notable if there is a very large transaction executed on the master.

• If the master OR the slave dies, the replication to other masters will stop.

• A replicating master is subject to the configuration of a slave (e.g. transactions may be incorrectly filtered out).

• Re-setting replication after failover is a bit more complex.

Setting things up

• Setup is identical to Multi-Master Ring Replication, except that rpl_semi_sync_master_enabled=0 is not required.

What to do when one of the masters dies and has to be replaced by a slave.

Let assume that master1 in the above picture has failed.

What needs to be done is to replace master1 with slave1 and add a new slave to replace slave1. Here follows a step by step description of how to do this.

The new slave that will be added to replace slave1 place will below be called slave3. The new master will be called master3 (to simplify explanations). Note that in some cases, the failed master can be re-used as the new slave if it did recover properly. If this is the case, reset all replications setups on the failed master.

Note that when one sets up a master->slave replication, all configurations are done only on the slave!

• Promote slave1 as the new master3. Applications should now be moved to use master3. Note MaxScale can do this step automatically.

• Setup master3 as a slave of slave2.

  • The ring replication is now active (at least temporarily, until we have slave3 in place).

• Delete the old replication setup in master3 that pointed to the deleted master1.

• Prepare the new slave3 (which can take a while if it based on a backup of master3).

• Setup slave3 as a slave of master3.

• If delayed slave1 exists, redirect it to be a slave of slave3.

• Update master2 to be slave of slave3 (from being a slave of master3).

Some other options:

• For semi-sync setups, the old master1 can be re-used as slave3 if re-started with --init-rpl-role=SLAVE during recovery

• For non-semi-synchronous setups, one can use option CHANGE MASTER TO MASTER_DEMOTE_TO_SLAVE=1 (requires MariaDB 10.11 or higher).

See also

• Multi-source replication

_{This page is licensed: CC BY-SA / Gnu FDL}

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 Seconds_Behind_Master value depends on the timestamp of the last executed event, its value 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 still be growing until it commits. Then it will be changed to about 10 hours (more precisely to 10 hours + execution_time_on_slave - execution_time_on_master which corresponds to the above bullet). Since that the value will be growing until next event is executed and Seconds_behind_master is rebased on its completion time again. To expand more on this check a practical example in the comments.

• 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.

1. 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.

clock_difference_between_master_and_slave

When setting up a master and slaves it is important that the internal clock has the same configuration. In almost all unix systems the internal clock is in UTC by default. On Windows system it may be in local time. This should be changed to be in UTC if one uses MariaDB with replication!

In addition to being in UTC, one should ensure that the clocks are synchronized. If not properly synchronized, there is a gap between the values of the clocks between different machines. MariaDB replication takes this into account by comparing the master and slave clocks when the slave connects to the master. The difference is clock_difference_between_master_and_slave.

_{This page is licensed: CC BY-SA / Gnu FDL}

Some writes, replicated from the primary can be executed in parallel (simultaneously) on the replica. Note that for parallel replication to work, both the primary and replica need to be MariaDB 10.0.5 or later.

Parallel Replication Overview

MariaDB replication in general takes place in three parts:

• Replication events are read from the primary by the IO thread and queued in the relay log.

• Replication events are fetched one at a time by the SQL thread from the relay log

• Each event is applied on the replica to replicate all changes done on the primary.

Before MariaDB 10, the third step was also performed by the SQL thread; this meant that only one event could execute at a time, and replication was essentially single-threaded. Since MariaDB 10, the third step can optionally be performed by a pool of separate replication worker threads, and thereby potentially increase replication performance by applying multiple events in parallel.

How to Enable Parallel Replica

To enable, specify slave-parallel-threads=# in your my.cnf file as an argument to mysql. Parallel replication can in addition be disabled on a per-multi-source connection by setting @@connection_name.slave-parallel-mode to "none".

The value (#) of slave_parallel_threads specifies how many threads will be created in a pool of worker threads used to apply events in parallel for all your replicas (this includesmulti-source replication). If the value is zero, then no worker threads are created, and old-style replication is used where events are applied inside the SQL thread. Usually the value, if non-zero, should be at least two times the number of multi-source primary connections used. It makes little sense to use only a single worker thread for one connection; this will incur some overhead in inter-thread communication between the SQL thread and the worker thread, but with just a single worker thread events can not be applied in parallel anyway.

slave-parallel-threads=# is a dynamic variable that can be changed without restarting mysqld. All replicas connections must however be stopped when changing the value.

Configuring the Replica Parallel Mode

Parallel replication can be in-order or out-of-order:

• In-order executes transactions in parallel, but orders the commit step of the transactions to happen in the exact same order as on the primary. Transactions are only executed in parallel to the extent that this can be automatically verified to be possible without any conflicts. This means that the use of parallelism is completely transparent to the application.

• Out-of-order can execute and commit transactions in different order on the replica than originally on the primary. This means that the application must be tolerant to seeing updates occur in different order. The application is also responsible for ensuring that there are no conflicts between transactions that are replicated out-of-order. Out-of-order is only used in GTID mode and only when explicitly enabled by the application, using the replication domain that is part of the GTID.

In-Order Parallel Replication

Optimistic Mode of In-Order Parallel Replication

Optimistic mode of in-order parallel replication provides a lot of opportunities for parallel apply on the replica while still preserving exact transaction semantics from the point of view of applications. It is the default mode from MariaDB 10.5.1.

Optimistic mode of in-order parallel replication can be configured by setting the slave_parallel_mode system variable to optimistic on the replica.

Any transactional DML (INSERT/UPDATE/DELETE) is allowed to run in parallel, up to the limit of @@slave_domain_parallel_threads. This may cause conflicts on the replica, eg. if two transactions try to modify the same row. Any such conflict is detected, and the latter of the two transactions is rolled back, allowing the former to proceed. The latter transaction is then re-tried once the former has completed.

The term "optimistic" is used for this mode, because the server optimistically assumes that few conflicts will occur, and that the extra work spent rolling back and retrying conflicting transactions is justified from the gain from running most transactions in parallel.

There are a few heuristics to try to avoid needless conflicts. If a transaction executed a row lock wait on the primary, it will not be run in parallel on the replica. Transactions can also be marked explicitly as potentially conflicting on the primary, by setting the variable@@skip_parallel_replication. More such heuristics may be added in later MariaDB versions. There is a further --slave-parallel-mode called "aggressive", where these heuristics are disabled, allowing even more transactions to be applied in parallel.

Non-transactional DML and DDL is not safe to optimistically apply in parallel, as it cannot be rolled back in case of conflicts. Thus, in optimistic mode, non-transactional (such as MyISAM) updates are not applied in parallel with earlier events (it is however possible to apply a MyISAM update in parallel with a later InnoDB update). DDL statements are not applied in parallel with any other transactions, earlier or later.

The different kind of transactions can be identified in the output ofmariadb-binlog. For example:

#150324 13:06:26 server id 1  end_log_pos 6881 	GTID 0-1-42 ddl
...
#150324 13:06:26 server id 1  end_log_pos 7816 	GTID 0-1-47
...
#150324 13:06:26 server id 1  end_log_pos 8177  GTID 0-1-49 trans
/*!100101 SET @@session.skip_parallel_replication=1*//*!*/;
...
#150324 13:06:26 server id 1  end_log_pos 9836 	GTID 0-1-59 trans waited

GTID 0-1-42 is marked as being DDL. GTID 0-1-47 is marked as being non-transactional DML, while GTID 0-1-49 is transactional DML (seen on the "trans" keyword). GTID 0-1-49 was additionally run with@@skip_parallel_replication set on the primary. GTID 0-1-59 is transactional DML that had a row lock wait when run on the primary (the "waited" keyword).

Aggressive Mode of In-Order Parallel Replication

Aggressive mode of in-order parallel replication is very similar to optimistic mode. The main difference is that the replica does not consider whether transactions conflicted on the primary when deciding whether to apply the transactions in parallel.

Aggressive mode of in-order parallel replication can be configured by setting the slave_parallel_mode system variable to aggressive on the replica.

Conservative Mode of In-Order Parallel Replication

Conservative mode of in-order parallel replication uses the group commit on the primary to discover potential for parallel apply of events on the replica. If two transactions commit together in a group commit on the primary, they are written into the binlog with the same commit id. Such events are certain to not conflict with each other, and they can be scheduled by the parallel replication to run in different worker threads.

Conservative mode of in-order parallel replication is the default mode until MariaDB 10.5.0, but it can also be configured by setting the slave_parallel_mode system variable to conservative on the replica.

Two transactions that were committed separately on the primary can potentially conflict (eg. modify the same row of a table). Thus, the worker that applies the second transaction will not start immediately, but wait until the first transaction begins the commit step; at this point it is safe to start the second transaction, as it can no longer disrupt the execution of the first one.

Here is example output from mariadb-binlog that shows how GTID events are marked with commit id. The GTID 0-1-47 has no commit id, and can not run in parallel. The GTIDs 0-1-48 and 0-1-49 have the same commit id 630, and can thus replicate in parallel with one another on a replica:

#150324 12:54:24 server id 1  end_log_pos 20052 	GTID 0-1-47 trans
...
#150324 12:54:24 server id 1  end_log_pos 20212 	GTID 0-1-48 cid=630 trans
...
#150324 12:54:24 server id 1  end_log_pos 20372 	GTID 0-1-49 cid=630 trans

In either case, when the two transactions reach the point where the low-level commit happens and commit order is determined, the two commits are sequenced to happen in the same order as on the primary, so that operation is transparent to applications.

The opportunities for parallel replication on replicas can be highly increased if more transactions are committed in a group commit on the primary. This can be tuned using the binlog_commit_wait_count andbinlog_commit_wait_usec variables. If for example the application can tolerate up to 50 milliseconds extra delay for transactions on the primary, one can set binlog_commit_wait_usec=50000 andbinlog_commit_wait_count=20 to get up to 20 transactions at a time available for replication in parallel. Care must however be taken to not set binlog_commit_wait_usec too high, as this could cause significant slowdown for applications that run a lot of small transactions serially one after the other.

Note that even if there is no parallelism available from the primary group commit, there is still an opportunity for speedup from in-order parallel replication, since the actual commit steps of different transactions can run in parallel. This can be particularly effective on a replica with binlog enabled (log_slave_updates=1), and more so if replica is configured to be crash-safe (sync_binlog=1 andinnodb_flush_log_at_trx_commit=1), as this makes group commit possible on the replica.

Minimal Mode of In-Order Parallel Replication

Minimal mode of in-order parallel replication onlyallows the commit step of transactions to be applied in parallel; all other steps are applied serially.

Minimal mode of in-order parallel replication can be configured by setting the slave_parallel_mode system variable to minimal on the replica.

Out-of-Order Parallel Replication

Out-of-order parallel replication happens (only) when using GTID mode, when GTIDs with different replication domains are used. The replication domain is set by the DBA/application using the variable gtid_domain_id.

Two transactions having GTIDs with different domain_id are scheduled to different worker threads by parallel replication, and are allowed to execute completely independently from each other. It is the responsibility of the application to only set different domain_ids for transactions that are truly independent, and are guaranteed to not conflict with each other. The application must also be able to work correctly even though the transactions with different domain_id are seen as committing in different order between the replica and the primary, and between different replicas.

Out-of-order parallel replication can potentially give more performance gain than in-order parallel replication, since the application can explicitly give more opportunities for running transactions in parallel than what the server can determine on its own automatically.

One simple but effective usage is to run long-running statements, such as ALTER TABLE, in a separate replication domain. This allows replication of other transactions to proceed uninterrupted:

SET SESSION gtid_domain_id=1
ALTER TABLE t ADD INDEX myidx(b)
SET SESSION gtid_domain_id=0

Normally, a long-running ALTER TABLE or other query will stall all following transactions, causing the replica to become behind the primary as least as long time as it takes to run the long-running query. By using out-of-order parallel replication by setting the replication domain id, this can be avoided. The DBA/application must ensure that no conflicting transactions will be replicated while the ALTER TABLE runs.

Another common opportunity for out-of-order parallel replication comes in connection with multi-source replication. Suppose we have two different primaries M1 and M2, and we are using multi-source replication to have S1 as a replica of both M1 and M2. S1 will apply events received from M1 in parallel with events received from M2. If we now have a third-level replica S2 that replicates from S1 as primary, we want S2 to also be able to apply events that originated on M1 in parallel with events that originated on M2. This can be achieved with out-of-order parallel replication, by settinggtid_domain_id different on M1 and M2.

Note that there are no special restrictions on what operations can be replicated in parallel using out-of-order; such operations can be on the same database/schema or even on the same table. The only restriction is that the operations must not conflict, that is they must be able to be applied in any order and still end up with the same result.

When using out-of-order parallel replication, the current replica position in the primary's binlog becomes multi-dimensional - each replication domain can have reached a different point in the primary binlog at any one time. The current position can be seen from the variablegtid_slave_pos. When the replica is stopped, restarted, or switched to replicate from a different primary using CHANGE MASTER, MariaDB automatically handles restarting each replication domain at the appropriate point in the binlog.

Out-of-order parallel replication is disabled when--slave-parallel-mode=minimal (or none).

Checking Worker Thread Status in SHOW PROCESSLIST

The worker threads will be listed as "system user" in SHOW PROCESSLIST. Their state will show the query they are currently working on, or it can show one of these:

• "Waiting for work from main SQL threads". This means that the worker thread is idle, no work is available for it at the moment.

• "Waiting for prior transaction to start commit before starting next transaction". This means that the previous batch of transactions that committed together on the primary primary has to complete first. This worker thread is waiting for that to happen before it can start working on the following batch.

• "Waiting for prior transaction to commit". This means that the transaction has been executed by the worker thread. In order to ensure in-order commit, the worker thread is waiting to commit until the previous transaction is ready to commit before it.

Expected Performance Gain

Here is an article showing up to ten times improvement when using parallel replication: 18435.html.

Configuring the Maximum Size of the Parallel Replica Queue

The slave_parallel_max_queued system variable can be used to configure the maximum size of the parallel replica queue. This system variable is only meaningful when parallel replication is configured (i.e. when slave_parallel_threads > 0).

When parallel replication is used, the SQL thread will read ahead in the relay logs, queueing events in memory while looking for opportunities for executing events in parallel. The slave_parallel_max_queued system variable sets a limit for how much memory it will use for this.

The configured value of the slave_parallel_max_queued system variable is actually allocated for each worker thread, so the total allocation is actually equivalent to the following:

slave_parallel_max_queued * slave_parallel_threads

If this value is set too high, and the replica is far (eg. gigabytes of binlog) behind the primary, then the SQL thread can quickly read all of that and fill up memory with huge amounts of binlog events faster than the worker threads can consume them.

On the other hand, if set too low, the SQL thread might not have sufficient space for queuing enough events to keep the worker threads busy, which could reduce performance. In this case, the SQL thread will have the thread state that states Waiting for room in worker thread event queue. For example:

+----+-------------+-----------+------+---------+--------+-----------------------------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time   | State                                         | Info             | Progress |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------+------------------+----------+
|  3 | system user |           | NULL | Connect |    139 | closing tables                                | NULL             |    0.000 |
|  4 | system user |           | NULL | Connect |    139 | Waiting for work from SQL thread              | NULL             |    0.000 |
|  6 | system user |           | NULL | Connect | 264274 | Waiting for master to send event              | NULL             |    0.000 |
| 10 | root        | localhost | NULL | Sleep   |     43 |                                               | NULL             |    0.000 |
| 21 | system user |           | NULL | Connect |     45 | Waiting for room in worker thread event queue | NULL             |    0.000 |
| 54 | root        | localhost | NULL | Query   |      0 | init                                          | SHOW PROCESSLIST |    0.000 |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------+------------------+----------+

The slave_parallel_max_queued system variable does not define a hard limit, since the binary log events that are currently executing always need to be held in-memory. This means that at least two events per worker thread can always be queued in-memory, regardless of the value of slave_parallel_threads.

Usually, the slave_parallel_threads system variable should be set large enough that the SQL thread is able to read far enough ahead in the binary log to exploit all possible parallelism. In normal operation, the replica will hopefully not be too far behind, so there will not be a need to queue much data in-memory. The slave_parallel_max_queued system variable could be set fairly high (eg. a few hundred kilobytes) to not limit throughtput. It should just be set low enough that total allocation of the parallel replica queue will not cause the server to run out of memory.

Configuration Variable slave_domain_parallel_threads

The pool of replication worker threads is shared among all multi-source primary connections, and among all replication domains that can replicate in parallel using out-of-order.

If one primary connection or replication domain is currently processing a long-running query, it is possible that it will allocate all the worker threads in the pool, only to have them wait for the long-running query to complete, stalling any other primary connection or replication domain, which will have to wait for a worker thread to become free.

This can be avoided by settingslave_domain_parallel_threads to a number that is lower than slave_parallel_threads. When set different from zero, each replication domain in one primary connection can reserve at most that many worker threads at any one time, leaving the rest (up to the value ofslave_parallel_threads) free for other primary connections or replication domains to use in parallel.

The slave_domain_parallel_threads variable is dynamic and can be changed without restarting the server; all replicas must be stopped while changing it, though.

Implementation Details

The implementation is described in MDEV-4506.

See Also

• Better Parallel Replication for MariaDB and MySQL (MariaDB.com blog)

• Evaluating MariaDB & MySQL Parallel Replication Part 2: Slave Group Commit (MariaDB.com blog)

_{This page is licensed: CC BY-SA / Gnu FDL}

Description

Standard MariaDB replication is asynchronous, but MariaDB also provides a semisynchronous replication option. The feature is built into the server and is always available. In versions prior to MariaDB 10.3, it was a separate plugin that needed to be installed.

With regular asynchronous replication, replicas request events from the primary's binary log whenever the replicas are ready. The primary does not wait for a replica to confirm that an event has been received.

With fully synchronous replication, all replicas are required to respond that they have received the events. See Galera Cluster.

Semisynchronous replication waits for just one replica to acknowledge that it has received and logged the events.

Semisynchronous replication therefore comes with some negative performance impact, but increased data integrity. Since the delay is based on the roundtrip time to the replica and back, this delay is minimized for servers in close proximity over fast networks.

Semisynchronous replication is built into the server. See MDEV-13073 for more information.

Enabling Semisynchronous Replication

Semisynchronous replication can be enabled by setting the relevant system variables on the primary and the replica.

If a server needs to be able to switch between acting as a primary and a replica, then you can enable both the primary and replica system variables on the server. For example, you might need to do this if MariaDB MaxScale is being used to enable auto-failover or switchover with MariaDB Monitor.

Enabling Semisynchronous Replication on the Primary

Semisynchronous replication can be enabled on the primary by setting the rpl_semi_sync_master_enabled system variable to ON. It can be set dynamically with SET GLOBAL. For example:

SET GLOBAL rpl_semi_sync_master_enabled=ON;

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
rpl_semi_sync_master_enabled=ON

Enabling Semisynchronous Replication on the Replica

Semisynchronous replication can be enabled on the replica by setting the rpl_semi_sync_slave_enabled system variable to ON. It can be set dynamically with SET GLOBAL. For example:

SET GLOBAL rpl_semi_sync_slave_enabled=ON;

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
rpl_semi_sync_slave_enabled=ON

When switching between semisynchronous replication and asynchronous replication on a replica with replica IO threads already running, the replica I/O thread will need to be restarted. For example:

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

If this is not done, then the replica IO thread will continue to use the previous setting.

Configuring the Primary Timeout

In semisynchronous replication, only after the events have been written to the relay log and flushed does the replica acknowledge receipt of a transaction's events. If the replica does not acknowledge the transaction before a certain amount of time has passed, then a timeout occurs and the primary switches to asynchronous replication. This will be reflected in the primary's error log with messages like the following:

[Warning] Timeout waiting for reply of binlog (file: mariadb-1-bin.000002, pos: 538), semi-sync up to file , position 0.
[Note] Semi-sync replication switched OFF.

When this occurs, the Rpl_semi_sync_master_status status variable will be switched to OFF.

When at least one semisynchronous replica catches up, semisynchronous replication is resumed. This will be reflected in the primary's error log with messages like the following:

[Note] Semi-sync replication switched ON with replica (server_id: 184137206) at (mariadb-1-bin.000002, 215076)

When this occurs, the Rpl_semi_sync_master_status status variable will be switched to ON.

The number of times that semisynchronous replication has been switched off can be checked by looking at the value of the Rpl_semi_sync_master_no_times status variable.

If you see a lot of timeouts like this in your environment, then you may want to change the timeout period. The timeout period can be changed by setting the rpl_semi_sync_master_timeout system variable. It can be set dynamically with SET GLOBAL. For example:

SET GLOBAL rpl_semi_sync_master_timeout=20000;

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
rpl_semi_sync_master_timeout=20000

To determine a good value for the rpl_semi_sync_master_timeout system variable, you may want to look at the values of the Rpl_semi_sync_master_net_avg_wait_time and Rpl_semi_sync_master_tx_avg_wait_time status variables.

Configuring the Primary Wait Point

In semisynchronous replication, there are two potential points at which the primary can wait for the replica acknowledge the receipt of a transaction's events. These two wait points have different advantages and disadvantages.

The wait point is configured by the rpl_semi_sync_master_wait_point system variable. The supported values are:

• AFTER_SYNC

• AFTER_COMMIT

It can be set dynamically with SET GLOBAL. For example:

SET GLOBAL rpl_semi_sync_master_wait_point='AFTER_SYNC';

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
rpl_semi_sync_master_wait_point=AFTER_SYNC

When this variable is set to AFTER_SYNC, the primary performs the following steps:

1. Prepares the transaction in the storage engine.

2. Syncs the transaction to the binary log.

3. Waits for acknowledgement from the replica.

4. Commits the transaction to the storage engine.

5. Returns an acknowledgement to the client.

The effects of the AFTER_SYNC wait point are:

• All clients see the same data on the primary at the same time; after acknowledgement by the replica and after being committed to the storage engine on the primary.

• If the primary crashes, then failover should be lossless, because all transactions committed on the primary would have been replicated to the replica.

• However, if the primary crashes, then its binary log may also contain events for transactions that were prepared by the storage engine and written to the binary log, but that were never actually committed by the storage engine. As part of the server's automatic crash recovery process, the server may recover these prepared transactions when the server is restarted. This could cause the "old" crashed primary to become inconsistent with its former replicas when they have been reconfigured to replace the old primary with a new one. The old primary in such a scenario can be re-introduced only as a semisync replica. The server post-crash recovery of the server configured with rpl_semi_sync_slave_enabled = ON ensures through MDEV-21117 that the server will not have extra transactions. The reconfigured as semisync replica server's binlog gets truncated to discard transactions proven not to be committed, in any of their branches if they are multi-engine. Truncation does not occur though when there exists a non-transactional group of events beyond the truncation position in which case recovery reports an error. When the semisync replica recovery can't be carried out, the crashed primary may need to be rebuilt.

When this variable is set to AFTER_COMMIT, the primary performs the following steps:

1. Prepares the transaction in the storage engine.

2. Syncs the transaction to the binary log.

3. Commits the transaction to the storage engine.

4. Waits for acknowledgement from the replica.

5. Returns an acknowledgement to the client.

The effects of the AFTER_COMMIT wait point are:

• Other clients may see the committed transaction before the committing client.

• If the primary crashes, then failover may involve some data loss, because the primary may have committed transactions that had not yet been acknowledged by the replicas.

Versions

System Variables

rpl_semi_sync_master_enabled

• Description: Set to ON to enable semi-synchronous replication primary. Disabled by default.

• Commandline: --rpl-semi-sync-master-enabled[={0|1}]

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: OFF

rpl_semi_sync_master_timeout

• Description: The timeout value, in milliseconds, for semi-synchronous replication in the primary. If this timeout is exceeded in waiting on a commit for acknowledgement from a replica, the primary will revert to asynchronous replication.

  • When a timeout occurs, the Rpl_semi_sync_master_status status variable will also be switched to OFF.

  • See Configuring the Primary Timeout for more information.

• Commandline: --rpl-semi-sync-master-timeout[=#]

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 10000 (10 seconds)

• Range: 0 to 18446744073709551615

rpl_semi_sync_master_trace_level

• Description: The tracing level for semi-sync replication. Four levels are defined:

  • 1: General level, including for example time function failures.

  • 16: More detailed level, with more verbose information.

  • 32: Net wait level, including more information about network waits.

  • 64: Function level, including information about function entries and exits.

• Commandline: --rpl-semi-sync-master-trace-level[=#]

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 32

• Range: 0 to 18446744073709551615

rpl_semi_sync_master_wait_no_slave

• Description: If set to ON, the default, the replica count (recorded by Rpl_semi_sync_master_clients) may drop to zero, and the primary will still wait for the timeout period. If set to OFF, the primary will revert to asynchronous replication as soon as the replica count drops to zero.

• Commandline: --rpl-semi-sync-master-wait-no-slave[={0|1}]

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: ON

rpl_semi_sync_master_wait_point

• Description: Whether the transaction should wait for semi-sync acknowledgement after having synced the binlog (AFTER_SYNC), or after having committed in storage engine (AFTER_COMMIT, the default).

  • When this variable is set to AFTER_SYNC, the primary performs the following steps:

    1. Prepares the transaction in the storage engine.

    2. Syncs the transaction to the binary log.

    3. Waits for acknowledgement from the replica.

    4. Commits the transaction to the storage engine.

    5. Returns an acknowledgement to the client.

  • When this variable is set to AFTER_COMMIT, the primary performs the following steps:

    1. Prepares the transaction in the storage engine.

    2. Syncs the transaction to the binary log.

    3. Commits the transaction to the storage engine.

    4. Waits for acknowledgement from the replica.

    5. Returns an acknowledgement to the client.

  • In MariaDB 10.1.2 and before, this system variable does not exist. However, in those versions, the primary waits for the acknowledgement from replicas at a point that is equivalent to AFTER_COMMIT.

  • See Configuring the Primary Wait Point for more information.

• Commandline: --rpl-semi-sync-master-wait-point=value

• Scope: Global

• Dynamic: Yes

• Data Type: enum

• Default Value: AFTER_COMMIT

• Valid Values: AFTER_SYNC, AFTER_COMMIT

rpl_semi_sync_master_wait_for_slave_count

• Description: The number of replicas that need to acknowledge that they have received a transaction before the transaction can complete on the primary.

• Commandline: --rpl-semi-sync-master-wait-for-slave-count=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 1

• Range: 0 to 65535

• Introduced: MariaDB 12.1

rpl_semi_sync_slave_delay_master

• Description: Only write primary info file when ack is needed.

• Commandline: --rpl-semi-sync-slave-delay-master[={0|1}]

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: OFF

rpl_semi_sync_slave_enabled

• Description: Set to ON to enable semi-synchronous replication replica. Disabled by default.

• Commandline: --rpl-semi-sync-slave-enabled[={0|1}]

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: OFF

rpl_semi_sync_slave_kill_conn_timeout

• Description: Timeout for the mysql connection used to kill the replica io_thread's connection on primary. This timeout comes into play when stop slave is executed.

• Commandline: --rpl-semi-sync-slave-kill-conn-timeout[={0|1}]

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 5

• Range: 0 to 4294967295

rpl_semi_sync_slave_trace_level

• Description: The tracing level for semi-sync replication. The levels are the same as for rpl_semi_sync_master_trace_level.

• Commandline: --rpl-semi-sync-slave-trace_level[=#]

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 32

• Range: 0 to 18446744073709551615

Options

init-rpl-rol

• From MariaDB 10.6.19, MariaDB 10.11.9, MariaDB 11.1.6, MariaDB 11.2.5, MariaDB 11.4.3 and MariaDB 11.5.2, changes the condition for semi-sync recovery to truncate the binlog to instead use this option, when set to SLAVE. This avoids a possible error state where the replica’s state is ahead of the primary’s. See -init-rpl-role.

rpl-semi-sync_master

• Description: Controls how the server should treat the plugin when the server starts up.

  • Valid values are:

    • OFF - Disables the plugin without removing it from the mysql.plugins table.

    • ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.

    • FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.

    • FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.

  • See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.

• Commandline: --rpl-semi-sync-master=value

• Data Type: enumerated

• Default Value: ON

• Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT

• Removed: MariaDB 10.3.3

rpl-semi-sync_slave

• Description: Controls how the server should treat the plugin when the server starts up.

  • Valid values are:

    • OFF - Disables the plugin without removing it from the mysql.plugins table.

    • ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.

    • FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.

    • FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.

  • See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.

• Commandline: --rpl-semi-sync-slave=value

• Data Type: enumerated

• Default Value: ON

• Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT

• Removed: MariaDB 10.3.3

Status Variables

For a list of status variables added when the plugin is installed, see Semisynchronous Replication Plugin Status Variables.

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB improves on row-based replication (see binary log formats) of tables which have no primary key but do have some other index. This is based in part on the original Percona patch "row_based_replication_without_primary_key.patch", with some additional fixes and enhancements.

When row-based replication is used with UPDATE or DELETE, the slave needs to locate each replicated row based on the value in columns. If the table contains at least one index, an index lookup will be used (otherwise a table scan is needed for each row, which is extremely inefficient for all but the smallest table and generally to be avoided).

In MariaDB, the slave will try to choose a good index among any available:

• The primary key is used, if there is one.

• Else, the first unique index without NULL-able columns is used, if there is one.

• Else, a choice is made among any normal indexes on the table (e.g. aFULLTEXT index is not considered).

The choice of which of several non-unique indexes to use is based on the cardinality of indexes; the one that is most selective (has the smallest average number of rows per distinct tuple of column values) is preferred. Note that for this choice to be effective, for most storage engines (like MyISAM, InnoDB) it is necessary to make sure ANALYZE TABLE has been run on the slave, otherwise statistics about index cardinality will not be available. In the absence of index cardinality, the first unique index will be chosen, if any, else the first non-unique index.

Prior to MariaDB 5.3, the slave would always choose the first index without considering cardinality. The slave could even choose an unusable index (like FULLTEXT) if no other index was available (MySQL Bug #58997), causing row-based replication to break in this case; this was also fixed in MariaDB 5.3.

See Also

• What is MariaDB 5.3

_{This page is licensed: CC BY-SA / Gnu FDL}

A safe statement is generally deterministic; in other words the statement will always produce the same result. For example, an INSERT statement producing a random number will most likely produce a different result on the primary than on the replica, and so cannot be replicated safely.

When an unsafe statement is run, the current binary logging format determines how the server responds.

• If the binary logging format is statement-based, unsafe statements generate a warning and are logged normally.

• If the binary logging format is mixed, unsafe statements are logged using the row-based format, while safe statements use the statement-based format.

• If the binary logging format is row-based, all statements are logged normally, and the distinction between safe and unsafe is not made.

MariaDB tries to detect unsafe statements. When an unsafe statement is issued, a warning similar to the following is produced:

Note (Code 1592): Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

MariaDB also issues this warning for some classes of statements that are safe.

Unsafe Statements

The following statements are regarded as unsafe:

• INSERT ... ON DUPLICATE KEY UPDATE statements using tables with multiple primary or unique keys, as the order that the keys are checked in, and which affect the rows chosen to update, is not deterministic. The warning about this was removed, because we always check keys in the same order on the primary and replica if the primary and replica are using the same storage engine.

• INSERT-DELAYED. These statements are inserted in an indeterminate order.

• INSERT ... SELECT for a table that has an AUTO_INCREMENT column.

• INSERTs on tables with a composite primary key that has an AUTO_INCREMENT column that isn't the first column of the composite key.

• When a table has an AUTO_INCREMENT column and a trigger or stored procedure executes an UPDATE statement against the table.

• UPDATE statements that use LIMIT, since the order of the returned rows is unspecified. This applies even to statements using an ORDER BY clause, which are deterministic (a known bug). However, LIMIT 0 is an exception to this rule (see MDEV-6170), and these statements are safe for replication.

• When using a user-defined function.

• Statements using any of the following functions, which can return different results on the replica:

  • CURRENT_ROLE()

  • CURRENT_USER()

  • FOUND_ROWS()

  • GET_LOCK()

  • IS_FREE_LOCK()

  • IS_USED_LOCK()

  • JSON_TABLE()

  • LOAD_FILE()

  • MASTER_POS_WAIT()

  • RAND()

  • RANDOM_BYTES()

  • RELEASE_ALL_LOCKS()

  • RELEASE_LOCK()

  • ROW_COUNT()

  • SESSION_USER()

  • SLEEP()

  • SYSDATE()

  • SYSTEM_USER()

  • USER()

  • UUID()

  • UUID_SHORT().

• Statements which refer to log tables, since these may differ across servers.

• Statements which refer to self-logging tables. Statements following a read or write to a self-logging table within a transaction are also considered unsafe.

• Statements which refer to system variables (there are a few exceptions).

• LOAD DATA INFILE statements.

• Non-transactional reads or writes that execute after transactional reads within a transaction.

• If row-based logging is used for a statement, and the session executing the statement has any temporary tables, row-based logging is used for the remaining statements until the temporary table is dropped. This is because temporary tables can't use row-based logging, so if it is used due to one of the above conditions, all subsequent statements using that table are unsafe. The server deals with this situation by treating all statements in the session as unsafe for statement-based logging until the temporary table is dropped.

Safe Statements

The following statements are not deterministic, but are considered safe for binary logging and replication:

• CONNECTION_ID()

• CURDATE()

• CURRENT_DATE()

• CURRENT_TIME()

• CURRENT_TIMESTAMP()

• CURTIME()

• LAST_INSERT_ID()

• LOCALTIME()

• LOCALTIMESTAMP()

• NOW()

• UNIX_TIMESTAMP()

• UTC_DATE()

• UTC_TIME()

• UTC_TIMESTAMP()

Isolation Levels

Even when using safe statements, not all transaction isolation levels are safe with statement-based or mixed binary logging. While the REPEATABLE READ and SERIALIZABLE isolation levels can be used with both statement- and row-based replication, the READ COMMITTED and READ UNCOMMITTED isolation levels only support row-based replication, as with them isolation between transactions is not guaranteed at all, and different transaction orders on a replica, or when doing point-in-time recovery, would lead to different results than on the original master.

This restriction does not apply if only non-transactional storage engines are used.

See Also

• Replication and Foreign Keys

_{This page is licensed: CC BY-SA / Gnu FDL}

Replication filters allow users to configure replicas to intentionally skip certain events.

Binary Log Filters for Replication Primaries

MariaDB provides options that can be used on a replication primary to restrict local changes to specific databases from getting written to the binary log, which also determines whether any replicas replicate those changes.

Binary Log Filter Options

The following options are available, and they are evaluated in the order that they are listed below. If there are conflicting settings, binlog_do_db prevails. Before MariaDB 11.2.0, they are only available as options; from MariaDB 11.2.0 they are also available as system variables.

binlog_do_db

The binlog_do_db option allows you to configure a replication primary to write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replicas will not be able to replicate them.

This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

This option can not be set dynamically.

When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times. For example:

[mariadb]
...
binlog_do_db=db1
binlog_do_db=db2

This will tell the primary to do the following:

• Write statements and transactions affecting the database named db1 into the binary log.

• Write statements and transactions affecting the database named db2 into the binary log.

• Don't write statements and transactions affecting any other databases into the binary log.

binlog_ignore_db

The binlog_ignore_db option allows you to configure a replication primary to not write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replicas will not be able to replicate them.

This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

This option can not be set dynamically.

When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times. For example:

[mariadb]
...
binlog_ignore_db=db1
binlog_ignore_db=db2

This will tell the primary to do the following:

• Don't write statements and transactions affecting the database named db1 into the binary log.

• Don't write statements and transactions affecting the database named db2 into the binary log.

• Write statements and transactions affecting any other databases into the binary log.

The binlog_ignore_db option is effectively ignored if the binlog_do_db option is set, so those two options should not be set together.

Replication Filters for Replicas

MariaDB provides options and system variables that can be used on used on a replicas to filter events replicated in the binary log.

Replication Filter Options

The following options and system variables are available, and they are evaluated in the order that they are listed below. If there are conflicting settings, the respective replicate_do_ prevails.

replicate_rewrite_db

The replicate_rewrite_db option (and, from MariaDB 10.11, system variable), allows you to configure a replica to rewrite database names. It uses the format primary_database->replica_database. If a replica encounters a binary log event in which the default database (i.e. the one selected by the USE statement) is primary_database, then the replica will apply the event in replica_database instead.

This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

This option only affects statements that involve tables. This option does not affect statements involving the database itself, such as CREATE DATABASE, ALTER DATABASE, and DROP DATABASE.

This option's rewrites are evaluated before any other replication filters configured by the replicate_* system variables.

Statements that use table names qualified with database names do not work with other replication filters such as replicate_do_table.

Until MariaDB 10.11, this option could not be set dynamically.

When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times. For example:

[mariadb]
...
replicate_rewrite_db=db1->db3
replicate_rewrite_db=db2->db4

This will tell the replica to do the following:

• If a binary log event is encountered in which the default database was db1, then apply the event in db3 instead.

• If a binary log event is encountered in which the default database was db2, then apply the event in db4 instead.

See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.

replicate_do_db

The replicate_do_db system variable allows you to configure a replica to apply statements and transactions affecting databases that match a specified name.

This system variable will not work with cross-database updates with statement-based logging or when using mixed-based logging and the statement is logged statement based. For statement-based replication, only the default database (that is, the one selected by USE) is considered, not any explicitly mentioned tables in the query. See the Statement-Based Logging section for more information.

When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.

When setting it dynamically, it is not possible to specify database names that contain commas. If you need to specify database names that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server option group in an option file when the server is started.

When setting it dynamically, the replica threads must be stopped. For example:

STOP SLAVE;
SET GLOBAL replicate_do_db='db1,db2';
START SLAVE;

When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:

[mariadb]
...
replicate_do_db=db1
replicate_do_db=db2

This will tell the replica to do the following:

• Replicate statements and transactions affecting the database named db1.

• Replicate statements and transactions affecting the database named db2.

• Ignore statements and transactions affecting any other databases.

See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.

replicate_ignore_db

The replicate_ignore_db system variable allows you to configure a replica to ignore statements and transactions affecting databases that match a specified name.

This system variable will not work with cross-database updates with statement-based logging or when using mixed-based logging and the statement is logged statement based. For statement-based replication, only the default database (that is, the one selected by USE) is considered, not any explicitly mentioned tables in the query. See the Statement-Based Logging section for more information.

When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.

When setting it dynamically, it is not possible to specify database names that contain commas. If you need to specify names or patterns that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server option group in an option file when the server is started.

When setting it dynamically, the replica threads must be stopped. For example:

STOP SLAVE;
SET GLOBAL replicate_ignore_db='db1,db2';
START SLAVE;

When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:

[mariadb]
...
replicate_ignore_db=db1
replicate_ignore_db=db2

This will tell the replica to do the following:

• Ignore statements and transactions affecting databases named db1.

• Ignore statements and transactions affecting databases named db2.

• Replicate statements and transactions affecting any other databases.

The replicate_ignore_db system variable is effectively ignored if the replicate_do_db system variable is set, so those two system variables should not be set together.

See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.

replicate_do_table

The replicate_do_table system variable allows you to configure a replica to apply statements and transactions that affect tables that match a specified name. The table name is specified in the format: dbname.tablename.

This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

This option only affects statements that involve tables. This option does not affect statements involving the database itself, such as CREATE DATABASE, ALTER DATABASE, and DROP DATABASE.

When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.

When setting it dynamically, it is not possible to specify database or table names or patterns that contain commas. If you need to specify database or table names that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server option group in an option file when the server is started.

When setting it dynamically, the replica threads must be stopped. For example:

STOP SLAVE;
SET GLOBAL replicate_do_table='db1.tab,db2.tab';
START SLAVE;

When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:

[mariadb]
...
replicate_do_table=db1.tab
replicate_do_table=db2.tab

This will tell the replica to do the following:

• Replicate statements and transactions affecting tables in databases named db1 and which are named tab.

• Replicate statements and transactions affecting tables in databases named db2 and which are named tab.

• Ignore statements and transactions affecting any other tables.

See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.

replicate_ignore_table

The replicate_ignore_table system variable allows you to configure a replica to ignore statements and transactions that affect tables that match a specified name. The table name is specified in the format: dbname.tablename.

This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.

When setting it dynamically, it is not possible to specify database or table names that contain commas. If you need to specify database or table names that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server option group in an option file when the server is started.

When setting it dynamically, the replica threads must be stopped. For example:

STOP SLAVE;
SET GLOBAL replicate_ignore_table='db1.tab,db2.tab';
START SLAVE;

When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:

[mariadb]
...
replicate_ignore_table=db1.tab
replicate_ignore_table=db2.tab

This will tell the replica to do the following:

• Ignore statements and transactions affecting tables in databases named db1 and which are named tab.

• Ignore statements and transactions affecting tables in databases named db2 and which are named tab.

• Replicate statements and transactions affecting any other tables.

The replicate_ignore_table system variable is effectively ignored if either the replicate_do_table system variable or the replicate_wild_do_table system variable is set, so the replicate_ignore_table system variable should not be used with those two system variables.

See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.

replicate_wild_do_table

The replicate_wild_do_table system variable allows you to configure a replica to apply statements and transactions that affect tables that match a specified wildcard pattern.

The wildcard pattern uses the same semantics as the LIKE operator. This means that the following characters have a special meaning:

• _ - The _ character matches any single character.

• % - The % character matches zero or more characters.

• \ - The \ character is used to escape the other special characters in cases where you need the literal character.

This system variable will work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

The system variable does filter databases, tables, views and triggers.

The system variable does not filter stored procedures, stored functions, and events. The replicate_do_db system variable will need to be used to filter those.

If the table name pattern for a filter is just specified as %, then all tables in the database will be matched. In this case, the filter will also affect certain database-level statements, such as CREATE DATABASE, ALTER DATABASE and DROP DATABASE.

When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.

When setting it dynamically, it is not possible to specify database or table names or patterns that contain commas. If you need to specify database or table names or patterns that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server option group in an option file when the server is started.

When setting it dynamically, the replica threads must be stopped. For example:

STOP SLAVE;
SET GLOBAL replicate_wild_do_table='db%.tab%,app1.%';
START SLAVE;

When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:

[mariadb]
...
replicate_wild_do_table=db%.tab%
replicate_wild_do_table=app1.%

This will tell the replica to do the following:

• Replicate statements and transactions affecting tables in databases that start with db and whose table names start with tab.

• Replicate statements and transactions affecting the database named app1.

• Ignore statements and transactions affecting any other tables and databases.

See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.

replicate_wild_ignore_table

The replicate_wild_ignore_table system variable allows you to configure a replica to ignore statements and transactions that affect tables that match a specified wildcard pattern.

The wildcard pattern uses the same semantics as the LIKE operator. This means that the following characters have a special meaning:

• _ - The _ character matches any single character.

• % - The % character matches zero or more characters.

• \ - The \ character is used to escape the other special characters in cases where you need the literal character.

This system variable will work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

The system variable does filter databases, tables, views and triggers.

The system variable does not filter stored procedures, stored functions, and events. The replicate_ignore_db system variable will need to be used to filter those.

If the table name pattern for a filter is just specified as %, then all tables in the database will be matched. In this case, the filter will also affect certain database-level statements, such as CREATE DATABASE, ALTER DATABASE and DROP DATABASE.

When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.

When setting it dynamically, it is not possible to specify database or table names or patterns that contain commas. If you need to specify database or table names or patterns that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server option group in an option file when the server is started.

When setting it dynamically, the replica threads must be stopped. For example:

STOP SLAVE;
SET GLOBAL replicate_wild_ignore_table='db%.tab%,app1.%';
START SLAVE;

When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:

[mariadb]
...
replicate_wild_ignore_table=db%.tab%
replicate_wild_ignore_table=app1.%

This will tell the replica to do the following:

• Ignore statements and transactions affecting tables in databases that start with db and whose table names start with tab.

• Ignore statements and transactions affecting all the tables in the database named app1.

• Replicate statements and transactions affecting any other tables and databases.

The replicate_ignore_table system variable is effectively ignored if either the replicate_do_table system variable or the replicate_wild_do_table system variable is set, so the replicate_ignore_table system variable should not be used with those two system variables.

See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.

Configuring Replication Filter Options with Multi-Source Replication

How you configure replication filters with multi-source replication depends on whether you are configuring them dynamically or whether you are configuring them in a server option group in an option file.

Setting Replication Filter Options Dynamically with Multi-Source Replication

The usage of dynamic replication filters changes somewhat when multi-source replication is in use. By default, the variables are addressed to the default connection, so in a multi-source environment, the required connection needs to be specified. There are two ways to do this.

Prefixing the Replication Filter Option with the Connection Name

One way to change a replication filter for a multi-source connection is to explicitly specify the name when changing the filter. For example:

STOP SLAVE 'gandalf';
SET GLOBAL gandalf.replicate_do_table='database1.table1,database1.table2,database1.table3';
START SLAVE 'gandalf';

Changing the Default Connection

Alternatively, the default connection can be changed by setting the default_master_connection system variable, and then the replication filter can be changed in the usual fashion. For example:

SET default_master_connection = 'gandalf';
STOP SLAVE; 
SET GLOBAL replicate_do_table='database1.table1,database1.table2,database1.table3';
START SLAVE;

Setting Replication Filter Options in Option Files with Multi-Source Replication

If you are using multi-source replication and if you would like to make this filter persist server restarts by adding it to a server option group in an option file, then the option file can also include the connection name that each filter would apply to. For example:

[mariadb]
...
gandalf.replicate_do_db=database1
saruman.replicate_do_db=database2

CHANGE MASTER Options

The CHANGE MASTER statement has a few options that can be used to filter certain types of binary log events.

IGNORE_SERVER_IDS

The IGNORE_SERVER_IDS option for CHANGE MASTER can be used to configure a replica to ignore binary log events that originated from certain servers. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.

DO_DOMAIN_IDS

The DO_DOMAIN_IDS option for CHANGE MASTER can be used to configure a replica to only apply binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.

IGNORE_DOMAIN_IDS

The IGNORE_DOMAIN_IDS option for CHANGE MASTER can be used to configure a replica to ignore binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.

Replication Filters and Binary Log Formats

The way that a replication filter is interpreted can depend on the binary log format.

Statement-Based Logging

When an event is logged in its statement-based format, many replication filters that affect a database will test the filter against the default database (i.e. the one selected by the USE statement). This applies to the following replication filters:

• binlog_do_db

• binlog_ignore_db

• replicate_rewrite_db

• replicate_do_db

• replicate_ignore_db

When an event is logged in its statement-based format, many replication filters that affect a table will test the filter against the table in the default database (i.e. the one selected by the USE statement). This applies to the following replication filters:

• replicate_do_table

• replicate_ignore_table

This means that cross-database updates not work with replication filters and statement-based binary logging. For example, if replicate_do_table=db2.tab were set, then the following would not replicate with statement-based binary logging:

USE db1;
INSERT INTO db2.tab VALUES (1);

If you need to be able to support cross-database updates with replication filters and statement-based binary logging, then you should use the following replication filters:

• replicate_wild_do_table

• replicate_wild_ignore_table

Row-Based Logging

When an event is logged in its row-based format, many replication filters that affect a database will test the filter against the database that is actually affected by the event.

Similarly, when an event is logged in its row-based format, many replication filters that affect a table will test the filter against the table in the database that is actually affected by the event.

This means that cross-database updates work with replication filters and statement-based binary logging.

Keep in mind that DDL statements are always logged to the binary log in statement-based format, even when the binlog_format system variable is set to ROW. This means that the notes mentioned in Statement-Based Logging always apply to DDL.

Replication Filters and Galera Cluster

When using Galera cluster, replication filters should be used with caution. See Configuring MariaDB Galera Cluster: Replication Filters for more details.

See Also

• Dynamic replication filters — our wheel will be square!

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB's replication implementation requires several types of threads.

Threads on the Primary

The primary usually only has one type of replication-related thread: the binary log dump thread.

If semisynchronous replication is enabled, then the primary also has an ACK receiver thread.

Binary Log Dump Thread

The binary log dump thread runs on the primary and dumps the binary log to the replica. This thread can be identified by running the SHOW PROCESSLIST statement and finding the thread where the thread command is "Binlog Dump".

The primary creates a separate binary log dump thread for each replica connected to the primary. You can identify which replicas are connected to the primary by executing the SHOW SLAVE HOSTS statement.

Binary Log Dump Threads and the Shutdown Process

When a primary server is shutdown and it goes through the normal shutdown process, the primary kills client threads in random order. By default, the primary also considers its binary log dump threads to be regular client threads. As a consequence, the binary log dump threads can be killed while client threads still exist, and this means that data can be written on the primary during a normal shutdown that won't be replicated. This is true even if semi-synchronous replication is being used. Data is not lost, it is stored in the primary server's binary log. The replicas on reconnection, after the primary server restarts, will resume at the exact position they where killed off during the primary shutdown. No data is lost.

In MariaDB 10.4 and later, this problem can be solved by shutting down the server using either the mariadb-admin utility or the SHUTDOWN command, and providing a special option.

For example, this problem can be solved by shutting down the server with the mariadb-admin utility and by providing the --wait-for-all-slaves option to the utility and by executing the shutdown command with the utility:

mariadb-admin --wait-for-all-slaves shutdown

Or this problem can be solved by shutting down the server with the SHUTDOWN command and by providing the WAIT FOR ALL SLAVES option to the command:

SHUTDOWN WAIT FOR ALL SLAVES;

When one of these special options is provided, the server only kills its binary log dump threads after all client threads have been killed, and it only completes the shutdown after the last binary log has been sent to all connected replicas.

In MariaDB 10.4 and later, it is still not possible to enable this behavior by default. This means that this behavior is currently inaccessible when shutting down the server using tools like systemd or sysVinit.

In MariaDB 10.3 and before, it is recommended to manually switchover replicas to a new primary before shutting down the old primary.

ACK Receiver Thread

When semisynchronous replication is enabled, semisynchronous replicas send acknowledgements (ACKs) to their primary to confirm that they have received some transaction. The primary creates an ACK receiver thread to receive these ACKs.

Threads on the Replica

The replica has three types of replication-related threads: the replica I/O thread, the replica SQL thread, and worker threads, which are only applicable when parallel replication is in use.

When multi-source replication is in use, each independent replication connection has its own replica threads of each type.

Replica I/O Thread

The replica's I/O thread receives the binary log events from the primary and writes them to its relay log.

Binary Log Position

The binary log position of the replica's I/O thread can be checked by executing the SHOW SLAVE STATUS statement. It will be shown as the Master_Log_File and Read_Master_Log_Pos columns.

The binary log position of the replica's I/O thread can be set by setting the MASTER_LOG_FILE and MASTER_LOG_POS options with the CHANGE MASTER statement.

The binary log position of the replica's I/O thread and the values of most other CHANGE MASTER options are written to either the default master.info file or the file that is configured by the master_info_file option. The replica's I/O thread keeps this binary log position updated as it downloads events only when the MASTER_USE_GTID option is set to NO. Otherwise the file is not updated on a per event basis. See CHANGE MASTER TO: Option Persistence for more information.

Replica SQL Thread

The replica's SQL thread reads events from the relay log. What it does with them depends on whether parallel replication is in use. If parallel replication is not in use, then the SQL thread applies the events to its local copy of the data. If parallel replication is in use, then the SQL thread hands off the events to its worker threads to apply in parallel.

Relay Log Position

The relay log position of the replica's SQL thread can be checked by executing the SHOW SLAVE STATUS statement. It will be shown as the Relay_Log_File and Relay_Log_Pos columns.

The relay log position of the replica's SQL thread can be set by setting the RELAY_LOG_FILE and RELAY_LOG_POS options with the CHANGE MASTER statement.

The relay log position of the replica's SQL thread is written to either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable. The replica's SQL thread keeps this relay log position updated as it applies events. See CHANGE MASTER TO: Option Persistence for more information.

Binary Log Position

The corresponding binary log position of the current relay log position of the replica's SQL thread can be checked by executing the SHOW SLAVE STATUS statement. It will be shown as the Relay_Master_Log_File and Exec_Master_Log_Pos columns.

GTID Position

If the replica is replicating binary log events that contain GTIDs, then the replica's's SQL thread will write every GTID that it applies to the mysql.gtid_slave_pos table. This GTID can be inspected and modified through the gtid_slave_pos system variable.

If the replica has the log_slave_updates system variable enabled and if the replica has the binary log enabled, then every write by the replica's SQL thread will also go into the replica's binary log. This means that GTIDs of replicated transactions would be reflected in the value of the gtid_binlog_pos system variable.

See CHANGE MASTER TO: GTID Persistence for more information.

Worker Threads

When parallel replication is in use, then the SQL thread hands off the events to its worker threads to apply in parallel.

_{This page is licensed: CC BY-SA / Gnu FDL}

Replication is based upon the binary log. However, cascading deletes or updates based on foreign key relations are an internal mechanism, and are not written to the binary log.

Because of this, an identical statement run on the master and the slave may result in different outcomes if the foreign key relations are not identical on both master and slave This could be the case if the storage engine on one supports cascading deletes (e.g. InnoDB) and the storage engine on the other does not (e.g. MyISAM), or the one has specified a foreign key relation, and the other hasn't.

Take the following example:

CREATE TABLE employees (
    x INT PRIMARY KEY,
    name VARCHAR(10)
) ENGINE = InnoDB;

CREATE TABLE children (
    y INT PRIMARY KEY,
    f INT,
    name VARCHAR(10),
    FOREIGN KEY fk (f) REFERENCES employees (x)
        ON DELETE CASCADE
) ENGINE = InnoDB;

The slave, however, has been set up without InnoDB support, and defaults to MyISAM, so the foreign key restrictions are not in place.

INSERT INTO employees VALUES (1, 'Yaser'), (2, 'Prune');

INSERT INTO children VALUES (1, 1, 'Haruna'), (2, 1, 'Hera'), (3, 2, 'Eva');

At this point, the slave and the master are in sync:

SELECT * FROM employees;
+---+-------+
| x | name  |
+---+-------+
| 1 | Yaser |
| 2 | Prune |
+---+-------+
2 rows in set (0.00 sec)

SELECT * FROM children;
+---+------+--------+
| y | f    | name   |
+---+------+--------+
| 1 |    1 | Haruna |
| 2 |    1 | Hera   |
| 3 |    2 | Eva    |
+---+------+--------+

However, after:

DELETE FROM employees WHERE x=1;

there are different outcomes on the slave and the master.

On the master, the cascading deletes have taken effect:

SELECT * FROM children;
+---+------+------+
| y | f    | name |
+---+------+------+
| 3 |    2 | Eva  |
+---+------+------+

On the slave, the cascading deletes did not take effect:

SELECT * FROM children;
+---+------+--------+
| y | f    | name   |
+---+------+--------+
| 1 |    1 | Haruna |
| 2 |    1 | Hera   |
| 3 |    2 | Eva    |
+---+------+--------+

_{This page is licensed: CC BY-SA / Gnu FDL}

This page lists system variables that are related to binary logging and replication.

See Server System Variables for a complete list of system variables and instructions on setting them, as well as System variables for global transaction ID.

Also see mariadbd replication options for related options that are not system variables (such as binlog_do_db and binlog_ignore_db).

See also the Full list of MariaDB options, system and status variables.

auto_increment_increment

• Description: The increment for all AUTO_INCREMENT values on the server, by default 1. Intended for use in primary-to-primary replication.

• Commandline: --auto-increment-increment[=#]

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 1

• Range: 1 to 65535

auto_increment_offset

• Description: The offset for all AUTO_INCREMENT values on the server, by default 1. Intended for use in primary-to-primary replication. Should be not be larger than auto_increment_increment. See AUTO_INCREMENT#Replication.

• Commandline: --auto-increment-offset[=#]

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 1

• Range: 1 to 65535

binlog_alter_two_phase

• Description: When set, split ALTER at binary logging into two statements: START ALTER and COMMIT/ROLLBACK ALTER. The ON setting is recommended for long-running ALTER-table so it could start on replica before its actual execution on primary.

• Commandline: --binlog-alter-two-phase[={0|1}]

• Scope: Global, Session

• Dynamic: Yes

• Data Type: boolean

• Default Value: OFF

• Introduced: MariaDB 10.8.1

binlog_annotate_row_events

• Description: This option tells the primary to write annotate_rows_events to the binary log.

• Commandline: --binlog-annotate-row-events[={0|1}]

• Scope: Global, Session

• Dynamic: Yes

• Data Type: boolean

• Default Value:

  • ON (>= MariaDB 10.2.4)

  • OFF (<= MariaDB 10.2.3)

binlog_cache_size

• Description: If the binary log is active, this variable determines the size in bytes, per-connection, of the cache holding a record of binary log changes during a transaction. A separate variable, binlog_stmt_cache_size, sets the upper limit for the statement cache. The binlog_cache_disk_use and binlog_cache_use server status variables will indicate whether this variable needs to be increased (you want a low ratio of binlog_cache_disk_use to binlog_cache_use).

• Commandline: --binlog-cache-size=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 32768

• Range - 32 bit: 4096 to 4294967295

• Range - 64 bit: 4096 to 18446744073709547520

binlog_checksum

• Description: Specifies the type of BINLOG_CHECKSUM_ALG for log events in the binary log.

• Commandline:

  • --binlog-checksum=name

  • --binlog-checksum=[0|1]

• Scope: Global

• Dynamic: Yes

• Data Type: string

• Default Value:

  • CRC32 (>= MariaDB 10.2.1)

  • NONE (<= MariaDB 10.2.0)

• Valid Values: NONE (0), CRC32 (1)

binlog_commit_wait_count

• Description: Configures the behavior of group commit for the binary log, which can help increase transaction throughput and is used to enable conservative mode of in-order parallel replication. With group commit for the binary log, the server can delay flushing a committed transaction into binary log until the given number of transactions are ready to be flushed as a group. The delay will however not be longer than the value set by binlog_commit_wait_usec. The default value of 0 means that no delay is introduced. Setting this value can reduce I/O on the binary log and give an increased opportunity for parallel apply on the replica when conservative mode of in-order parallel replication is enabled, but too high a value will decrease the transaction throughput. By monitoring the status variable binlog_group_commit_trigger_count (>=MariaDB 10.1.5) it is possible to see how often this is occurring.

• Starting with MariaDB 10.0.18 and MariaDB 10.1.4: If the server detects that one of the committing transactions T1 holds an InnoDB row lock that another transaction T2 is waiting for, then the commit will complete immediately without further delay. This helps avoid losing throughput when many transactions need conflicting locks. This often makes it safe to use this option without losing throughput on a replica with conservative mode of in-order parallel replication, provided the value ofslave_parallel_threads is sufficiently high.

• Commandline: --binlog-commit-wait-count=#]

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 0

• Range: 0 to 18446744073709551615

binlog_commit_wait_usec

• Description: Configures the behavior of group commit for the binary log, which can help increase transaction throughput and is used to enable conservative mode of in-order parallel replication. With group commit for the binary log, the server can delay flushing a committed transaction into binary log until the transaction has waited the configured number of microseconds. By monitoring the status variable binlog_group_commit_trigger_timeout (>=MariaDB 10.1.5) it is possible to see how often group commits are made due to binlog_commit_wait_usec. As soon as the number of pending commits reaches binlog_commit_wait_count, the wait will be terminated, though. Thus, this setting only takes effect if binlog_commit_wait_count is non-zero.

• Commandline: --binlog-commit-wait-usec#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 100000

• Range: 0 to 18446744073709551615

binlog_direct_non_transactional_updates

• Description: Replication inconsistencies can occur due when a transaction updates both transactional and non-transactional tables and the updates to the non-transactional tables are visible before being written to the binary log. This is because, to preserve causality, the non-transactional statements are written to the transaction cache, which is only flushed on commit. Setting binlog_direct_non_transactional_updates to 1 (0 is default) will cause non-transactional tables to be written straight to the binary log, rather than the transaction cache. This setting has no effect when row-based binary logging is used, as it requires statement-based logging. See binlog_format. Use with care, and only in situations where no dependencies exist between the non-transactional and transactional tables, for example INSERTing into a non-transactional table based upon the results of a SELECT from a transactional table.

• Commandline: --binlog-direct-non-transactional-updates[=value]

• Scope: Global, Session

• Dynamic: Yes

• Data Type: boolean

• Default Value: OFF (0)

binlog_do_db

• Description: This option allows you to configure a replication primary to write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replicas will not be able to replicate them.

  • This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.

  • Until MariaDB 11.2.0, only available as an option, not a system variable. This option can not be set dynamically.

  • When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.

  • See Replication Filters for more information.

• Commandline: --binlog-do-db=#

• Scope: Global

• Dynamic: No

• Data Type: string

• Default Value: NULL

• Introduced: MariaDB 11.2.0 (as a system variable)