Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.4Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 202.2Kb
Man Pages (Zip) - 307.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Replication and Binary Logging Option and Variable Reference

18.1.6.1 Replication and Binary Logging Option and Variable Reference

The following tables list basic information about the MySQL command-line options and system variables applicable to replication and the binary log.

Table 18.3 Summary of Replication options and variables in MySQL 5.7

Option or Variable Name
Command Line System Variable Status Variable
Option File Scope Dynamic
Notes

abort-slave-event-count

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

binlog_gtid_simple_recovery

Yes Yes No
Yes Global No

DESCRIPTION: Controls how binary logs are iterated during GTID recovery

Com_change_master

No No Yes
No Both No

DESCRIPTION: Count of CHANGE MASTER TO statements

Com_show_master_status

No No Yes
No Both No

DESCRIPTION: Count of SHOW MASTER STATUS statements

Com_show_new_master

No No Yes
No Both No

DESCRIPTION: Count of SHOW NEW MASTER statements

Com_show_slave_hosts

No No Yes
No Both No

DESCRIPTION: Count of SHOW SLAVE HOSTS statements

Com_show_slave_status

No No Yes
No Both No

DESCRIPTION: Count of SHOW SLAVE STATUS statements

Com_show_slave_status_nonblocking

No No Yes
No Both No

DESCRIPTION: Count of SHOW SLAVE STATUS NONBLOCKING statements

Com_slave_start

No No Yes
No Both No

DESCRIPTION: Count of START SLAVE statements

Com_slave_stop

No No Yes
No Both No

DESCRIPTION: Count of STOP SLAVE statements

disconnect-slave-event-count

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

enforce-gtid-consistency

Yes Yes No
Yes Global Yes

DESCRIPTION: Prevents execution of statements that cannot be logged in a transactionally safe manner

enforce_gtid_consistency

Yes Yes No
Yes Global Yes

DESCRIPTION: Prevents execution of statements that cannot be logged in a transactionally safe manner

executed-gtids-compression-period

Yes No No
Yes No

DESCRIPTION: Deprecated and will be removed in a future version. Use the renamed gtid-executed-compression-period instead.

executed_gtids_compression_period

No Yes No
No Global Yes

DESCRIPTION: Deprecated and will be removed in a future version. Use the renamed gtid_executed_compression_period instead.

gtid-executed-compression-period

Yes No No
Yes No

DESCRIPTION: Compress gtid_executed table each time this many transactions have occurred. 0 means never compress this table. Applies only when binary logging is disabled.

gtid-mode

Yes Yes No
Yes Global Yes

DESCRIPTION: Controls whether GTID based logging is enabled and what type of transactions the logs can contain

gtid_executed

No Yes No
No Global No

DESCRIPTION: Global: All GTIDs in the binary log (global) or current transaction (session). Read-only.

gtid_executed_compression_period

No Yes No
No Global Yes

DESCRIPTION: Compress gtid_executed table each time this many transactions have occurred. 0 means never compress this table. Applies only when binary logging is disabled.

gtid_mode

No Yes No
No Global Yes

DESCRIPTION: Controls whether GTID based logging is enabled and what type of transactions the logs can contain

gtid_next

No Yes No
No Session Yes

DESCRIPTION: Specifies the GTID for the next statement to execute. See documentation for details.

gtid_owned

No Yes No
No Both No

DESCRIPTION: The set of GTIDs owned by this client (session), or by all clients, together with the thread ID of the owner (global). Read-only.

gtid_purged

No Yes No
No Global Yes

DESCRIPTION: The set of all GTIDs that have been purged from the binary log.

init_slave

Yes Yes No
Yes Global Yes

DESCRIPTION: Statements that are executed when a slave connects to a master

log-slave-updates

Yes Yes No
Yes Global No

DESCRIPTION: Tells the slave to log the updates performed by its SQL thread to its own binary log

log_slave_updates

Yes Yes No
Yes Global No

DESCRIPTION: Whether the slave should log the updates performed by its SQL thread to its own binary log. Read-only; set using the --log-slave-updates server option.

log_statements_unsafe_for_binlog

No Yes No
No Global Yes

DESCRIPTION: Disables error 1592 warnings being written to the error log

master-info-file

Yes No No
Yes No

DESCRIPTION: The location and name of the file that remembers the master and where the I/O replication thread is in the master's binary logs

master-info-repository

Yes No No
Yes No

DESCRIPTION: Whether to write master status information and replication I/O thread location in the master's binary logs to a file or table.

master-retry-count

