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).
See also the .
auto_increment_incrementDescription: 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_offsetDescription: 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_phaseDescription: 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_eventsDescription: 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_sizeDescription: 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_checksumDescription: Specifies the type of BINLOG_CHECKSUM_ALG for log events in the .
Command line:
--binlog-checksum=name
--binlog-checksum=[0|1]
binlog_commit_wait_countDescription: 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_usecDescription: 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_updatesDescription: 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_dbDescription: 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_secondsDescription: 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_sizeDescription: Size of in-memory cache that is allocated when reading and files.
Command line: --binlog-file-cache-size=#
Scope: Global, Session
Dynamic: Yes
binlog_formatDescription: 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_indexDescription: 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_sizeDescription: 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_minDescription: 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_dbDescription: 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_thresholdDescription: 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_posDescription: 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_schedulingDescription: 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_sizeDescription: 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_imageDescription: 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_metadataDescription: 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_limitDescription: Alias for .
Introduced:
binlog_stmt_cache_sizeDescription: 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_formatsDescription: 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_connectionDescription: 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_binlogDescription: Encrypt (including ). See and .
Command line: --encrypt-binlog[={0|1}]
Scope: Global
Dynamic: No
expire_logs_daysDescription: 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_slaveDescription: 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_binDescription: 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_basenameDescription: 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_compressDescription: 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_lenDescription: 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_indexDescription: 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_creatorsDescription: 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_statementsDescription: 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_updatesDescription: 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_fileDescription: 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_checksumDescription: 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_sizeDescription: 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_sizeDescription: 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_sizeDescription: 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_sizeDescription: 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_sizeDescription: 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_limitDescription: 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_logDescription: 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_basenameDescription: 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_indexDescription: 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_fileDescription: 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_purgeDescription: 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_recoveryDescription: 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_limitDescription: 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_eventsDescription: 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_dbDescription: 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_tableDescription: 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_skipDescription: 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_dbDescription: 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_tableDescription: 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_dbDescription: 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_idDescription: 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_tableDescription: 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_tableDescription: 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_hostDescription: 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_passwordDescription: 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_portDescription: 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_userDescription: 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_idDescription: 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_infoDescription: 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_replicationDescription: 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_replicationDescription: 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_timeoutDescription: 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_protocolDescription: 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_purgeDescription: 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_modeDescription: 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_threadsDescription: 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_modeDescription: 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_tmpdirDescription: 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_packetDescription: 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_timeDescription: 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_timeoutDescription: 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_queuedDescription: 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_modeDescription: 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_threadsDescription: 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_workersDescription: Alias for .
Command line: --slave-parallel-workers=#
slave_run_triggers_for_rbrDescription: See for a description and use-case for this setting.
Command line: --slave-run-triggers-for-rbr=value
Scope: Global
Dynamic: Yes
slave_skip_errorsDescription: 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_checksumDescription: 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_retriesDescription: 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_errorsDescription: 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_intervalDescription: 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_conversionsDescription: 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_binDescription: 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_counterDescription: 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_binlogDescription: 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_infoDescription: 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_logDescription: 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_infoDescription: 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
Data Type: numeric
Default Value: 1
Range: 1 to 65535
Data Type: numeric
Default Value: 1
Range: 1 to 65535
Data Type: boolean
Default Value: OFF
Introduced:
Data Type: boolean
Default Value: ON
Data Type: numeric
Default Value: 32768
Range - 32 bit: 4096 to 4294967295
Range - 64 bit: 4096 to 18446744073709547520
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: CRC32
Valid Values: NONE (0), CRC32 (1)
Command line: --binlog-commit-wait-count=#]
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 18446744073709551615
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 100000
Range: 0 to 18446744073709551615
Dynamic: Yes
Data Type: boolean
Default Value: OFF (0)
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.
Command line: --binlog-do-db=#
Scope: Global
Dynamic: No
Data Type: string
Default Value: NULL
Introduced: (as a system variable)
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 8553600
Introduced:
Data Type: numeric
Default Value: 16384
Range: 8192 to 18446744073709551615
Dynamic: Yes
Data Type: enumeration
Default Value: MIXED
Valid Values: ROW, STATEMENT or MIXED
Data Type: boolean
Default Value: ON
Introduced:
Data Type: numeric
Default Value: 4096
Range: 64 to 16777216
Introduced:
Data Type: numeric
Default Value: 65536
Range: 1 to 1073741824
Introduced:
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.
Command line: --binlog-ignore-db=name
Scope: Global
Dynamic: No
Data Type: string
Default Value: NULL
Introduced:
Data Type: bigint unsigned
Default Value: 134217728
Range: 10485760 to 18446744073709551615
Introduced:
Data Type: boolean
Default Value: OFF
Introduced:
Dynamic: No
Data Type: boolean
Default Value: ON
Deprecated:
Data Type: numeric
Default Value: 8192
Range: 256 to 4294967040 (in multiples of 256)
Introduced:
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 .
Command line: --binlog-row-image=value
Scope: Global, Session
Dynamic: Yes
Data Type: enum
Default Value: FULL
Valid Values:
<= : FULL, NOBLOB or MINIMAL
>=: FULL, NOBLOB, MINIMAL or FULL_NODUP
FULL: All metadata is logged.Command line: --binlog-row-metadata=value
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: NO_LOG
Valid Values: NO_LOG, MINIMAL, FULL
Introduced:
Dynamic: Yes
Data Type: numeric
Default Value: 32768
Range - 32 bit: 4096 to 4294967295
Range - 64 bit: 4096 to 18446744073709547520
Data Type: enum
Default Value: STATEMENT
Valid Values: STATEMENT or MIXED,STATEMENT
Introduced:
Data Type: string
Default Value: '' (empty string)
Data Type: boolean
Default Value: OFF
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0.000000 (>= ), 0 (<= )
Range: 0 to 99
Data Type: string
Related variables: init_connect
--log-bin[=name]Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Related variables: sql_log_bin
Dynamic: No
Data Type: string
Default Value: None
Dynamic: No
Data Type: boolean
Default Value: OFF
Data Type: numeric
Default Value: 256
Range: 10 to 1024
Dynamic: No
Data Type: string
Default Value: None
MODIFIES SQL DATAThis 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.
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.
Command line: --log-bin-trust-function-creators[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Data Type: boolean
Default Value: ON
Dynamic: No
Data Type: boolean
Default Value: OFF
Data Type: string
Default Value: master.info
Introduced: (as a system variable, previously just an option)
Data Type: bool
Default Value: OFF (0)
Dynamic: Yes
Data Type: numeric
Default Value: 18446744073709547520
Range: 4096 to 18446744073709547520
Data Type: numeric
Default Value: 1073741824 (1GB)
Range: 4096 to 1073741824 (4KB to 1GB)
Data Type: numeric
Default Value: 18446744073709547520 (64 bit), 4294963200 (32 bit)
Range: 4096 to 18446744073709547520
Data Type: numeric
Default Value: 0
Range: 0 to 18446744073709551615
Introduced:
Data Type: numeric
Default Value: 0
Range: 0, or 4096 to 1073741824 (4KB to 1GB)
Data Type: numeric
Default Value: 0 (no limit)
Range: 0 to 18446744073709551615
Dynamic: No
Data Type: filename
Default Value: '' (none)
Data Type: string
Default Value: None
Dynamic: No
Dynamic: No
Data Type: string
Default Value: None
Scope: Global
Dynamic: No
Data Type: string
Default Value: relay-log.info
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.
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Data Type: numeric
Default Value: 0
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520
Data Type: boolean
Default Value: ON
See Replication Filters for more information.
Command line: --replicate-do-db=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: '' (empty)
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.
Command line: --replicate-do-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: '' (empty)
Data Type: enumeration
Default Value: replicate
Valid Values: REPLICATE, FILTER_ON_SLAVE, FILTER_ON_MASTER
See Replication Filters for more information.
Command line: --replicate-ignore-db=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: '' (empty)
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.
Command line: --replicate-ignore-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: '' (empty)
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 , 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
Command line: --replicate-rewrite-db=primary_database->replica_database
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: '' (empty)
Introduced:
Data Type: boolean
Default Value: OFF
Introduced: (as a system variable, previously just an option)
See Replication Filters for more information.
Command line: --replicate-wild-do-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: '' (empty)
See Replication Filters for more information.
Command line: --replicate-wild-ignore-table=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: '' (empty)
Data Type: string
Data Type: string
Data Type: numeric
Default Value: 0
Range: 0 to 65535
Data Type: string
In and below, the default server_id value is 0. If a replica's server_id value is 0, then all primary's will refuse its connection attempts. If a primary's server_id value is 0, then it will refuse all replica connection attempts.
Command line: --server-id =#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range: 1 to 4294967295
Data Type: boolean
Default Value: OFF
Introduced: (as a system variable, previously just an option)
Data Type: boolean
Default Value: OFF
Data Type: boolean
Default Value: OFF
Data Type: double
Default Value: 31536000.000000
Range: 0 to 31536000
Introduced:
Data Type: boolean
Default Value: 0
Data Type: numeric
Default Value: 1; 0 on Galera cluster nodes.
Range: 0 to 18446744073709551615
Introduced:
Scope: Global
Dynamic: Yes
Data Type: enumeration
Default Value: IDEMPOTENT
Valid Values: IDEMPOTENT, STRICT
Data Type: numeric
Default Value: 0
Valid Values: 0 to 16383
Default Value: IDEMPOTENT (NDB), STRICT (All)
Valid Values: IDEMPOTENT, STRICT
Data Type: file name
Default Value: /tmp
Data Type: numeric
Default Value: 1073741824
Range: 1024 to 1073741824
Data Type: numeric
Default Value: 0.000000
Range: 0 to 31536000
Introduced:
Data Type: numeric
Default Value:
60 (1 minute)
Range: 1 to 31536000
This system variable is only meaningful when parallel
replication is configured (i.e. when slave_parallel_threads > 0).
See Parallel Replication: Configuring the Maximum Size of the Parallel Slave Queue for more information.
Command line: --slave-parallel-max-queued=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 131072
Range: 0 to 2147483647
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.
Command line: None
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: optimistic (>= ), conservative (<= )
Valid Values: conservative, optimistic, none, aggressive and minimal
The replica threads must be stopped in order to change this option's value dynamically.
Events that were logged with GTIDs with different gtid_domain_id values can be applied in parallel in an out-of-order manner. Each gtid_domain_id can use the number of threads configured by slave_domain_parallel_threads.
Events that were group-committed on the primary can be applied in parallel in an in-order manner, and the specific behavior can be configured by setting slave_parallel_mode.
Command line: --slave-parallel-threads=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 16383
Data Type: enum
Default Value: NO
Valid Values: NO, YES, LOGGING, or ENFORCE (>= )
Dynamic: No
Data Type: string
Default Value: OFF
Valid Values: [list of error codes], ALL, OFF
Data Type: bool
Default Value: ON (1)
Data Type: numeric
Default Value: 10
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520
Dynamic: No
Data Type: string
Default Value:
1158,1159,1160,1161,1205,1213,1020,1429,2013,12701 (>= , , , , , )
1158,1159,1160,1161,1205,1213,1429,2013,12701 (>= )
Valid Values: comma-separated list of error codes
Introduced:
Data Type: numeric
Default Value: 0
Range: 0 to 3600
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.
Command line: --slave-type-conversions=set
Scope: Global
Dynamic: Yes
Data Type: set
Default Value: Empty variable
Valid Values: ALL_LOSSY, ALL_NON_LOSSY, empty
booleanDefault Value: 1
Related variables: log_bin
Default Value: 0
Data Type: numeric
Default Value: 0
Range: 0 to 4294967295
Data Type: numeric
Default Value: 10000
Data Type: numeric
Default Value: 10000
Data Type: numeric
Default Value: 10000
Range: 0 to 4294967295