1 of 31

Replication

This section covers configuring and managing master-replica setups to ensure data redundancy and improve read scalability.

Replication Overview

Explore the core concepts of MariaDB standard replication. Learn about the primary-replica architecture, data redundancy strategies, and how to scale read operations effectively.

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

Common Replication Setups

Standard Replication

Provides infinite read scale out.
Provides high-availability by upgrading replica to primary.

Ring Replication

Provides read and write scaling.
Doesn’t handle conflicts.
If one primary fails, replication stops.

Ring Replication with slaves

Provides read and write scaling.
Doesn’t handle conflicts.
If one primary fails, replication stops.

Ring Replication with replication through slaves

Provides read and write scaling.
Doesn’t handle conflicts.
If one primary fails, replication stops.

Star Replication

Provides read and write scaling.
Doesn’t handle conflicts.
Have to use replication filters to avoid duplication of data.
, which is a 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.

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 for more details on unsafe statements.

For replication compatibility details between MariaDB and MySQL, see .

Replication Statements

Access the complete reference of SQL statements used to manage replication. This guide covers commands for controlling primaries, configuring replicas, and monitoring status.

Replication SQL statements can be found in the Reference section.

Read-Only Replicas

Learn how to configure replicas as read-only instances. This ensures data integrity by preventing accidental writes on the replica while allowing it to process replication events.

A common replication setup is to have the replicas read-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 (<= ) or READ ONLY ADMIN privilege (>= ) 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 (<= ) or READ ONLY ADMIN privilege (>= ).

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.

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 while running as root during maintenance:

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}

Delayed Replication

Learn to configure a time lag for your replica. Delayed replication helps recover from human errors on the primary, such as accidental drop commands, by preserving older states.

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

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

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

Delayed replication is enabled using the MASTER_DELAY option to :

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

New Variables to Measure Replication Lag

MariaDB 10.6 ES and 11.6 introduced new variables to measure replication lag. The variables are recommended to monitor the time difference between the master and slave.

Master_last_event_time: The timestamp of the last event read from the master by the IO thread.

Fields in are associated with delayed replication

SQL_Delay: This is the value specified by MASTER_DELAY in CHANGE MASTER (or 0 if none).
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.
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

Slave_SQL_Running_State: This shows the state of the SQL driver threads, same as in . 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 , a 3rd party tool called 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}

Row-based Replication With No Primary Key

Understand the performance implications and best practices for replicating tables without primary keys when using row-based logging, including how to avoid full table scans.

MariaDB improves on row-based (see ) 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 or , 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).

Replication and Foreign Keys

Understand how foreign key constraints interact with replication. Learn best practices for managing cascading deletes and updates across primary and replica servers.

Replication is based upon the . 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.

Binlog Event Checksum Interoperability

Learn about checksum compatibility between different MariaDB versions. Ensure older replicas can correctly verify binary log events generated by newer primaries.

The introduction of checksums on binlog events changes the format that events are stored in binary log files and sent over the network to replicas. This raises the question on what happens when replicating between different versions of the server, where one server is a newer version that has the binlog checksum feature implemented, while the other server is an older version that does not know about binlog checksums.

When checksums are disabled on the primary (or the primary has the old version with no checksums implemented), there is no problem. In this case the binlog format is backwards compatible, and replication works fine.

When the primary is a newer version with checksums enabled in the binlog, but the replica is an old version that does not understand checksums, replication will fail. The primary will disconnect the replica with an error, and also log a warning in its own error log. This prevents sending events to the replica that it will be unable to interpret correctly, but means that binlog checksums can not be used with older replicas. (With the recommended upgrade path, where replicas are upgraded before primaries, this is not a problem of course).

Replicating from a new MySQL primary with checksums enabled to a new MariaDB which also understands checksums works, and the MariaDB replica will verify checksums on replicated events.

There is however a problem when a newer MySQL replica replicates against a newer MariaDB primary with checksums enabled. The replica server looks at the primary server version to know whether events include checksums or not, and MySQL has not yet been updated to learn that MariaDB does this already from version 5.3.0 (as of the time of writing, MySQL 5.6.2). Thus, if MariaDB at least version 5.3.0 but less that 5.6.1 is used as a primary with binlog checksums enabled, a MySQL replica will interpret the received events incorrectly as it does not realise the last part of the events is the checksum. So replication will fail with an error about corrupt events or even silent corruption of replicated data in unlucky cases. This requires changes to the MySQL server to fix.

Here is a summary table of the status of replication between different combination of primary and replica servers and checksum enabled/disabled:

OLD: MySQL <5.6.1 or MariaDB < 5.3.0 with no checksum capabilities
NEW-MARIA: MariaDB >= 5.3.0 with checksum capabilities
NEW-MYSQL: MySQL >= 5.6.1 with checksum capabilities

Primary mariadb-lbinlog

Replica / enabled?

Checksums

Status

Checksums and mariadb-binlog

When using the client program, there are similar issues.

A version of mariadb-binlog which understands checksums can read binlog files from either old or new servers, with or without checksums enabled.

An old version of mariadb-binlog can read binlog files produced by a new server version if checksums were disabled when the log was produced. Old versions of mariadb-binlog reading a new binlog file containing checksums will be confused, and output will be garbled, with the added checksums being interpreted as extra garbage at the end of query strings and similar entries. No error will be reported in this case, just wrong output.

A version of mysqlbinlog (the MySQL equivalent to mariadb-binlog and the old MariaDB name for the binary) from MySQL >= 5.6.1 will have similar problems as a replica until this is fixed in MySQL. When reading a binlog file with checksums produced by MariaDB >= 5.3.0 but < 5.6.1, it will not realise that checksums are included, and will produce garbled output just like an old version ofmysqlbinlog. The MariaDB version of mariadb-binlog can read binlog files produced by either MySQL or MariaDB just fine.

Binlog Event Checksums

Configure checksums to detect data corruption in binary logs. Learn how to enable verification to ensure the integrity of replication events during transmission and storage.

MariaDB includes a feature to include a checksum in events.

Checksums are enabled with the , which is set to CRC32 by default.

The variable can be changed dynamically without restarting the server. Setting the variable in any way (even to the existing value) forces a rotation of the

Changing a Replica to Become the Primary

Follow the procedure to promote a replica to a primary role. This guide details the steps for planned failovers or topology reorganization with minimal downtime.

This article describes how to change a replica to become a primary and optionally to set the old primary as a replica for the new primary.

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

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

Note that in the examples below, [connection_name] is used as the . If you are not using named connections you can ignore this.

Stopping the Original Master.

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

If you are using you can just stop the server with the command as the replicas should be automatically up to date.

If you are using , then you to handle the whole process of taking down the primary and replacing it with one of the replicas.

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

Manually Take Down the Primary

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

Note that you should not disconnect this session as otherwise the read lock will disappear and you have to start from the beginning.

Then you should check the current position of the primary:

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

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

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

When replica is up to date, you can then take the PRIMARY down. This should be on the same connection where you executed .