Yes No No
Yes No

DESCRIPTION: Number of tries the slave makes to connect to the master before giving up

master_info_repository

Yes Yes No
Yes Global Yes

DESCRIPTION: Whether to write master status information and replication I/O thread location in the master's binary logs to a file or table

relay-log

Yes Yes No
Yes Global No

DESCRIPTION: The location and base name to use for relay logs

relay-log-index

Yes Yes No
Yes Global No

DESCRIPTION: The location and name to use for the file that keeps a list of the last relay logs

relay-log-info-file

Yes No No
Yes No

DESCRIPTION: The location and name of the file that remembers where the SQL replication thread is in the relay logs

relay-log-info-repository

Yes No No
Yes No

DESCRIPTION: Whether to write the replication SQL thread's location in the relay logs to a file or a table.

relay-log-recovery

Yes No No
Yes No

DESCRIPTION: Enables automatic recovery of relay log files from master at startup

relay_log_basename

No Yes No
No Global No

DESCRIPTION: Complete path to relay log, including filename

relay_log_index

Yes Yes No
Yes Global No

DESCRIPTION: The name of the relay log index file

relay_log_info_file

Yes Yes No
Yes Global No

DESCRIPTION: The name of the file in which the slave records information about the relay logs

relay_log_info_repository

No Yes No
No Global Yes

DESCRIPTION: Whether to write the replication SQL thread's location in the relay logs to a file or a table

relay_log_purge

Yes Yes No
Yes Global Yes

DESCRIPTION: Determines whether relay logs are purged

relay_log_recovery

Yes Yes No
Yes Global Yes

DESCRIPTION: Whether automatic recovery of relay log files from master at startup is enabled; must be enabled for a crash-safe slave.

relay_log_space_limit

Yes Yes No
Yes Global No

DESCRIPTION: Maximum space to use for all relay logs

replicate-do-db

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread to restrict replication to the specified database

replicate-do-table

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread to restrict replication to the specified table

replicate-ignore-db

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread not to replicate to the specified database

replicate-ignore-table

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread not to replicate to the specified table

replicate-rewrite-db

Yes No No
Yes No

DESCRIPTION: Updates to a database with a different name than the original

replicate-same-server-id

Yes No No
Yes No

DESCRIPTION: In replication, if set to 1, do not skip events having our server id

replicate-wild-do-table

Yes No No
Yes No

DESCRIPTION: Tells the slave thread to restrict replication to the tables that match the specified wildcard pattern

replicate-wild-ignore-table

Yes No No
Yes No

DESCRIPTION: Tells the slave thread not to replicate to the tables that match the given wildcard pattern

report-host

Yes Yes No
Yes Global No

DESCRIPTION: Host name or IP of the slave to be reported to the master during slave registration

report-password

Yes Yes No
Yes Global No

DESCRIPTION: An arbitrary password that the slave server should report to the master. Not the same as the password for the MySQL replication user account.

report-port

Yes Yes No
Yes Global No

DESCRIPTION: Port for connecting to slave reported to the master during slave registration

report-user

Yes Yes No
Yes Global No

DESCRIPTION: An arbitrary user name that a slave server should report to the master. Not the same as the name used with the MySQL replication user account.

Rpl_semi_sync_master_clients

No No Yes
No Global No

DESCRIPTION: Number of semisynchronous slaves

rpl_semi_sync_master_enabled

No Yes No
No Global Yes

DESCRIPTION: Whether semisynchronous replication is enabled on the master

Rpl_semi_sync_master_net_avg_wait_time

No No Yes
No Global No

DESCRIPTION: The average time the master waited for a slave reply

Rpl_semi_sync_master_net_wait_time

No No Yes
No Global No

DESCRIPTION: The total time the master waited for slave replies

Rpl_semi_sync_master_net_waits

No No Yes
No Global No

DESCRIPTION: The total number of times the master waited for slave replies

Rpl_semi_sync_master_no_times

No No Yes
No Global No

DESCRIPTION: Number of times the master turned off semisynchronous replication

Rpl_semi_sync_master_no_tx

No No Yes
No Global No

DESCRIPTION: Number of commits not acknowledged successfully

Rpl_semi_sync_master_status

No No Yes
No Global No

DESCRIPTION: Whether semisynchronous replication is operational on the master

Rpl_semi_sync_master_timefunc_failures

No No Yes
No Global No

DESCRIPTION: Number of times the master failed when calling time functions

rpl_semi_sync_master_timeout

No Yes No
No Global Yes

