Replication and Binary Log System Variables

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.

Contents

  1. auto_increment_increment
  2. auto_increment_offset
  3. binlog_alter_two_phase
  4. binlog_annotate_row_events
  5. binlog_do_db
  6. binlog_cache_size
  7. binlog_checksum
  8. binlog_commit_wait_count
  9. binlog_commit_wait_usec
  10. binlog_direct_non_transactional_updates
  11. binlog_expire_logs_seconds
  12. binlog_file_cache_size
  13. binlog_format
  14. binlog_gtid_index
  15. binlog_gtid_index_page_size
  16. binlog_gtid_index_span_min
  17. binlog_ignore_db
  18. binlog_optimize_thread_scheduling
  19. binlog_row_event_max_size
  20. binlog_row_image
  21. binlog_row_metadata
  22. binlog_stmt_cache_size
  23. default_master_connection
  24. encrypt_binlog
  25. expire_logs_days
  26. gtid_binlog_pos
  27. gtid_binlog_state
  28. gtid_current_pos
  29. gtid_domain_id
  30. gtid_seq_no
  31. gtid_slave_pos
  32. gtid_strict_mode
  33. gtid_pos_auto_engines
  34. init_slave
  35. last_gtid
  36. log_bin
  37. log_bin_basename
  38. log_bin_compress
  39. log_bin_compress_min_len
  40. log_bin_index
  41. log_bin_trust_function_creators
  42. log_slow_slave_statements
  43. log_slave_updates
  44. master_verify_checksum
  45. max_binlog_cache_size
  46. max_binlog_size
  47. max_binlog_stmt_cache_size
  48. max_binlog_total_size
  49. max_relay_log_size
  50. read_binlog_speed_limit
  51. relay_log
  52. relay_log_basename
  53. relay_log_index
  54. relay_log_info_file
  55. relay_log_purge
  56. relay_log_recovery
  57. relay_log_space_limit
  58. replicate_annotate_row_events
  59. replicate_do_db
  60. replicate_do_table
  61. replicate_events_marked_for_skip
  62. replicate_ignore_db
  63. replicate_ignore_table
  64. replicate_rewrite_db
  65. replicate_wild_do_table
  66. replicate_wild_ignore_table
  67. report_host
  68. report_password
  69. report_port
  70. report_user
  71. server_id
  72. skip_parallel_replication
  73. skip_replication
  74. slave_compressed_protocol
  75. slave_connections_needed_for_purge
  76. slave_ddl_exec_mode
  77. slave_domain_parallel_threads
  78. slave_exec_mode
  79. slave_load_tmpdir
  80. slave_max_allowed_packet
  81. slave_max_statement_time
  82. slave_net_timeout
  83. slave_parallel_max_queued
  84. slave_parallel_mode
  85. slave_parallel_threads
  86. slave_parallel_workers
  87. slave_run_triggers_for_rbr
  88. slave_skip_errors
  89. slave_sql_verify_checksum
  90. slave_transaction_retries
  91. slave_transaction_retry_errors
  92. slave_transaction_retry_interval
  93. slave_type_conversions
  94. sql_log_bin
  95. sql_slave_skip_counter
  96. sync_binlog
  97. sync_master_info
  98. sync_relay_log
  99. sync_relay_log_info

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

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

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

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