Preparing the Replica to be a Primary

Stop all old connections to the old primary(s) and reset read only mode, if you had it enabled. You also want to save the values of and gtid_binlog_pos, as you may need these to setup new replicas.

Reconnect Other Replicas to the New Primary

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

The XXX values for MASTER_LOG_FILE and MASTER_LOG_POS should be the values you got from the SHOW MASTER STATUS command you did when you finished setting up the replica.

Changing the Old Primary to be a Replica

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

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

For the same reason it's also good to execute the following commands on the old primary (same as for other replicas, but with some extra security). The read_only option below is there to ensure that old applications doesn't by accident try to update the old primary by mistake. It only affects normal connections to the replica, not changes from the new primary.

Moving Applications to Use New Primary

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

Replication When the Primary and Replica Have Different Table Definitions

Understand the rules and limitations when replicating between tables with differing structures. Learn how attribute promotion and column handling work in row-based replication.

While replication is usually meant to take place between primaries and replicas with the same table definitions and this is recommended, in certain cases replication can still take place even if the definitions are identical.

Tables on the replica and the primary do not need to have the same definition in order for to take place. There can be differing numbers of columns, or differing data definitions and, in certain cases, replication can still proceed.

Restricting Speed of Reading Binlog from Primary by a Replica

Configure throughput limits for replication traffic. Learn to throttle the binlog download speed to prevent replication from consuming all available network bandwidth.

When a replica starts after being stopped for some time, or a new replica starts that was created from a backup from some time back, a lot of old binlog events may need to be downloaded from the primary. If this happens from many replicas simultaneously, it can put a lot of load on the primary.

The read_binlog_speed_limit option can be used to reduce such load, by limiting the speed at which events are downloaded. The limit is given as maximum kilobytes per second to download on one replica connection.

With this option set, the replication I/O thread will limit the rate of download. Since the I/O thread is often much faster to download events than the SQL thread is at applying them, an appropriate value forread_binlog_speed_limit may reduce load spikes on the primary without much limit in the speed of the replica.

The option read_binlog_speed_limit is available starting from .

Selectively Skipping Replication of Binlog Events

Learn techniques to bypass specific replication events. This guide explains how to ignore individual transactions or errors to restore replication flow after a stoppage.

Normally, all changes that are logged as events in the binary log are also replicated to all replicas (though still subject to filtering by replicate-do-db, replicate-ignore-db, and similar options). However, sometimes it may be desirable to have certain events be logged into the binlog, but not be replicated to all or a subset of replicas, where the distinction between events that should be replicated or not is under the control of the application making the changes.

This could be useful if an application does some replication external to the server outside of the built-in replication, or if it has some data that should not be replicated for whatever reason.

This is possible with the following .

Primary Session Variable: skip_replication

When the variable is set to true, changes are logged into the with the flag @@skip_replication set. Such events will not be replicated by replicas that run with--replicate-events-marked-for-skip set different from its default of REPLICATE.

Variable Name

skip_replication

The skip_replication option only has effect if is enabled and is true.

Attempting to change @@skip_replication in the middle of a transaction will fail; this is to avoid getting half of a transaction replicated while the other half is not replicated. Be sure to end any current transaction withCOMMIT/ROLLBACK before changing the variable.

Replica Option: --replicate-events-marked-for-skip

The option tells the replica whether to replicate events that are marked with the @@skip_replication flag. Default is REPLICATE, to ensure that all changes are replicated to the replica. If set to FILTER_ON_SLAVE, events so marked will be skipped on the replica and not replicated. If set toFILTER_ON_MASTER, the filtering will be done on the primary, saving on network bandwidth as the events will not be received by the replica at all.

Variable Name

replicate_events_marked_for_skip

Note: replicate_events_marked_for_skip is a dynamic variable (it can be changed without restarting the server), however the replica threads must be stopped when it is changed, otherwise an error will be thrown.

When events are filtered due to @@skip_replication, the filtering happens on the primary side; in other words, the event is never sent to the replica. If many events are filtered like this, a replica can sit a long time without receiving any events from the primary. This is not a problem in itself, but must be kept in mind when inquiring on the replica about events that are filtered. For example START SLAVE UNTIL <some position> will stop when the first event that is not filtered is encountered at the given position or beyond. If the event at the given position is filtered, then the replica thread will only stop when the next non-filtered event is encountered. In effect, if an event is filtered, to the replica it appears that it was never written to the binlog on the primary.

Note that when events are filtered for a replica, the data in the database will be different on the replica and on the primary. It is the responsibility of the application to replicate the data outside of the built-in replication or otherwise ensure consistency of operation. If this is not done, it is possible for replication to encounter, for example, contraint violations or other problems which will cause replication to stop and require manual intervention to fix.

The session variable @@skip_replication can be changed without requiring special privileges. This makes it possible for normal applications to control it without requiring SUPER privileges. But it must be kept in mind when using replicas with --replicate-events-marked-for-skip set different from REPLICATE, as it allows any connection to do changes that are not replicated.

skip_replication and sql_log_bin

and @@skip_replication are somewhat related, as they can both be used to prevent a change on the primary from being replicated to the replica. The difference is that with @@skip_replication, changes are still written into the binlog, and replication of the events is only skipped on replicas that explicitly are configured to do so, with--replicate-events-marked-for-skip different fromREPLICATE. With @@sql_log_bin, events are not logged into the binlog, and so are not replicated by any replica.

skip_replication and the Binlog

When events in the binlog are marked with the @@skip_replication flag, the flag will be preserved if the events are dumped by the program and re-applied against a server with the program. Similarly, the statement will preserve the flag from the event being replayed. And a replica which runs with--log-slave-updates and does not filter events (--replicate-events-marked-for-skip=REPLICATE) will also preserve the flag in the events logged into the binlog on the replica.

LOAD DATA FROM MASTER (removed)

Syntax

Description

This feature has been removed from recent versions of MariaDB.

Since the current implementation of LOAD DATA FROM MASTER

LOAD TABLE FROM MASTER (removed)

Syntax

Description

This feature has been removed from recent versions of MariaDB.

Since the current implementation of and LOAD TABLE FROM MASTER

MariaDB 5.2 Replication Feature Preview

Note: This page is obsolete. The information is old, outdated, or otherwise currently incorrect. We are keeping the page for historical reasons only. Do not rely on the information in this article.

This page describes a "feature preview release" which previewed some replication-related features which are included in . If you would like to try out the features mentioned here, it is recommended that you use () instead of the actual release described below. Likewise, the code is available in the .

About this release

There has been quite a lot of interest in these features, and providing this feature preview release allows the developers to get more and earlier feedback, as well as allowing more users an early opportunity to evaluate the new features.

XtraDB option --innodb-release-locks-early