DESCRIPTION: Number of milliseconds to wait for slave acknowledgment

rpl_semi_sync_master_trace_level

No Yes No
No Global Yes

DESCRIPTION: The semisynchronous replication debug trace level on the master

Rpl_semi_sync_master_tx_avg_wait_time

No No Yes
No Global No

DESCRIPTION: The average time the master waited for each transaction

Rpl_semi_sync_master_tx_wait_time

No No Yes
No Global No

DESCRIPTION: The total time the master waited for transactions

Rpl_semi_sync_master_tx_waits

No No Yes
No Global No

DESCRIPTION: The total number of times the master waited for transactions

rpl_semi_sync_master_wait_for_slave_count

No Yes No
No Global Yes

DESCRIPTION: How many slave acknowledgments the master must receive per transaction before proceeding

rpl_semi_sync_master_wait_no_slave

No Yes No
No Global Yes

DESCRIPTION: Whether master waits for timeout even with no slaves

rpl_semi_sync_master_wait_point

No Yes No
No Global Yes

DESCRIPTION: The wait point for slave transaction receipt acknowledgment

Rpl_semi_sync_master_wait_pos_backtraverse

No No Yes
No Global No

DESCRIPTION: The total number of times the master waited for an event with binary coordinates lower than events waited for previously

Rpl_semi_sync_master_wait_sessions

No No Yes
No Global No

DESCRIPTION: Number of sessions currently waiting for slave replies

Rpl_semi_sync_master_yes_tx

No No Yes
No Global No

DESCRIPTION: Number of commits acknowledged successfully

rpl_semi_sync_slave_enabled

No Yes No
No Global Yes

DESCRIPTION: Whether semisynchronous replication is enabled on slave

Rpl_semi_sync_slave_status

No No Yes
No Global No

DESCRIPTION: Whether semisynchronous replication is operational on slave

rpl_semi_sync_slave_trace_level

No Yes No
No Global Yes

DESCRIPTION: The semisynchronous replication debug trace level on the slave

rpl_stop_slave_timeout

Yes Yes No
Yes Global Yes

DESCRIPTION: Set the number of seconds that STOP SLAVE waits before timing out.

server_uuid

No Yes No
No Global No

DESCRIPTION: The server's globally unique ID, automatically (re)generated at server start

show-slave-auth-info

Yes No No
Yes No

DESCRIPTION: Show user name and password in SHOW SLAVE HOSTS on this master

simplified_binlog_gtid_recovery

Yes Yes No
Yes Global No

DESCRIPTION: Controls how binary logs are iterated during GTID recovery

skip-slave-start

Yes No No
Yes No

DESCRIPTION: If set, slave is not autostarted

slave-checkpoint-group

Yes No No
Yes No

DESCRIPTION: Maximum number of transactions processed by a multi-threaded slave before a checkpoint operation is called to update progress status. Not supported by MySQL Cluster.

slave-checkpoint-period

Yes No No
Yes No

DESCRIPTION: Update progress status of multi-threaded slave and flush relay log info to disk after this number of milliseconds. Not supported by MySQL Cluster.

slave-load-tmpdir

Yes Yes No
Yes Global No

DESCRIPTION: The location where the slave should put its temporary files when replicating a LOAD DATA INFILE statement

slave-max-allowed-packet

Yes No No
Yes No

DESCRIPTION: Maximum size, in bytes, of a packet that can be sent from a replication master to a slave; overrides max_allowed_packet.

slave_net_timeout

Yes Yes No
Yes Global Yes

DESCRIPTION: Number of seconds to wait for more data from a master/slave connection before aborting the read

slave-parallel-type

Yes No No
Yes No

DESCRIPTION: Tells the slave to use database partioning (DATABASE) or timestamp information (LOGICAL_CLOCK) from the master to parallelize transactions. The default is DATABASE.

slave-parallel-workers

Yes No No
Yes No

DESCRIPTION: Number of worker threads for executing events in parallel. Set to 0 (the default) to disable slave multi-threading. Not supported by MySQL Cluster.

slave-pending-jobs-size-max

Yes No No
No No

DESCRIPTION: Maximum size of slave worker queues holding events not yet applied.

slave-rows-search-algorithms

Yes No No
Yes No

DESCRIPTION: Determines search algorithms used for slave update batching. Any 2 or 3 from the list INDEX_SEARCH, TABLE_SCAN, HASH_SCAN; the default is TABLE_SCAN,INDEX_SCAN.

slave-skip-errors

Yes Yes No
Yes Global No