auto_increment_increment

  • Description: The increment for all AUTO_INCREMENT values on the server, by default 1. Intended for use in primary-to-primary replication.
  • Commandline: --auto-increment-increment[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1
  • Range: 1 to 65535

auto_increment_offset


binlog_alter_two_phase

  • Description: When set, split ALTER at binary logging into two statements: START ALTER and COMMIT/ROLLBACK ALTER. The ON setting is recommended for long-running ALTER-table so it could start on replica before its actual execution on primary.
  • Commandline: --binlog-alter-two-phase[={0|1}]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 10.8.1

binlog_annotate_row_events

  • Description: This option tells the primary to write annotate_rows_events to the binary log.
  • Commandline: --binlog-annotate-row-events[={0|1}]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value:

binlog_do_db

  • Description: This option allows you to configure a replication primary to write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replicas will not be able to replicate them.
    • This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • Until MariaDB 11.2.0, only available as an option, not a system variable. This option can not be set dynamically.
    • When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.
    • See Replication Filters for more information.
  • Commandline: --binlog-do-db=#
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: NULL
  • Introduced: MariaDB 11.2.0 (as a system variable)

binlog_cache_size

  • Description: If the binary log is active, this variable determines the size in bytes, per-connection, of the cache holding a record of binary log changes during a transaction. A separate variable, binlog_stmt_cache_size, sets the upper limit for the statement cache. The binlog_cache_disk_use and binlog_cache_use server status variables will indicate whether this variable needs to be increased (you want a low ratio of binlog_cache_disk_use to binlog_cache_use).
  • Commandline: --binlog-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 32768
  • Range - 32 bit: 4096 to 4294967295
  • Range - 64 bit: 4096 to 18446744073709547520

binlog_checksum

  • Description: Specifies the type of BINLOG_CHECKSUM_ALG for log events in the binary log.
  • Commandline:
    • --binlog-checksum=name
    • --binlog-checksum=[0|1]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value:
  • Valid Values: NONE (0), CRC32 (1)

binlog_commit_wait_count


binlog_commit_wait_usec


binlog_direct_non_transactional_updates

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

binlog_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 MariaDB 10.6.1, binlog_expire_logs_seconds and expire_logs_days are forms of aliases, such that changes to one automatically reflect in the other.
  • Commandline: --binlog-expire-logs-seconds=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 4294967295
  • Introduced: MariaDB 10.6.1

binlog_file_cache_size

  • Description: Size of in-memory cache that is allocated when reading binary log and relay log files.
  • Commandline: --binlog-file-cache-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16384
  • Range: 8192 to 18446744073709551615
  • Introduced: MariaDB 10.3.3

binlog_format

  • Description: Determines whether replication is row-based, statement-based or mixed. Statement-based was the default until MariaDB 10.2.3. Be careful of changing the binary log format when a replication environment is already running. See Binary Log Formats. Starting from MariaDB 10.0.22 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.
  • Commandline: --binlog-format=format
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value:
  • Valid Values: ROW, STATEMENT or MIXED

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 Binlog indexing.
  • Commandline: --binlog-gtid-index{=0|1}
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON
  • Introduced: MariaDB 11.4

binlog_gtid_index_page_size

  • Description: Page size to use for the binlog GTID index. See Binlog indexing.
  • Commandline: --binlog-gtid-index-page-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4096
  • Range: 64 to 16777216
  • Introduced: MariaDB 11.4

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 bytes of binlog file written, to reduce the size of the index. Normally does not need tuning. See Binlog indexing.
  • Commandline: --binlog-gtid-index-span-min=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 65536
  • Range: 1 to 1073741824
  • Introduced: MariaDB 11.4

binlog_ignore_db

  • Description: This option allows you to configure a replication primary to not write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replicas will not be able to replicate them.
    • This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • Until MariaDB 11.2.0, only available as an option, not a system variable. This option can not be set dynamically.
    • When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.
    • See Replication Filters for more information.
  • Commandline: --binlog-ignore-db=name
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: NULL
  • Introduced: MariaDB 11.2.0

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.
  • Commandline: --binlog-optimize-thread-scheduling or --skip-binlog-optimize-thread-scheduling
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: ON

binlog_row_event_max_size

  • Description: The maximum size of a row-based binary log 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 MariaDB 11.2.0, only available as an option, not a system variable.
  • Commandline: --binlog-row-event-max-size=val
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 8192
  • Range: 256 to 4294967040 (in multiples of 256)
  • Introduced: MariaDB 11.2.0

binlog_row_image

  • Description: Controls the logging format in row-based replication. In row-based replication (the variable has no effect with statement-based replication), 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.
    • NOBLOB: mariadbd avoids logging blob and text columns whenever possible (eg, blob column was not changed or is not part of primary key).
    • MINIMAL: A PK equivalent (PK columns or full row if there is no PK in the table) is logged in the before image, and only changed columns are logged in the after image.
    • FULL_NODUP: All columns are logged in the before image, but only changed columns or all columns of inserted record are logged in the after image. This is essentially the same as FULL, but takes less space. From MariaDB 11.4.
  • Commandline: --binlog-row-image=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: FULL
  • Valid Values:

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.
    • FULL: All metadata is logged.
  • Commandline: --binlog-row-metadata=value
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: NO_LOG
  • Valid Values: NO_LOG, MINIMAL, FULL
  • Introduced: MariaDB 10.5.0

binlog_stmt_cache_size

  • Description: If the binary log 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 binlog_cache_size, determines the cache size for binary log statements inside a transaction. The binlog_stmt_cache_disk_use and binlog_stmt_cache_use server status variables 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).
  • Commandline: --binlog-stmt-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 32768
  • Range - 32 bit: 4096 to 4294967295
  • Range - 64 bit: 4096 to 18446744073709547520