The --innodb-release-locks-early feature (MWL#163) was included in the 5.2 replication preview. However, it was omitted from due to the bug lp:798213.

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

Replication Filters

Learn how to filter specific databases or tables from being replicated. This guide covers configuration options to replicate only the data you need on specific replicas.

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 to restrict local changes to specific databases from getting written to the , which also determines whether any replicas replicate those changes.

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 , they are only available as options; from they are also available as system variables.

`binlog_do_db`

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

This option will not work with cross-database updates with . See the section for more information.

This option cannot be set dynamically.

When setting it on the command-line or in a server in an , 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:

This will tell the primary to do the following:

Write statements and transactions affecting the database named db1 into the .
Write statements and transactions affecting the database named db2 into the .
Don't write statements and transactions affecting any other databases into the .

`binlog_ignore_db`

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

This option will not work with cross-database updates with . See the section for more information.

This option cannot be set dynamically.

This will tell the primary to do the following:

Don't write statements and transactions affecting the database named db1 into the .
Don't write statements and transactions affecting the database named db2 into the .
Write statements and transactions affecting any other databases into the .

The option is effectively ignored if the 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 to filter events replicated in the .

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 option (and, from , system variable), allows you to configure a to rewrite database names. It uses the format primary_database->replica_database. If a replica encounters a event in which the default database (i.e. the one selected by the 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 . See the section for more information.

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

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 .

Until , this option could not be set dynamically.

This will tell the replica to do the following:

If a event is encountered in which the default database was db1, then apply the event in db3 instead.
If a event is encountered in which the default database was db2, then apply the event in db4 instead.

See for how to configure this system variable with .

`replicate_do_db`

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

This system variable will not work with cross-database updates with or when using 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 section for more information.

When setting it dynamically with , 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 in an when the server is .

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

When setting it on the command-line or in a server in an , 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:

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 for how to configure this system variable with .

`replicate_ignore_db`

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

When setting it dynamically with , 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 in an when the server is .

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

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 system variable is effectively ignored if the system variable is set, so those two system variables should not be set together.

See for how to configure this system variable with .

`replicate_do_table`

The system variable allows you to configure a 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 . See the section for more information.

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

When setting it dynamically with , 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 in an when the server is .

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

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 for how to configure this system variable with .

`replicate_ignore_table`

The system variable allows you to configure a 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 . See the section for more information.

When setting it dynamically with , 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 in an when the server is .

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

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 system variable is effectively ignored if either the system variable or the system variable is set, so the system variable should not be used with those two system variables.

See for how to configure this system variable with .

`replicate_wild_do_table`

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

The wildcard pattern uses the same semantics as the 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 \

This system variable will work with cross-database updates with . See the section for more information.

The system variable does filter databases, tables, and .

The system variable does not filter , , and . The 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 , and .

When setting it dynamically with , 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 in an when the server is .

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

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 for how to configure this system variable with .

`replicate_wild_ignore_table`

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

The wildcard pattern uses the same semantics as the 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 \

This system variable will work with cross-database updates with . See the section for more information.

The system variable does filter databases, tables, and .

The system variable does not filter , , and . The system variable will need to be used to filter those.

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

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

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 system variable is effectively ignored if either the system variable or the system variable is set, so the system variable should not be used with those two system variables.

See for how to configure this system variable with .

How you configure replication filters with depends on whether you are configuring them dynamically or whether you are configuring them in a server in an .

Setting Replication Filter Options Dynamically with Multi-Source Replication

The usage of dynamic replication filters changes somewhat when 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:

Changing the Default Connection

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

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

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

CHANGE MASTER Options

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

`IGNORE_SERVER_IDS`

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

`DO_DOMAIN_IDS`

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

`IGNORE_DOMAIN_IDS`

The option for CHANGE MASTER can be used to configure a to ignore events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , 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 .

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 statement). This applies to the following replication filters:

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 statement). This applies to the following replication filters:

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

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:

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 in statement-based format, even when the system variable is set to ROW. This means that the notes mentioned in always apply to DDL.

Replication Filters and Galera Cluster

When using Galera cluster, replication filters should be used with caution. See for more details.

Parallel Replication

Boost MariaDB Server replication performance with parallel replication. This section explains how to configure replicas to apply events concurrently, reducing lag and improving throughput.

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 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 .
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 in your file as an argument to mysql. Parallel replication can in addition be disabled on a per-multi-source connection by setting 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 includes ). 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 event cannot 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 .

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

Any transactional DML (INSERT/UPDATE/DELETE) is allowed to run in parallel, up to the limit of . This may cause conflicts on the replica, e.g. 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 . More such heuristics may be added in later MariaDB versions. There is a further 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 of . For example:

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 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 system variable to aggressive on the replica.

Conservative Mode of In-Order Parallel Replication

Conservative mode of in-order parallel replication uses the on the primary to discover potential for parallel apply of events on the replica. If two transactions commit together in a 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 , but it can also be configured by setting the 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 that shows how GTID events are marked with commit id. The GTID 0-1-47 has no commit id and cannot 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:

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 on the primary. This can be tuned using the and 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 , 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 (), and more so if replica is configured to be crash-safe ( and ), as this makes possible on the replica.

Minimal Mode of In-Order Parallel Replication

Minimal mode of in-order parallel replication _only_allows 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 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:

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 setting gtid_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 (or none).

Checking Worker Thread Status in SHOW PROCESSLIST

The worker threads will be listed as "system user" in . 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: .

Configuring the Maximum Size of the Parallel Replica Queue

The 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 > 0).

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

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

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

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

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

Usually, the system variable should be set large enough that the is able to read far enough ahead in the 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 system variable could be set fairly high (e.g. a few hundred kilobytes) to not limit throughput. 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 setting 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 of ) 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 .

Global Transaction ID

Understand how Global Transaction IDs (GTIDs) track replication events. Learn to use GTIDs for simplified failover, easier topology changes, and crash-safe replication.

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 for more information):

On a master server, all updates to the database (DML and DDL) are written into the 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.

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 (i.e. ROW, STATEMENT, andMIXED formats are all supported).

Benefits

Using global transaction ID provides two main benefits:

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.

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

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

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

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 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 . 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 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 instead of , 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 . 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 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 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:

The replica server internally uses the 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 (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 as the default storage engine, for example), use :

ALTER TABLE mysql.gtid_slave_pos ENGINE = InnoDB

The 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 , the server variable can preferably be set to make the server handle this automatically. See the description of the for details.

Using `current_pos` vs. `slave_pos`

When setting the 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 system variable, which is a union of and . Using the value slave_pos causes the replica to instead set its position based on the 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 statement or otherwise write to a table while the are stopped, then new local GTIDs may be generated in , which will affect the replica's value of . This may cause errors when the are restarted, since the local GTIDs will be absent from the primary.

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

Using GTIDs with Parallel Replication

If is in use, then events that were logged with GTIDs with different values can be applied in parallel in an manner.

Using GTIDs with MariaDB Galera Cluster

Starting with , MariaDB Galera Cluster has limited support for GTIDs. See 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:

Setup the new server and load it with the initial data.
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 . 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:

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 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 on the server that was backed up:

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

This method is particularly useful when setting up a new replica from a backup of the primary. Remember to ensure that the value of 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 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 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 table or any other table.