DESCRIPTION: Tells the slave thread to continue replication when a query returns an error from the provided list

slave_checkpoint_group

Yes Yes No
Yes Global Yes

DESCRIPTION: Maximum number of transactions processed by a multi-threaded slave before a checkpoint operation is called to update progress status. Not supported by MySQL Cluster.

slave_checkpoint_period

Yes Yes No
Yes Global Yes

DESCRIPTION: Update progress status of multi-threaded slave and flush relay log info to disk after this number of milliseconds. Not supported by MySQL Cluster.

slave_compressed_protocol

Yes Yes No
Yes Global Yes

DESCRIPTION: Use compression on master/slave protocol

slave_exec_mode

Yes Yes No
Yes Global Yes

DESCRIPTION: Allows for switching the slave thread between IDEMPOTENT mode (key and some other errors suppressed) and STRICT mode; STRICT mode is the default, except for MySQL Cluster, where IDEMPOTENT is always used

Slave_heartbeat_period

No No Yes
No Global No

DESCRIPTION: The slave's replication heartbeat interval, in seconds

slave_max_allowed_packet

No Yes No
No Global Yes

DESCRIPTION: Maximum size, in bytes, of a packet that can be sent from a replication master to a slave; overrides max_allowed_packet.

Slave_open_temp_tables

No No Yes
No Global No

DESCRIPTION: Number of temporary tables that the slave SQL thread currently has open

slave_parallel_type

No Yes No
No Global Yes

DESCRIPTION: Tells the slave to use database partioning (DATABASE) or information (LOGICAL_CLOCK) from master to parallelize transactions. The default is DATABASE.

slave_parallel_workers

Yes Yes No
No Global Yes

DESCRIPTION: Number of worker threads for executing events in parallel. Set to 0 (the default) to disable slave multi-threading. Not supported by MySQL Cluster.

slave_pending_jobs_size_max

No Yes No
No Global Yes

DESCRIPTION: Maximum size of slave worker queues holding events not yet applied.

slave_preserve_commit_order

Yes Yes No
No Global Yes

DESCRIPTION: Ensures that all commits by slave workers happen in the same order as on the master to maintain consistency when using parallel worker threads.

Slave_retried_transactions

No No Yes
No Global No

DESCRIPTION: The total number of times since startup that the replication slave SQL thread has retried transactions

slave_rows_search_algorithms

No Yes No
No Global Yes

DESCRIPTION: Determines search algorithms used for slave update batching. Any 2 or 3 from the list INDEX_SEARCH, TABLE_SCAN, HASH_SCAN; the default is TABLE_SCAN,INDEX_SCAN.

Slave_running

No No Yes
No Global No

DESCRIPTION: The state of this server as a replication slave (slave I/O thread status)

slave_transaction_retries

Yes Yes No
Yes Global Yes

DESCRIPTION: Number of times the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait timeout, before giving up and stopping

slave_type_conversions

Yes Yes No
Yes Global No

DESCRIPTION: Controls type conversion mode on replication slave. Value is a list of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY. Set to an empty string to disallow type conversions between master and slave.

sql_slave_skip_counter

No Yes No
No Global Yes

DESCRIPTION: Number of events from the master that a slave server should skip. Not compatible with GTID replication.

sync_binlog

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronously flush binary log to disk after every #th event

sync_master_info

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronize master.info to disk after every #th event.

sync_relay_log

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronize relay log to disk after every #th event.

sync_relay_log_info

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronize relay.info file to disk after every #th event.


Section 18.1.6.2, “Replication Master Options and Variables”, provides more detailed information about options and variables relating to replication master servers. For more information about options and variables relating to replication slaves, see Section 18.1.6.3, “Replication Slave Options and Variables”.

Table 18.4 Summary of Binary Logging options and variables in MySQL 5.7

Option or Variable Name
Command Line System Variable Status Variable
Option File Scope Dynamic
Notes

binlog-checksum

Yes No No
Yes No

DESCRIPTION: Enable/disable binary log checksums

binlog-do-db

Yes No No
Yes No

DESCRIPTION: Limits binary logging to specific databases

binlog_format

Yes Yes No
Yes Both Yes

DESCRIPTION: Specifies the format of the binary log

binlog-ignore-db

Yes No No
Yes No

DESCRIPTION: Tells the master that updates to the given database should not be logged to the binary log

binlog-row-event-max-size

Yes No No
Yes No

DESCRIPTION: Binary log max event size

binlog-rows-query-log-events

Yes No No
Yes No