default_master_connection

  • Description: In multi-source replication, specifies which connection will be used for commands and variables if you don't specify a connection.
  • Commandline: None
  • Scope: Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty string)

encrypt_binlog


expire_logs_days

  • Description: Number of days after which the binary log can be automatically removed. By default 0, or no automatic removal. When using replication, 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 PURGE BINARY LOGS. Units are whole days (integer) until MariaDB 10.6.0, or 1/1000000 precision (double) from MariaDB 10.6.1.
    Starting from MariaDB 10.6.1, expire_logs_days and binlog_expire_logs_seconds are forms of aliases, such that changes to one automatically reflect in the other.
  • Commandline: --expire-logs-days=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0.000000 (>= MariaDB 10.6.1), 0 (<= MariaDB 10.6.0)
  • Range: 0 to 99

init_slave

  • Description: Similar to init_connect, 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 START SLAVE completes.
  • Commandline: --init-slave=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Related variables: init_connect

log_bin

  • Description: Whether binary logging is enabled or not. If the --log-bin option 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 --log-basename is not specified). We strongly recommend you use either --log-basename or specify a filename to ensure that replication 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 data directory. The name can optionally include the file extension; it will be stripped and only the file basename will be used.
  • Commandline: --log-bin[=name]
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF
  • Related variables: sql_log_bin

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.
  • Commandline: No commandline option
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: None
  • Dynamic: No

log_bin_compress


log_bin_compress_min_len

  • Description: Minimum length of sql statement (in statement mode) or record (in row mode) that can be compressed. See Compressing Events to Reduce Size of the Binary Log.
  • Commandline: --log-bin-compress-min-len
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 256
  • Range: 10 to 1024

log_bin_index

  • Description: File that holds the names for last binlog files.
  • Commandline: --log-bin-index=name
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: None