Setting up a New Replica with mariadb-backup

A new replica can easily be set up with , which is a fork of . See for more information.

Setting up a New Replica with mariadb-dump

A new replica can also be set up with .

automatically includes the GTID position as a comment in the backup file if either the or option is used. It also automatically includes the commands to set and execute in the backup file if the option is used with either the or 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 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;

(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):

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 ). 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 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, . 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:

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 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 to function correctly, and setting 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

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 . To get rid of these, you can stop the replica and execute on the replica:

Additional Syntax For Global Transaction ID

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 and options are not updated per received event in 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

START SLAVE UNTIL master_gtid_pos=xxx

When starting replication with , 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:

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

SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS

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:

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

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 function takes as input an old-style 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 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 for details.

Binlog Indexing

MariaDB starting with

Prior to , when a replica connects, MariaDB needs to scan files from the beginning in order to find the place to start replicating. If replica reconnects are frequent, this can be slow. 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:

- enable/disable the feature
- adjust the size of the pages
- adjust the sparseness of the index

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

- incremented for each successful lookup in a GTID index.
- 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

Enables/disables .

Adjusts the size of the pages

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 for each replication domain. This system variable's value is automatically updated whenever a 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 .

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

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

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

The GTID position defined by gtid_slave_pos can be used as a replica's starting replication position by setting when the replica is configured with the statement. As an alternative, the 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 contains later GTIDs for certain replication domains, then will contain the GTIDs from 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 , then the server will give the user a warning.

This can help protect the user when the replica is configured to use 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 with .

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

Command line: None
Scope: Global
Dynamic: Yes
Data Type: string

`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 , or after ), 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 , which will also delete all binary logs. However, note that does not also reset . Since is the union of and gtid_binlog_pos, that means that new GTIDs added to gtid_binlog_pos can lag behind those in if contains GTIDs in the same domain with higher sequence numbers. If you want to reset for a specific GTID domain in cases like this, then you will also have to change in addition to executing . See for notes on how to change its value.

Command line: None
Scope: Global
Dynamic: Read-only
Data Type: string

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

Command line: None
Scope: Global
Dynamic: Yes
Data Type: string

`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 and system variables. It gets GTIDs of transactions executed locally from the value of the system variable. It gets GTIDs of replicated transactions from the value of the system variable.

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

GTIDs from in which the of the GTID is not equal to the server's own are effectively ignored. If contains a GTID for a given replication domain, but the of the GTID is not equal to the server's own , and 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 when the replica is configured with the statement. As an alternative, the 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 and , since gtid_current_pos is formed from the values of those variables.

Command line: None
Scope: Global
Dynamic: Read-only
Data Type: string

`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:

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).
Similarly, an attempt to manually binlog a GTID with a lower sequence number (by setting @@SESSION.gtid_seq_no) is rejected with an error.
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).

Command line: --gtid-strict-mode[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean

`gtid_domain_id`

Description: This variable is used to decide which replication domain new GTIDs are logged in for a primary server. See for details. This variable can also be set on the session level by a user with the SUPER privilege. This is used by to preserve the domain ID of GTID events.
Command line: --gtid-domain-id=#
Scope: Global, Session
Dynamic: Yes

`last_gtid`

Description: Holds the GTID that was assigned to the last transaction, or statement that was logged to the . 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 and , is typically used by to set up the gtid value of the transaction being decoded into the output.
Command line: None
Scope: Session
Dynamic: Yes

`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 . See also
Command line: --gtid-ignore-duplicates=#

`gtid_pos_auto_engines`

This variable is used to enable multiple versions of the 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 , , or ).

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.

Command line: --gtid-pos-auto-engines=value
Scope: Global
Dynamic: Yes
Data Type: string (comma-separated list of engine names)

`gtid_cleanup_batch_size`

Description: Normally does not need tuning. How many old rows must accumulate in the before a background job will be run to delete them. Can be increased to reduce number of commits if using many different engines with , or to reduce CPU overhead if using a huge number of different . Can be decreased to reduce number of old rows in the table.
Command line: --gtid-cleanup-batch-size=#
Scope: Global

Replication and Binary Log System Variables

Browse the reference for system variables that control replication behavior and binary logging. Use these settings to tune performance and behavior on primaries and replicas.

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

`auto_increment_increment`