DESCRIPTION: Enables logging of rows query log events when using row-based logging. Disabled by default. Do not enable when producing logs for pre-5.6.2 slaves/readers.

Binlog_cache_disk_use

No No Yes
No Global No

DESCRIPTION: Number of transactions that used a temporary file instead of the binary log cache

binlog_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Size of the cache to hold the SQL statements for the binary log during a transaction

Binlog_cache_use

No No Yes
No Global No

DESCRIPTION: Number of transactions that used the temporary binary log cache

binlog_checksum

No Yes No
No Global Yes

DESCRIPTION: Enable/disable binary log checksums

binlog_direct_non_transactional_updates

Yes Yes No
Yes Both Yes

DESCRIPTION: Causes updates using statement format to nontransactional engines to be written directly to binary log. See documentation before using.

binlog_error_action

Yes Yes No
Yes Both Yes

DESCRIPTION: Controls what happens when the server cannot write to the binary log.

binlog_group_commit_sync_delay

Yes Yes No
Yes Global Yes

DESCRIPTION: Sets the number of microseconds to wait before synchronizing transactions to disk.

binlog_group_commit_sync_no_delay_count

Yes Yes No
Yes Global Yes

DESCRIPTION: Sets the maximum number of transactions to wait for before aborting the current delay specified by binlog_group_commit_sync_delay.

binlog_max_flush_queue_time

No Yes No
No Global Yes

DESCRIPTION: How long to read transactions before flushing to binary log

binlog_order_commits

No Yes No
No Global Yes

DESCRIPTION: Whether to commit in same order as writes to binary log

binlog_row_image

Yes Yes No
Yes Both Yes

DESCRIPTION: Use full or minimal images when logging row changes. Allowed values are full, minimal, and noblob.

binlog_rows_query_log_events

No Yes No
No Both Yes

DESCRIPTION: When TRUE, enables logging of rows query log events in row-based logging mode. FALSE by default. Do not enable when producing logs for pre-5.6.2 replication slaves or other readers.

Binlog_stmt_cache_disk_use

No No Yes
No Global No

DESCRIPTION: Number of nontransactional statements that used a temporary file instead of the binary log statement cache

binlog_stmt_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Size of the cache to hold nontransactional statements for the binary log during a transaction

Binlog_stmt_cache_use

No No Yes
No Global No

DESCRIPTION: Number of statements that used the temporary binary log statement cache

binlogging_impossible_mode

Yes Yes No
Yes Both Yes

DESCRIPTION: Deprecated and will be removed in a future version. Use the renamed binlog_error_action instead.

Com_show_binlog_events

No No Yes
No Both No

DESCRIPTION: Count of SHOW BINLOG EVENTS statements

Com_show_binlogs

No No Yes
No Both No

DESCRIPTION: Count of SHOW BINLOGS statements

log-bin-use-v1-row-events

Yes Yes No
Yes Global No

DESCRIPTION: Use version 1 binary log row events

log_bin_basename

No Yes No
No Global No

DESCRIPTION: Complete path to binary log, including filename

log_bin_use_v1_row_events

Yes Yes No
Yes Global No

DESCRIPTION: Shows whether server is using version 1 binary log row events

master-verify-checksum

Yes No No
Yes No

DESCRIPTION: Cause master to examine checksums when reading from the binary log

master_verify_checksum

No Yes No
No Global Yes

DESCRIPTION: Cause master to read checksums from binary log.

max-binlog-dump-events

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

max_binlog_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Can be used to restrict the total size used to cache a multi-statement transaction

max_binlog_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Binary log will be rotated automatically when size exceeds this value

max_binlog_stmt_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Can be used to restrict the total size used to cache all nontransactional statements during a transaction

slave-sql-verify-checksum

Yes No No
Yes No

DESCRIPTION: Cause slave to examine checksums when reading from the relay log

slave_sql_verify_checksum

No Yes No
No Global Yes

DESCRIPTION: Cause slave to examine checksums when reading from relay log.

sporadic-binlog-dump-fail

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication


Section 18.1.6.4, “Binary Logging Options and Variables”, provides more detailed information about options and variables relating to binary logging. For additional general information about the binary log, see Section 6.4.4, “The Binary Log”.

For information about the sql_log_bin and sql_log_off variables, see Section 6.1.5, “Server System Variables”.

For a table showing all command-line options, system and status variables used with mysqld, see Section 6.1.3, “Server Option and Variable Reference”.


User Comments
Sign Up Login You must be logged in to post a comment.