log_bin_trust_function_creators

  • Description: Functions and triggers can be dangerous when used with replication. 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 binary log is enabled by default, such as:
    • When log_bin_trust_function_creators is OFF and log_bin is ON, CREATE FUNCTION and ALTER FUNCTION statements will trigger an error if the function is defined with any of the NOT DETERMINISTIC, CONTAINS SQL or MODIFIES SQL DATA characteristics.
    • This means that when log_bin_trust_function_creators is OFF and log_bin is ON, CREATE FUNCTION and ALTER FUNCTION statements will only succeed if the function is defined with any of the DETERMINISTIC, NO SQL, or READS SQL DATA characteristics.
    • When log_bin_trust_function_creators is OFF and log_bin is ON, the SUPER privilege is also required to execute the following statements:
    • Setting log_bin_trust_function_creators to ON removes these requirements around functions characteristics and the SUPER privileges.
    • See Binary Logging of Stored Routines for more information.
  • Commandline: --log-bin-trust-function-creators[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

log_slow_slave_statements

  • Description: Log slow statements executed by replica thread to the slow log if it is open. Before MariaDB 10.1.13, this was only available as a mariadbd option, not a server variable.
  • Commandline: --log-slow-slave-statements
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value:

log_slave_updates

  • Description: If set to 0, the default, updates on a replica received from a primary during replication 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.
  • Commandline: --log-slave-updates
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

master_verify_checksum

  • Description: Verify binlog checksums when reading events from the binlog on the primary.
  • Commandline: --master-verify-checksum=[0|1]
  • Scope: Global
  • Access Type: Can be changed dynamically
  • Data Type: bool
  • Default Value: OFF (0)

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 max_binlog_stmt_cache_size and binlog_cache_size.
  • Commandline: --max-binlog-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 18446744073709547520
  • Range: 4096 to 18446744073709547520

max_binlog_size

  • Description: If the binary log exceeds this size 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 relay logs if max_relay_log_size is set to zero.
  • Commandline: --max-binlog-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1073741824 (1GB)
  • Range: 4096 to 1073741824 (4KB to 1GB)

max_binlog_stmt_cache_size

  • Description: Restricts the size used to cache non-transactional statements. See max_binlog_cache_size and binlog_stmt_cache_size.
  • Commandline: --max-binlog-stmt-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 18446744073709547520 (64 bit), 4294963200 (32 bit)
  • Range: 4096 to 18446744073709547520

max_binlog_total_size

  • Description: Maximum space to use for all binary logs. 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 slave_connections_needed_for_purge.
  • Commandline: --max-binlog-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 18446744073709551615
  • Introduced: MariaDB 11.4.0

max_relay_log_size

  • Description: Replica will rotate its relay log if it exceeds this size after a write. If set to 0, the max_binlog_size setting is used instead. Previously global only, since the implementation of multi-source replication, it can be set per session as well.
  • Commandline: --max-relay-log-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0, or 4096 to 1073741824 (4KB to 1GB)

read_binlog_speed_limit

  • Description: Used to restrict the speed at which a replica 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.
  • Commandline: --read-binlog-speed-limit=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0 (no limit)
  • Range: 0 to 18446744073709551615

relay_log

  • Description: Relay log basename. If not set, the basename of the files will be hostname-relay-bin.
  • Commandline: --relay-log=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: filename
  • Default Value: '' (none)

relay_log_basename

  • Description: The full path of the relay log file names, excluding the extension. Its value is derived from the relay-log variable value.
  • Commandline: No commandline option
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: None
  • Dynamic: No

relay_log_index

  • Description: Name and location of the relay log index file, the file that keeps a list of the last relay logs. Defaults to hostname-relay-bin.index.
  • Commandline: --relay-log-index=name
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: None

relay_log_info_file

  • Description: Name and location of the file where the RELAY_LOG_FILE and RELAY_LOG_POS options (i.e. the relay log position) for the CHANGE MASTER statement are written. The replica's SQL thread keeps this relay log position updated as it applies events.
  • Commandline: --relay-log-info-file=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: relay-log.info

relay_log_purge

  • Description: If set to 1 (the default), relay logs will be purged as soon as they are no longer necessary.
  • Commandline: --relay-log-purge={0|1}
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON
  • Note: In MySQL and in MariaDB before version 10.0.8 this variable was silently changed if you did CHANGE MASTER.

relay_log_recovery

  • Description: If set to 1 (0 is default), on startup the replica will drop all relay logs 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 relay_log_purge. 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.
  • Commandline: --relay-log-recovery
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

relay_log_space_limit

  • Description: Specifies the maximum space to be used for the relay logs. The IO thread will stop until the SQL thread has cleared the backlog. By default 0, or no limit.
  • Commandline: --relay-log-space-limit=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 0
  • Range - 32 bit: 0 to 4294967295
  • Range - 64 bit: 0 to 18446744073709547520

replicate_annotate_row_events

  • Description: Tells the replica to reproduce annotate_rows_events received from the primary in its own binary log. This option is sensible only when used in tandem with the log_slave_updates option.
  • Commandline: --replicate-annotate-row-events
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value:

replicate_do_db

  • Description: This system variable allows you to configure a replica to apply statements and transactions affecting databases that match a specified name.
    • This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.
    • When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
    • See Replication Filters for more information.
  • Commandline: --replicate-do-db=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty)

replicate_do_table

  • Description: This system variable allows you to configure a replica to apply statements and transactions that affect tables that match a specified name. The table name is specified in the format: dbname.tablename.
    • This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.
    • When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
    • See Replication Filters for more information.
  • Commandline: --replicate-do-table=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty)

replicate_events_marked_for_skip

  • Description: Tells the replica whether to replicate events that are marked with the @@skip_replication flag. See Selectively skipping replication of binlog events for more information.
  • Commandline: --replicate-events-marked-for-skip
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: replicate
  • Valid Values: REPLICATE, FILTER_ON_SLAVE, FILTER_ON_MASTER

replicate_ignore_db

  • Description: This system variable allows you to configure a replica to ignore statements and transactions affecting databases that match a specified name.
    • This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.
    • When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
    • See Replication Filters for more information.
  • Commandline: --replicate-ignore-db=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty)

replicate_ignore_table

  • Description: This system variable allows you to configure a replica to ignore statements and transactions that affect tables that match a specified name. The table name is specified in the format: dbname.tablename.
    • This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.
    • When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
    • See Replication Filters for more information.
  • Commandline: --replicate-ignore-table=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty)

