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 mysqld 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
1
. Intended for use in master-to-master replication. --auto-increment-increment[=#]
numeric
1
1
to 65535
auto_increment_offset
1
. Intended for use in master-to-master replication. --auto-increment-offset[=#]
numeric
1
1
to 65535
binlog_annotate_row_events
--binlog-annotate-row-events[={0|1}]
ON
(>= MariaDB 10.2.4) OFF
(<= MariaDB 10.2.3) binlog_cache_size
--binlog-cache-size=#
numeric
32768
4096
to 4294967295
4096
to 18446744073709547520
binlog_checksum
--binlog-checksum=name
--binlog-checksum=[0|1]
string
CRC32
(>= MariaDB 10.2.1) NONE
(<= MariaDB 10.2.0) NONE
(0
), CRC32
(1
) binlog_commit_wait_count
--binlog-commit-wait-count=#]
numeric
0
0
- 18446744073709551615
binlog_commit_wait_usec
binlog_commit_wait_usec
. As soon as the number of pending commits reaches binlog_commit_wait_count, the wait will be terminated, though. Thus, this setting only takes effect if binlog_commit_wait_count
is non-zero. --binlog-commit-wait-usec#
numeric
100000
0
- 18446744073709551615
binlog_direct_non_transactional_updates
--binlog-direct-non-transactional-updates[=value]
boolean
OFF (0)
binlog_file_cache_size
--binlog-file-cache-size=#
numeric
16384
8192
to 18446744073709551615
binlog_format
binlog_format
only applies to normal (not replicated) updates. --binlog-format=format
enumeration
MIXED
(>= MariaDB 10.2.4) STATEMENT
(<= MariaDB 10.2.3) ROW
, STATEMENT
or MIXED
binlog_optimize_thread_scheduling
--binlog-optimize-thread-scheduling
or --skip-binlog-optimize-thread-scheduling
boolean
ON
binlog_row_image
FULL
: All columns in the before and after image are logged. This is the default, and the only behavior in earlier versions. NOBLOB
: mysqld 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. --binlog-row-image=value
enum
FULL
FULL
, NOBLOB
or MINIMAL
binlog_row_metadata
NO_LOG
: No metadata is logged (default). MINIMAL
: Only metadata required by a slave is logged. FULL
: All metadata is logged. --binlog-row-metadata=value
enum
NO_LOG
NO_LOG
, MINIMAL
, FULL
binlog_stmt_cache_size
--binlog-stmt-cache-size=#
numeric
32768
4096
to 4294967295
4096
to 18446744073709547520
default_master_connection
string
''
(empty string) encrypt_binlog
--encrypt-binlog[={0|1}]
boolean
OFF
expire_logs_days
--expire-logs-days=#
numeric
0
0
to 99
init_slave
--init-slave=name
string
log_bin
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. --log-bin[=name]
boolean
OFF
log_bin_basename
log_bin
system variable. No commandline option
string
Yes
log_bin_compress
0
(the default) means no compression. See Compressing Events to Reduce Size of the Binary Log. --log-bin-compress
boolean
OFF
log_bin_compress_min_len
--log-bin-compress-min-len
numeric
256
10
to 1024
log_bin_index
--log-bin-index=name
string
log_bin_trust_function_creators
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. 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. log_bin_trust_function_creators
is OFF
and log_bin
is ON
, the SUPER
privilege is also required to execute the following statements: log_bin_trust_function_creators
to ON
removes these requirements around functions characteristics and the SUPER
privileges. --log-bin-trust-function-creators[={0|1}]
boolean
OFF
log_slow_slave_statements
--log-slow-slave-statements
boolean
ON
(>= MariaDB 10.2.4) OFF
(<= MariaDB 10.2.3) log_slave_updates
0
, the default, updates on a slave received from a master during replication are not logged in the slave's binary log. If set to 1
, they are. The slave's binary log needs to be enabled for this to have an effect. Set to 1
if you want to daisy-chain the slaves. --log-slave-updates
boolean
OFF
master_verify_checksum
--master-verify-checksum=[0|1]
bool
OFF (0)
max_binlog_cache_size
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. --max-binlog-cache-size=#
numeric
18446744073709547520
4096
to 18446744073709547520
max_binlog_size
--max-binlog-size=#
numeric
1073741824
(1GB) 4096 to 1073741824
(4KB to 1GB) max_binlog_stmt_cache_size
--max-binlog-stmt-cache-size=#
numeric
18446744073709547520
(64 bit), 4294963200
(32 bit) 4096
to 18446744073709547520
max_relay_log_size
max_binlog_size
was global only, but with the implementation of multi-source replication in MariaDB 10.0, it could be set per session as well. --max-relay-log-size=#
numeric
0
0
, or 4096 to 1073741824
(4KB to 1GB) read_binlog_speed_limit
--read-binlog-speed-limit=#
numeric
0
(no limit) 0
to 18446744073709551615
relay_log
--relay-log=file_name
filename
''
(none) relay_log_basename
No commandline option
string
Yes
relay_log_index
--relay-log-index=name
string
relay_log_info_file
RELAY_LOG_FILE
and RELAY_LOG_POS
options (i.e. the relay log position) for the CHANGE MASTER
statement are written. The slave's SQL thread keeps this relay log position updated as it applies events. --relay-log-info-file=file_name
string
relay-log.info
relay_log_purge
1
(the default), relay logs will be purged as soon as they are no longer necessary. --relay-log-purge={0|1}
boolean
ON
relay_log_recovery
1
(0
is default), on startup the slave will drop all relay logs that haven't yet been processed, and retrieve relay logs from the master. Can be useful after the slave 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. --relay-log-recovery
boolean
OFF
relay_log_space_limit
0
, or no limit. --relay-log-space-limit=#
numeric
0
0
to 4294967295
0
to 18446744073709547520
replicate_annotate_row_events
--replicate-annotate-row-events
boolean
ON
(>= MariaDB 10.2.4) OFF
(<= MariaDB 10.2.3) replicate_do_db
SET GLOBAL
, the system variable accepts a comma-separated list of filters. --replicate-do-db=name
string
''
(empty) replicate_do_table
dbname.tablename
. SET GLOBAL
, the system variable accepts a comma-separated list of filters. --replicate-do-table=name
string
''
(empty) replicate_events_marked_for_skip
@@skip_replication
flag. See Selectively skipping replication of binlog events for more information. --replicate-events-marked-for-skip
enumeration
replicate
REPLICATE
, FILTER_ON_SLAVE
, FILTER_ON_MASTER
replicate_ignore_db
SET GLOBAL
, the system variable accepts a comma-separated list of filters. --replicate-ignore-db=name
string
''
(empty) replicate_ignore_table
dbname.tablename
. SET GLOBAL
, the system variable accepts a comma-separated list of filters. --replicate-ignore-table=name
string
''
(empty) replicate_rewrite_db
replicate_rewrite_db
is not available as a system variable, only as a mysqld option. See the description on that page. replicate_wild_do_table
LIKE
operator. SET GLOBAL
, the system variable accepts a comma-separated list of filters. --replicate-wild-do-table=name
string
''
(empty) replicate_wild_ignore_table
LIKE
operator. SET GLOBAL
, the system variable accepts a comma-separated list of filters. --replicate-wild-ignore-table=name
string
''
(empty) report_host
--report-host=host_name
string
report_password
--show-slave-auth-info
is set. This password has no connection with user privileges or with the replication user account password. --report-password=password
string
report_port
--report-port=#
numeric
0
0
to 65535
report_user
--show-slave-auth-info
is set. This username has no connection with user privileges or with the replication user account. --report-user=name
string
server_id
server_id
value. server_id
values. server_id
value is 0
. If a slave's server_id
value is 0
, then all masters will refuse its connection attempts. If a master's server_id
value is 0
, then it will refuse all slave connection attempts. --server-id =#
numeric
1
(>= MariaDB 10.2.2), 0
(<= MariaDB 10.2.1) 1
to 4294967295
(>= MariaDB 10.2.2), 0
to 4294967295
(<= MariaDB 10.2.1) skip_parallel_replication
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. boolean
OFF
skip_replication
--replicate-events-marked-for-skip
set different from its default of REPLICATE
. See Selectively skipping replication of binlog events for more information. boolean
OFF
slave_compressed_protocol
--slave-compressed-protocol
boolean
0
slave_ddl_exec_mode
STRICT
and IDEMPOTENT
(default). In IDEMPOTENT
mode, the slave will not stop for failed DDL operations that would not cause a difference between the master and the slave. In particular CREATE TABLE is treated as CREATE OR REPLACE TABLE and DROP TABLE is treated as DROP TABLE IF EXISTS
. --slave-ddl-exec-mode=name
enumeration
IDEMPOTENT
IDEMPOTENT
, STRICT
slave_domain_parallel_threads
--slave-domain-parallel-threads=#
numeric
0
0
to 16383
slave_exec_mode
enumeration
IDEMPOTENT
(NDB), STRICT
(All) IDEMPOTENT
, STRICT
slave_load_tmpdir
--slave-load-tmpdir=path
file name
/tmp
slave_max_allowed_packet
--slave-max-allowed-packet=#
numeric
1073741824
1024
to 1073741824
slave_net_timeout
--slave-net-timeout=#
numeric
60 (1 minute)
(>= MariaDB 10.2.4) 3600 (1 hour)
(<= MariaDB 10.2.3) 1 upwards
slave_parallel_max_queued
slave_parallel_threads
> 0
). --slave-parallel-max-queued=#
numeric
131072
0
to 2147483647
slave_parallel_mode
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 maximise the parallelism, possibly at the cost of increased conflict rate. minimal
: only parallelizes the commit steps of transactions. none
disables parallel apply completely. enum
conservative
conservative
, optimistic
, none
, aggressive
and minimal
slave_parallel_threads
0
, then its value will determine how many slave worker threads will be created to apply binary log events in parallel. 0
(which is the default value), then no slave worker threads will be created. Instead, when replication is enabled, binary log events are applied by the slave's SQL thread. 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
. slave_parallel_mode
. --slave-parallel-threads=#
numeric
0
0
to 16383
slave_parallel_workers
--slave-parallel-workers=#
slave_run_triggers_for_rbr
--slave-run-triggers-for-rbr=value
enum
NO
NO
, YES
or LOGGING
slave_skip_errors
all
as an option permits the slave 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. --slave-skip-errors=[error_code1,error_code2,...|all|ddl_exist_errors]
string
OFF
[list of error codes]
, ALL
, OFF
slave_sql_verify_checksum
--slave-sql-verify-checksum=[0|1]
bool
ON (1)
slave_transaction_retries
--slave-transaction-retries=#
numeric
10
0
to 4294967295
0
to 18446744073709547520
slave_transaction_retry_errors
--slave-transaction_retry-errors=[error_code1,error_code2,...]
string
1158,1159,1160,1161,1205,1213,1429,2013,12701
(>= MariaDB 10.4.5) 1213,1205
(>= MariaDB 10.3.3) comma-separated list of error codes
slave_transaction_retry_interval
max(slave_transaction_retry_interval, min(retry_count, 5))
. --slave-transaction-retry-interval=#
numeric
0
0
to 3600
slave_type_conversions
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. --slave-type-conversions=set
set
Empty variable
ALL_LOSSY
, ALL_NON_LOSSY
, empty sql_log_bin
MariaDB 10.1.7
, this variable does not affect the replication of events in a Galera cluster. boolean
1
sql_slave_skip_counter
numeric
0
sync_binlog
--sync-binlog=#
numeric
0
0
to 4294967295
sync_master_info
--sync-master-info=#
numeric
10000
(>= MariaDB 10.1.7), 0
(<= MariaDB 10.1.6) sync_relay_log
--sync-relay-log=#
numeric
10000
(>= MariaDB 10.1.7), 0
(<= MariaDB 10.1.6) sync_relay_log_info
--sync-relay-log-info=#
numeric
10000
(>= MariaDB 10.1.7), 0
(<= MariaDB 10.1.6) 0
to 4294967295
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/replication-and-binary-log-system-variables/