Description: The increment for all values on the server, by default 1. Intended for use in primary-to-primary .
Command line: --auto-increment-increment[=#]
Scope: Global, Session

`auto_increment_offset`

Description: The offset for all values on the server, by default 1. Intended for use in primary-to-primary . Should be not be larger than . See .
Command line: --auto-increment-offset[=#]
Scope: Global, Session

`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.
Command line: --binlog-alter-two-phase[={0|1}]
Scope: Global, Session
Dynamic: Yes

`binlog_annotate_row_events`

Description: This option tells the primary to write to the binary log.
Command line: --binlog-annotate-row-events[={0|1}]
Scope: Global, Session
Dynamic: Yes

`binlog_cache_size`

Description: If the 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, , sets the upper limit for the statement cache. The and will indicate whether this variable needs to be increased (you want a low ratio of binlog_cache_disk_use to binlog_cache_use).
Command line: --binlog-cache-size=#
Scope: Global

`binlog_checksum`

Description: Specifies the type of BINLOG_CHECKSUM_ALG for log events in the .
Command line:
- --binlog-checksum=name
- --binlog-checksum=[0|1]

`binlog_commit_wait_count`

Description: Configures the behavior of , which can help increase transaction throughput and is used to enable . With , the server can delay flushing a committed transaction into 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 . 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 is enabled, but too high a value will decrease the transaction throughput. By monitoring the status variable (>=) it is possible to see how often this is occurring.
Starting with and : If the server detects that one of the committing transactions T1 holds an 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 , provided the value of is sufficiently high.

`binlog_commit_wait_usec`

Description: Configures the behavior of , which can help increase transaction throughput and is used to enable . With , the server can delay flushing a committed transaction into until the transaction has waited the configured number of microseconds. By monitoring the status variable (>=) 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 , the wait will be terminated, though. Thus, this setting only takes effect if binlog_commit_wait_count is non-zero.
Command line: --binlog-commit-wait-usec#

`binlog_direct_non_transactional_updates`

Description: 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 . 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.
Command line: --binlog-direct-non-transactional-updates[=value]
Scope: Global, Session

`binlog_do_db`

Description: This option allows you to configure a to write statements and transactions affecting databases that match a specified name into its . Since the filtered statements or transactions will not be present in the , its replicas will not be able to replicate them.
- This option will not work with cross-database updates with . See the section for more information.
- Until , only available as an option, not a system variable. This option can not be set dynamically.

`binlog_expire_logs_seconds`

Description: If non-zero, binary logs will be purged after binlog_expire_logs_seconds seconds. Possible purges happen at startup and at binary log rotation. From , binlog_expire_logs_seconds and are forms of aliases, such that changes to one automatically reflect in the other.
Command line: --binlog-expire-logs-seconds=#
Scope: Global

`binlog_file_cache_size`

Description: Size of in-memory cache that is allocated when reading and files.
Command line: --binlog-file-cache-size=#
Scope: Global, Session
Dynamic: Yes

`binlog_format`

Description: Determines whether is row-based, statement-based or mixed. Statement-based was the default until . Be careful of changing the binary log format when a replication environment is already running. See . Starting from a replica will apply any events it gets from the primary, regardless of the binary log format. binlog_format only applies to normal (not replicated) updates.
Command line: --binlog-format=format
Scope: Global, Session

`binlog_gtid_index`

Description: Enable the creation of a GTID index for every binlog file, and the use of such index for speeding up GTID lookup in the binlog. See .
Command line: --binlog-gtid-index{=0|1}
Scope: Global
Dynamic: Yes

`binlog_gtid_index_page_size`

Description: Page size to use for the binlog GTID index. See .
Command line: --binlog-gtid-index-page-size=#
Scope: Global
Dynamic: Yes

`binlog_gtid_index_span_min`

Description: Control sparseness of the binlog GTID index. If set to N, at most one index record will be added for every N byte of binlog file written, to reduce the size of the index. Normally does not need tuning. See .
Command line: --binlog-gtid-index-span-min=#
Scope: Global
Dynamic: Yes

`binlog_ignore_db`

Description: This option allows you to configure a to not write statements and transactions affecting databases that match a specified name into its . Since the filtered statements or transactions will not be present in the , its replicas will not be able to replicate them.
- This option will not work with cross-database updates with . See the section for more information.
- Until , only available as an option, not a system variable. This option cannot be set dynamically.

`binlog_large_commit_threshold`

Description: Increases transaction concurrency for large transactions (i.e. those with sizes larger than this value) by using the large transaction's cache file as a new binary log and rotating the active binary log to the large transaction's cache file at commit time. This avoids the default commit logic that copies the transaction cache data to the end of the active binary log file while holding a lock that prevents other transactions from binlogging.
Command line: --binlog-large-commit-threshold=val
Scope: Global

`binlog_legacy_event_pos`

Description: Fill in the end_log_pos field of all events in the binlog, even when doing so costs performance. Can be used in case some old application needs it for backwards compatibility. Setting this option can hurt binlog scalability.
Command line: --binlog-legacy-event-pos{=0|1}
Scope: Global
Dynamic: Yes

`binlog_optimize_thread_scheduling`

Description: Run fast part of group commit in a single thread, to optimize kernel thread scheduling. On by default. Disable to run each transaction in group commit in its own thread, which can be slower at very high concurrency. This option is mostly for testing one algorithm versus another, and it should not normally be necessary to change it. Deprecated in , as the option was initially added to provide a safe alternative for the newly added binlog group commit logic, such that when 0, it would disable a leader thread from performing the binlog write for all transactions that are a part of the group commit. Problems related to the binlog group commit optimization are expected to be addressed by now, so the option has been deprecated and will be removed in future.
Command line: --binlog-optimize-thread-scheduling or --skip-binlog-optimize-thread-scheduling

`binlog_row_event_max_size`

Description: The maximum size of a row-based event in bytes. Rows will be grouped into events smaller than this size if possible. The value has to be a multiple of 256. Until , only available as an option, not a system variable.
Command line: --binlog-row-event-max-size=val
Scope: Global, Session
Dynamic: Yes

`binlog_row_image`

Description: Controls the logging format in . In row-based replication (the variable has no effect with ), each row change event contains an image for matching against when choosing the row to be updated, and another image containing the changes. Before the introduction of this variable, all columns were logged for both of these images. In certain circumstances, this is not necessary, and memory, disk and network resources can be saved by partial logging. Note that to safely change this setting from the default, the table being replicated to must contain identical primary key definitions, and columns must be present, in the same order, and use the same data types as the original table. If these conditions are not met, matches may not be correctly determined and updates and deletes may diverge on the replica, with no warnings or errors returned.
- FULL: All columns in the before and after image are logged. This is the default, and the only behavior in earlier versions.

`binlog_row_metadata`

Description: Controls the format used for binlog metadata logging.
- NO_LOG: No metadata is logged (default).
- MINIMAL: Only metadata required by a replica is logged.

`binlog_space_limit`

Description: Alias for .
Introduced:

`binlog_stmt_cache_size`

Description: If the is active, this variable determines the size in bytes of the cache holding a record of binary log changes outside of a transaction. The variable , determines the cache size for binary log statements inside a transaction. The and will indicate whether this variable needs to be increased (you want a low ratio of binlog_stmt_cache_disk_use to binlog_stmt_cache_use).
Command line: --binlog-stmt-cache-size=#
Scope: Global

`create_tmp_table_binlog_formats`

Description: The under which the primary will log CREATE TEMPORARY statments to the . If CREATE TEMPORARY is not logged, all usage of the temporary table will be logged in ROW format. Allowed values are STATEMENT or MIXED,STATEMENT.
Command line: --create-tmp-table-binlog-formats=#
Scope: Global, Session
Dynamic: Yes

`default_master_connection`

Description: In , specifies which connection will be used for commands and variables if you don't specify a connection.
Command line: None
Scope: Session
Dynamic: Yes

`encrypt_binlog`

Description: Encrypt (including ). See and .
Command line: --encrypt-binlog[={0|1}]
Scope: Global
Dynamic: No

`expire_logs_days`

Description: Number of days after which the can be automatically removed. By default, 0, or no automatic removal. When using , should always be set higher than the maximum lag by any replica. Removals take place when the server starts up, when the binary log is flushed, when the next binary log is created after the previous one reaches the maximum size, or when running . Units are whole days (integer) until , or 1/1000000 precision (double) from . Starting from , expire_logs_days and are forms of aliases, such that changes to one automatically reflect in the other. Some container configs explicitly set expire_logs_days to 10, rather than leave it as the default, zero.
Command line: --expire-logs-days=#

`init_slave`

Description: Similar to , but the string contains one or more SQL statements, separated by semicolons, that will be executed by a replica server each time the SQL thread starts. These statements are only executed after the acknowledgement is sent to the replica and completes.
Command line: --init-slave=name
Scope: Global
Dynamic: Yes

`log_bin`

Description: Whether is enabled or not. If the --log-bin is used, log_bin will be set to ON, otherwise it will be OFF. If no name option is given for --log-bin, datadir/'log-basename'-bin or 'datadir'/mysql-bin will be used (the latter if is not specified). We strongly recommend you use either --log-basename or specify a filename to ensure that doesn't stop if the real hostname of the computer changes. The name option can optionally include an absolute path. If no path is specified, the log will be written to the . The name can optionally include the file extension; it will be stripped and only the file basename will be used.

`log_bin_basename`

Description: The full path of the binary log file names, excluding the extension. Its value is derived from the rules specified in log_bin system variable. This is a read-only variable only, there is no corresponding configuration file setting or command line option by the same name, use log_bin to set the basename path instead.
Command line: No commandline option
Scope: Global

`log_bin_compress`

Description: Whether or not the binary log can be compressed. 0 (the default) means no compression. See .
Command line: --log-bin-compress
Scope: Global
Dynamic: Yes

`log_bin_compress_min_len`

Description: Minimum length of sql statement (in statement mode) or record (in row mode) that can be compressed. See .
Command line: --log-bin-compress-min-len
Scope: Global
Dynamic: Yes

`log_bin_index`

Description: File that holds the names for last binlog files. If is also set, log_bin_index should be placed after in the config files. Later settings override earlier settings, so log-basename will override any earlier log file name settings.
Command line: --log-bin-index=name
Scope: Global

`log_bin_trust_function_creators`

Description: Functions and triggers can be dangerous when used with . Certain types of functions and triggers may have unintended consequences when the statements are applied on a replica. For that reason, there are some restrictions on the creation of functions and triggers when the is enabled by default, such as:
- When log_bin_trust_function_creators is OFF and is ON, and statements will trigger an error if the function is defined with any of the NOT DETERMINISTIC, CONTAINS SQL

`log_slow_slave_statements`

Description: Log slow statements executed by replica thread to the if it is open. Before , this was only available as a mariadbd option, not a server variable.
Command line: --log-slow-slave-statements
Scope: Global
Dynamic: Yes

`log_slave_updates`

Description: If set to 0, the default, updates on a replica received from a primary during are not logged in the replica's binary log. If set to 1, they are. The replica's binary log needs to be enabled for this to have an effect. Set to 1 if you want to daisy-chain the replicas.
Command line: --log-slave-updates
Scope: Global

`master_info_file`

Description: The location and name of the file that remembers the master and where the I/O replication thread is in the master's binlogs. Defaults to master.info.
Command line: --master-info-file=val
Scope: Global
Dynamic: No

`master_verify_checksum`

Description: Verify when reading events from the binlog on the primary.
Command line: --master-verify-checksum=[0|1]
Scope: Global
Access Type: Can be changed dynamically

`max_binlog_cache_size`

Description: Restricts the size in bytes used to cache a multi-transactional query. If more bytes are required, a Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage error is generated. If the value is changed, current sessions are unaffected, only sessions started subsequently. See and .
Command line: --max-binlog-cache-size=#
Scope: Global

`max_binlog_size`

Description: If the exceeds this size in bytes after a write, the server rotates it by closing it and opening a new binary log. Single transactions will always be stored in the same binary log, so the server will wait for open transactions to complete before rotating. This figure also applies to the size of if is set to zero.
Command line: --max-binlog-size=#
Scope: Global

`max_binlog_stmt_cache_size`

Description: Restricts the size used to cache non-transactional statements. See and .
Command line: --max-binlog-stmt-cache-size=#
Scope: Global
Dynamic: Yes

`max_binlog_total_size`

Description: Maximum space in bytes to use for all . Extra logs are deleted on server start, log rotation, FLUSH LOGS or when writing to binlog. Default is 0, which means no size restrictions. See also .
Command line: --max-binlog-size=#
Scope: Global
Dynamic: Yes

`max_relay_log_size`

Description: Replica will rotate its if it exceeds this size after a write. If set to 0, the setting is used instead. Previously global only, since the implementation of , it can be set per session as well.
Command line: --max-relay-log-size=#
Scope: Global, Session
Dynamic: Yes

`read_binlog_speed_limit`

Description: Used to restrict the speed at which a can read the binlog from the primary. This can be used to reduce the load on a primary if many replicas need to download large amounts of old binlog files at the same time. The network traffic will be restricted to the specified number of kilobytes per second.
Command line: --read-binlog-speed-limit=#
Scope: Global
Dynamic: Yes

`relay_log`

Description: basename. If not set, the basename of the files will be hostname-relay-bin, or derived from . If is also set, relay_log should be placed after in the config files. Later settings override earlier settings, so log-basename will override any earlier log file name settings.
Command line: --relay-log=file_name

`relay_log_basename`

Description: The full path of the relay log file names, excluding the extension. Its value is derived from the variable value.
Command line: No commandline option
Scope: Global
Dynamic: No

`relay_log_index`

Description: Name and location of the index file, the file that keeps a list of the last relay logs. Defaults to hostname-relay-bin.index, or derived from . If is also set, relay_log_index should be placed after in the config files. Later settings override earlier settings, so log-basename will override any earlier log file name settings.
Command line: --relay-log-index=name
Scope: Global

`relay_log_info_file`

Description: Name and location of the file where the RELAY_LOG_FILE and RELAY_LOG_POS options (i.e. the position) for the statement are written. The keeps this position updated as it applies events.
- See for more information.
Command line: --relay-log-info-file=file_name

`relay_log_purge`

Description: If set to 1 (the default), will be purged as soon as they are no longer necessary.
Command line: --relay-log-purge={0|1}
Scope: Global
Dynamic: Yes

`relay_log_recovery`

Description: If set to 1 (0 is default), on startup the replica will drop all that haven't yet been processed, and retrieve relay logs from the primary. Can be useful after the replica has crashed to prevent the processing of corrupt relay logs. relay_log_recovery should always be set together with . Setting relay-log-recovery=1 with relay-log-purge=0 can cause the relay log to be read from files that were not purged, leading to data inconsistencies.
Command line: --relay-log-recovery

`relay_log_space_limit`

Description: Specifies the maximum space to be used for the . The IO thread will stop until the SQL thread has cleared the backlog. By default 0, or no limit.
Command line: --relay-log-space-limit=#
Scope: Global

`replicate_annotate_row_events`

Description: Tells the replica to reproduce received from the primary in its own binary log. This option is sensible only when used in tandem with the option.
Command line: --replicate-annotate-row-events
Scope: Global
Dynamic: No

`replicate_do_db`

Description: This system variable allows you to configure a to apply statements and transactions affecting databases that match a specified name.
- This system variable will not work with cross-database updates with . See the section for more information.
- When setting it dynamically with , the system variable accepts a comma-separated list of filters.

`replicate_do_table`

Description: This system variable allows you to configure a 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 . See the section for more information.
- When setting it dynamically with , the system variable accepts a comma-separated list of filters.

`replicate_events_marked_for_skip`

Description: Tells the replica whether to events that are marked with the @@skip_replication flag. See for more information.
Command line: --replicate-events-marked-for-skip
Scope: Global

`replicate_ignore_db`

Description: This system variable allows you to configure a to ignore statements and transactions affecting databases that match a specified name.
- This system variable will not work with cross-database updates with . See the section for more information.
- When setting it dynamically with , the system variable accepts a comma-separated list of filters.

`replicate_ignore_table`

Description: This system variable allows you to configure a 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 . See the section for more information.
- When setting it dynamically with , the system variable accepts a comma-separated list of filters.

`replicate_rewrite_db`

Description: This option allows you to configure a to rewrite database names. It uses the format primary_database->replica_database. If a replica encounters a event in which the default database (i.e. the one selected by the 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 . See the section for more information.

`replicate_same_server_id`

Description: In replication, if set to 1, do not skip events having our server id. Default value is 0 (to break infinite loops in circular replication). Can't be set to 1 if is used
Command line: --replicate-same-server-id[={0|1}]
Scope: Global
Dynamic: No

`replicate_wild_do_table`

Description: This system variable allows you to configure a to apply statements and transactions that affect tables that match a specified wildcard pattern. The wildcard pattern uses the same semantics as the operator.
- This system variable will work with cross-database updates with . See the section for more information.
- When setting it dynamically with , the system variable accepts a comma-separated list of filters.

`replicate_wild_ignore_table`

Description: This system variable allows you to configure a to ignore statements and transactions that affect tables that match a specified wildcard pattern. The wildcard pattern uses the same semantics as the operator.
- This system variable will work with cross-database updates with . See the section for more information.
- When setting it dynamically with , the system variable accepts a comma-separated list of filters.

`report_host`

Description: The host name or IP address the replica reports to the primary when it registers. If left unset, the replica will not register itself. Reported by . Note that it is not sufficient for the primary to simply read the IP of the replica from the socket once the replica connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the replica from the primary or other hosts.
Command line: --report-host=host_name
Scope: Global

`report_password`

Description: Replica password reported to the primary when it registers. Reported by if --show-slave-auth-info is set. This password has no connection with user privileges or with the user account password.
Command line: --report-password=password
Scope: Global

`report_port`

Description: The command line option sets the TCP/IP port for connecting to the replica that will be reported to the primary during the replica's registration. Viewing the variable will show this value.
Command line: --report-port=#
Scope: Global
Dynamic: No

`report_user`

Description: Replica's account user name reported to the primary when it registers. Reported by if --show-slave-auth-info is set. This username has no connection with user privileges or with the user account.
Command line: --report-user=name
Scope: Global

`server_id`

Description: This system variable is used with to identify unique primary and replica servers in a topology. This system variable is also used with the to determine which server a specific transaction originated on.
- When is used with standalone MariaDB Server, each server in the replication topology must have a unique server_id value.
- When is used with , see for more information on how to set the server_id values.

`show_slave_auth_info`

Description: Show user and password in (SHOW SLAVE HOSTS) on this primary.
Command line: --show-slave-auth-info[={0|1}]
Scope: Global
Dynamic: No

`skip_parallel_replication`

Description: If set when a transaction is written to the binlog, parallel apply of that transaction will be avoided on a replica where is not aggressive. Can be used to avoid unnecessary rollback and retry for transactions that are likely to cause a conflict if replicated in parallel. See .
Command line: None
Scope: Session

`skip_replication`

Description: Changes are logged into the with the @@skip_replication flag set. Such events will not be by replica that run with --replicate-events-marked-for-skip set different from its default of REPLICATE. See for more information.
Command line: None
Scope: Session

`slave_abort_blocking_timeout`

Description: Maximum time a replica DDL will wait for a blocking SELECT or other user query until that query will be aborted. The argument will be treated as a decimal value with nanosecond precision. The variable is intended to solve a problem where a long-running SELECT on a replica causes DDL to wait for that SELECT to complete, potentially causing massive replica lag.
Command line: --slave-abort-blocking-timeout=num
Scope: Global

`slave_compressed_protocol`

Description: If set to 1 (0 is the default), will use compression for the replica/primary protocol if both primary and replica support this.
Command line: --slave-compressed-protocol
Scope: Global
Dynamic: Yes

`slave_connections_needed_for_purge`

Description: Minimum number of connected replicas required for automatic purge with , or . Change of the value triggers an attempt to purging, though without binlog rotation, with the purged set of files satisfying the above two parameters and the value that is set itself.
Command line: --slave-connections-needed-for-purge=#
Scope: Global

`slave_ddl_exec_mode`

Description: Modes for how of DDL events should be executed. Legal values are STRICT and IDEMPOTENT (default). In IDEMPOTENT mode, the replica will not stop for failed DDL operations that would not cause a difference between the primary and the replica. In particular is treated as and is treated as DROP TABLE IF EXISTS.
Command line: --slave-ddl-exec-mode=name

`slave_domain_parallel_threads`

Description: When set to a non-zero value, each domain in one primary connection can reserve at most that many worker threads at any one time, leaving the rest (up to the value of ) free for other primary connections or replication domains to use in parallel. See for details.
Command line: --slave-domain-parallel-threads=#
Scope: Global

`slave_exec_mode`

Description: Determines the mode used for error checking and conflict resolution. STRICT mode is the default, and catches all errors and conflicts. IDEMPOTENT mode suppresses duplicate key or no key errors, which can be useful in certain replication scenarios, such as when there are multiple primaries, or circular replication.
Scope: Global
Dynamic: Yes
Data Type: enumeration

`slave_load_tmpdir`

Description: Directory where the replica stores temporary files for statements. If not set, the replica will use . Should be set to a disk-based directory that will survive restarts, or else replication may fail.
Command line: --slave-load-tmpdir=path
Scope: Global
Dynamic: No

`slave_max_allowed_packet`

Description: Maximum packet size in bytes for replica SQL and I/O threads. This value overrides for purposes. Set in multiples of 1024 (the minimum) up to 1GB
Command line: --slave-max-allowed-packet=#
Scope: Global
Dynamic: Yes

`slave_max_statement_time`

Description: A query that has taken more than this in seconds to run on the replica will be aborted. The argument will be treated as a decimal value with microsecond precision. A value of 0 (default) means no timeout.
Command line: --slave-max-statement-time=#
Scope: Global
Dynamic: Yes

`slave_net_timeout`

Description: Time in seconds for the replica to wait for more data from the primary before considering the connection broken, after which it will abort the read and attempt to reconnect. The retry interval is determined by the MASTER_CONNECT_RETRY open for the statement, while the maximum number of reconnection attempts is set by the option. The first reconnect attempt takes place immediately.
Command line: --slave-net-timeout=#
Scope: Global

`slave_parallel_max_queued`

Description: When is used, the will read ahead in the relay logs, queueing events in memory while looking for opportunities for executing events in parallel. This system variable sets a limit for how much memory it will use for this.
- The configured value of this system variable is actually allocated for each , so the total allocation is actually equivalent to the following:
  - *

`slave_parallel_mode`

Description: Controls what transactions are applied in parallel when using .
- optimistic: tries to apply most transactional DML in parallel and handles any conflicts with rollback and retry. See .
- conservative: limits parallelism in an effort to avoid any conflicts. See .

`slave_parallel_threads`

Description: This system variable is used to configure .
- If this system variable is set to a value greater than 0, then its value will determine how many replica will be created to apply events in parallel.
- If this system variable is set to 0 (which is the default value), then no replica will be created. Instead, when replication is enabled, events are applied by the replica's .

`slave_parallel_workers`

Description: Alias for .
Command line: --slave-parallel-workers=#

`slave_run_triggers_for_rbr`

Description: See for a description and use-case for this setting.
Command line: --slave-run-triggers-for-rbr=value
Scope: Global
Dynamic: Yes

`slave_skip_errors`

Description: When an error occurs on the replica, usually halts. This option permits a list of to ignore, and for which replication will continue. This option should never be needed in normal use, and careless use could lead to replica that are out of sync with primaries. Error codes are in the format of the number from the replica error log. Using all as an option permits the replica the keep replicating no matter what error it encounters, an option you would never normally need in production, and which could rapidly lead to data inconsistencies. A count of these is kept in .
Command line: --slave-skip-errors=[error_code1,error_code2,...|all|ddl_exist_errors]
Scope: Global

`slave_sql_verify_checksum`

Description: Verify when the replica SQL thread reads events from the .
Command line: --slave-sql-verify-checksum=[0|1]
Scope: Global
Access Type: Can be changed dynamically

`slave_transaction_retries`

Description: Number of times a replica retries to execute an SQL thread after it fails due to InnDB deadlock or by exceeding the transaction execution time limit. If after this number of tries the SQL thread has still failed to execute, the replica will stop with an error. See also the system variable.
Command line: --slave-transaction-retries=#
Scope: Global

`slave_transaction_retry_errors`

Description: When an error occurs during a transaction on the replica, usually halts. By default, transactions that caused a deadlock or elapsed lock wait timeout will be retried. One can add other errors to the list of errors that should be retried by adding a comma-separated list of to this variable. This is particularly useful in some setups. Some recommended errors to retry for Spider are 1020, 1158, 1159, 1160, 1161, 1429, 2013, 12701 (these are in the default value in recent versions).
Command line: --slave-transaction_retry-errors=[error_code1,error_code2,...]
Scope: Global

`slave_transaction_retry_interval`

Description: Interval in seconds for the replica SQL thread to retry a failed transaction due to a deadlock, elapsed lock waits timeout or an error listed in . The interval is calculated as max(slave_transaction_retry_interval, min(retry_count, 5)).
Command line: --slave-transaction-retry-interval=#
Scope: Global

`slave_type_conversions`

Description: Determines the type conversion mode on the replica when using , including replications in MariaDB Galera cluster. Multiple options can be set, delimited by commas. If left empty, the default, type conversions are disallowed. The variable is dynamic and a change in its value takes effect immediately. This variable tells the server what to do if the table definition is different between the primary and replica (for example a column is 'int' on the primary and 'bigint' on the replica).
- ALL_NON_LOSSY means that all safe conversions (no data loss) are allowed.
- ALL_LOSSY means that all lossy conversions are allowed (for example 'bigint' to 'int'). This, however, does not imply that safe conversions (non-lossy) are allowed as well. In order to allow all conversions, one needs to allow both lossy as well as non-lossy conversions by setting this variable to ALL_NON_LOSSY,ALL_LOSSY.

`sql_log_bin`

Description: If set to 0 (1 is the default), no logging to the is done for the client. Only clients with the SUPER privilege can update this variable. Does not affect the replication of events in a Galera cluster. Note that sql_log_bin has no effect if is not set.
Scope: Session
Dynamic: Yes
Data Type:

`sql_slave_skip_counter`

Description: Number of events that a replica skips from the primary. If this would cause the replica to begin in the middle of an event group, the replica will instead begin from the beginning of the next event group. See .
Scope: Global
Dynamic: Yes
Data Type: numeric

`sync_binlog`

Description: MariaDB will synchronize its binary log file to disk after this many events. The default is 0, in which case the operating system handles flushing the file to disk. 1 is the safest, but slowest, choice, since the file is flushed after each write. If autocommit is enabled, there is one write per statement, otherwise there's one writes per transaction. If the disk has cache backed by battery, synchronization will be fast, and a more conservative number can be chosen.
Command line: --sync-binlog=#
Scope: Global

`sync_master_info`

Description: A replica will synchronize its master.info file to disk after this many events. If set to 0, the operating system handles flushing the file to disk.
Command line: --sync-master-info=#
Scope: Global
Dynamic: Yes

`sync_relay_log`

Description: The MariaDB server will synchronize its to disk after this many writes to the log. The default until was 0, in which case the operating system handles flushing the file to disk. 1 is the safest, but slowest, choice, since the file is flushed after each write. If autocommit is enabled, there is one write per statement, otherwise there's one write per transaction. If the disk has cache backed by battery, synchronization will be fast and a more conservative number can be chosen.
Command line: --sync-relay-log=#
Scope: Global

`sync_relay_log_info`

Description: A replica will synchronize its relay-log.info file to disk after this many transactions. The default until was 0, in which case the operating system handles flushing the file to disk. 1 is the most secure choice, because at most one event could be lost in the event of a crash, but it's also the slowest.
Command line: --sync-relay-log-info=#
Scope: Global,

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

Replication

Replication Overview

Replication Uses

Common Replication Setups

Standard Replication

Ring Replication

Ring Replication with slaves

Ring Replication with replication through slaves

Star Replication

Multi-Source Replication

Cross-Version Replication Compatibility

See Also

Replication Statements

Read-Only Replicas

Older MariaDB Versions

Delayed Replication

Fields in are associated with delayed replication

clock_difference_between_master_and_slave

Row-based Replication With No Primary Key

Replication and Foreign Keys

Binlog Event Checksum Interoperability

Checksums and mariadb-binlog

See Also

Binlog Event Checksums

Changing a Replica to Become the Primary

Stopping the Original Master.

Manually Take Down the Primary

Preparing the Replica to be a Primary

Reconnect Other Replicas to the New Primary

Changing the Old Primary to be a Replica

Moving Applications to Use New Primary

See Also

Replication When the Primary and Replica Have Different Table Definitions

Restricting Speed of Reading Binlog from Primary by a Replica

Selectively Skipping Replication of Binlog Events

Primary Session Variable: skip_replication

Replica Option: --replicate-events-marked-for-skip

skip_replication and sql_log_bin

skip_replication and the Binlog

See Also

LOAD DATA FROM MASTER (removed)

Syntax

Description

LOAD TABLE FROM MASTER (removed)

Syntax

Description

MariaDB 5.2 Replication Feature Preview

About this release

XtraDB option --innodb-release-locks-early

Replication

Replication Statements

Replication Overview

Replication Uses

Common Replication Setups

Standard Replication

Ring Replication

Ring Replication with slaves

Ring Replication with replication through slaves

Star Replication

Multi-Source Replication

Cross-Version Replication Compatibility

See Also

XtraDB option --innodb-release-locks-early

Row-based Replication With No Primary Key

LOAD TABLE FROM MASTER (removed)

Syntax

Description

Restricting Speed of Reading Binlog from Primary by a Replica

See Also

Replication and Foreign Keys

Binlog Event Checksums

LOAD DATA FROM MASTER (removed)

Syntax

Description

See Also

Changing a Replica to Become the Primary

Stopping the Original Master.

Manually Take Down the Primary

Preparing the Replica to be a Primary

Reconnect Other Replicas to the New Primary

`innodb_snapshot_isolation`