replicate_rewrite_db

  • Description: This option allows you to configure a replica to rewrite database names. It uses the format primary_database->replica_database. If a replica encounters a binary log event in which the default database (i.e. the one selected by the USE statement) is primary_database, then the replica will apply the event in replica_database instead.
    • This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • This option only affects statements that involve tables. This option does not affect statements involving the database itself, such as CREATE DATABASE, ALTER DATABASE, and DROP DATABASE.
    • When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.
    • See Replication Filters for more information.
    • Before MariaDB 10.11, replicate_rewrite_db was not available as a system variable, only as a mariadbd option, and could not be set dynamically. From MariaDB 10.11 it is available as a dynamic system variable
  • Commandline: --replicate-rewrite-db=primary_database->replica_database
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty)
  • Introduced: MariaDB 10.11.0

replicate_wild_do_table

  • Description: This system variable allows you to configure a replica to apply statements and transactions that affect tables that match a specified wildcard pattern. The wildcard pattern uses the same semantics as the LIKE operator.
    • This system variable will work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.
    • When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
    • See Replication Filters for more information.
  • Commandline: --replicate-wild-do-table=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty)

replicate_wild_ignore_table

  • Description: This system variable allows you to configure a replica to ignore statements and transactions that affect tables that match a specified wildcard pattern. The wildcard pattern uses the same semantics as the LIKE operator.
    • This system variable will work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
    • When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.
    • When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
    • See Replication Filters for more information.
  • Commandline: --replicate-wild-ignore-table=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: '' (empty)

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 SHOW SLAVE HOSTS. 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.
  • Commandline: --report-host=host_name
  • Scope: Global
  • Dynamic: No
  • Data Type: string

report_password

  • Description: Replica password reported to the primary when it registers. Reported by SHOW SLAVE HOSTS if --show-slave-auth-info is set. This password has no connection with user privileges or with the replication user account password.
  • Commandline: --report-password=password
  • Scope: Global
  • Dynamic: No
  • Data Type: string

report_port

  • Description: The commandline option sets the TCP/IP port for connecting to the replica that will be reported to the replicating primary during the replica's registration. Viewing the variable will show this value.
  • Commandline: --report-port=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 65535

report_user

  • Description: Replica's account user name reported to the primary when it registers. Reported by SHOW SLAVE HOSTS if --show-slave-auth-info is set. This username has no connection with user privileges or with the replication user account.
  • Commandline: --report-user=name
  • Scope: Global
  • Dynamic: No
  • Data Type: string

server_id

  • Description: This system variable is used with MariaDB replication to identify unique primary and replica servers in a topology. This system variable is also used with the binary log to determine which server a specific transaction originated on.
  • Commandline: --server-id =#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1
  • Range: 1 to 4294967295

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 slave_parallel_mode 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 parallel replication.
  • Commandline: None
  • Scope: Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

skip_replication

  • Description: Changes are logged into the binary log with the @@skip_replication flag set. Such events will not be replicated by replica that run with --replicate-events-marked-for-skip set different from its default of REPLICATE. See Selectively skipping replication of binlog events for more information.
  • Commandline: None
  • Scope: Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

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.
  • Commandline: --slave-compressed-protocol
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

slave_connections_needed_for_purge


slave_ddl_exec_mode

  • Description: Modes for how replication 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 CREATE TABLE is treated as CREATE OR REPLACE TABLE and DROP TABLE is treated as DROP TABLE IF EXISTS.
  • Commandline: --slave-ddl-exec-mode=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: IDEMPOTENT
  • Valid Values: IDEMPOTENT, STRICT

slave_domain_parallel_threads

  • Description: When set to a non-zero value, 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 slave_parallel_threads) free for other primary connections or replication domains to use in parallel. See Parallel Replication for details.
  • Commandline: --slave-domain-parallel-threads=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Valid Values: 0 to 16383

slave_exec_mode

  • Description: Determines the mode used for replication 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
  • Default Value: IDEMPOTENT (NDB), STRICT (All)
  • Valid Values: IDEMPOTENT, STRICT

slave_load_tmpdir

  • Description: Directory where the replica stores temporary files for replicating LOAD DATA INFILE statements. If not set, the replica will use tmpdir. Should be set to a disk-based directory that will survive restarts, or else replication may fail.
  • Commandline: --slave-load-tmpdir=path
  • Scope: Global
  • Dynamic: No
  • Data Type: file name
  • Default Value: /tmp

slave_max_allowed_packet

  • Description: Maximum packet size in bytes for replica SQL and I/O threads. This value overrides max_allowed_packet for replication purposes. Set in multiples of 1024 (the minimum) up to 1GB
  • Commandline: --slave-max-allowed-packet=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1073741824
  • Range: 1024 to 1073741824

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.
  • Commandline: --slave-max-statement-time=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0.000000
  • Range: 0 to 31536000
  • Introduced: MariaDB 10.10

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 CHANGE MASTER statement, while the maximum number of reconnection attempts is set by the master-retry-count option. The first reconnect attempt takes place immediately.
  • Commandline: --slave-net-timeout=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
    • 60 (1 minute)
  • Range: 1 to 31536000

slave_parallel_max_queued


slave_parallel_mode

  • Description: Controls what transactions are applied in parallel when using parallel replication.
    • optimistic: tries to apply most transactional DML in parallel, and handles any conflicts with rollback and retry. See optimistic mode.
    • conservative: limits parallelism in an effort to avoid any conflicts. See conservative mode.
    • aggressive: tries to maximize the parallelism, possibly at the cost of increased conflict rate.
    • minimal: only parallelizes the commit steps of transactions.
    • none disables parallel apply completely.
  • Commandline: None
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: optimistic (>= MariaDB 10.5.1), conservative (<= MariaDB 10.5.0)
  • Valid Values: conservative, optimistic, none, aggressive and minimal

slave_parallel_threads


slave_parallel_workers


slave_run_triggers_for_rbr


slave_skip_errors

  • Description: When an error occurs on the replica, replication usually halts. This option permits a list of error codes 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 primary's. 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 slave_skipped_errors.
  • Commandline: --slave-skip-errors=[error_code1,error_code2,...|all|ddl_exist_errors]
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: OFF
  • Valid Values: [list of error codes], ALL, OFF

slave_sql_verify_checksum

  • Description: Verify binlog checksums when the replica SQL thread reads events from the relay log.
  • Commandline: --slave-sql-verify-checksum=[0|1]
  • Scope: Global
  • Access Type: Can be changed dynamically
  • Data Type: bool
  • Default Value: ON (1)

slave_transaction_retries

  • Description: Number of times a replication 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 innodb_lock_wait_timeout system variable.
  • Commandline: --slave-transaction-retries=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10
  • Range - 32 bit: 0 to 4294967295
  • Range - 64 bit: 0 to 18446744073709547520

slave_transaction_retry_errors

  • Description: When an error occurs during a transaction on the replica, replication usually halts. By default, transactions that caused a deadlock or elapsed lock wait timeout will be retried. One can add other errors to the the list of errors that should be retried by adding a comma-separated list of error numbers to this variable. This is particularly useful in some Spider setups. Some recommended errors to retry for Spider are 1158,1159,1160,1161,1429,2013,12701.(From MariaDB 10.4.5, these are in the default value)
  • Commandline: --slave-transaction_retry-errors=[error_code1,error_code2,...]
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value:
  • Valid Values: comma-separated list of error codes
  • Introduced: MariaDB 10.3.3

slave_transaction_retry_interval

  • Description: Interval in seconds for the replica SQL thread to retry a failed transaction due to a deadlock, elapsed lock wait timeout or an error listed in slave_transaction_retry_errors. The interval is calculated as max(slave_transaction_retry_interval, min(retry_count, 5)).
  • Commandline: --slave-transaction-retry-interval=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 3600
  • Introduced: MariaDB 10.3.3

slave_type_conversions

  • Description: Determines the type conversion mode on the replica when using row-based replication, 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.
    • Empty (default) means that the server should give an error and replication should stop if the table definition is different between the primary and replica.
  • Commandline: --slave-type-conversions=set
  • Scope: Global
  • Dynamic: Yes
  • Data Type: set
  • Default Value: Empty variable
  • Valid Values: ALL_LOSSY, ALL_NON_LOSSY, empty

sql_log_bin

  • Description: If set to 0 (1 is the default), no logging to the binary log 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.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

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 SET GLOBAL sql_slave_skip_counter.
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0

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 write per transaction. If the disk has cache backed by battery, synchronization will be fast and a more conservative number can be chosen.
  • Commandline: --sync-binlog=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 4294967295

sync_master_info

  • Description: A replication 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.
  • Commandline: --sync-master-info=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10000

sync_relay_log

  • Description: The MariaDB server will synchronize its relay log to disk after this many writes to the log. The default until MariaDB 10.1.7 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.
  • Commandline: --sync-relay-log=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10000

sync_relay_log_info

  • Description: A replication replica will synchronize its relay-log.info file to disk after this many transactions. The default until MariaDB 10.1.7 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.
  • Commandline: --sync-relay-log-info=#
  • Scope: Global,
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10000
  • Range: 0 to 4294967295

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.