16.1.6.4 Binary Logging Options and Variables (original) (raw)

System Variables Used with Binary Logging

The following list describes system variables for controlling binary logging. They can be set at server startup and some of them can be changed at runtime usingSET. Server options used to control binary logging are listed earlier in this section.

• binlog_cache_size

  Command-Line Format	--binlog-cache-size=#
  System Variable	binlog_cache_size
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	32768
  Minimum Value	4096
  Maximum Value (64-bit platforms)	18446744073709547520
  Maximum Value (32-bit platforms)	4294963200
  Unit	bytes
  Block Size	4096
  The size of the cache to hold changes to the binary log during a transaction.
  A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large transactions, you can increase this cache size to get better performance. TheBinlog_cache_use andBinlog_cache_disk_use status variables can be useful for tuning the size of this variable. See Section 5.4.4, “The Binary Log”.
  binlog_cache_size sets the size for the transaction cache only; the size of the statement cache is governed by thebinlog_stmt_cache_size system variable.

• binlog_checksum

  Command-Line Format	--binlog-checksum=type
  System Variable	binlog_checksum
  Scope	Global
  Dynamic	Yes
  Type	String
  Default Value	CRC32
  Valid Values	NONECRC32
  When enabled, this variable causes the source to write a checksum for each event in the binary log.binlog_checksum supports the valuesNONE (disabled) andCRC32. The default isCRC32. You cannot change the value ofbinlog_checksum within a transaction.
  When binlog_checksum is disabled (valueNONE), the server verifies that it is writing only complete events to the binary log by writing and checking the event length (rather than a checksum) for each event.
  Changing the value of this variable causes the binary log to be rotated; checksums are always written to an entire binary log file, and never to only part of one.
  Setting this variable on the source to a value unrecognized by the replica causes the replica to set its ownbinlog_checksum value toNONE, and to stop replication with an error. (Bug #13553750, Bug #61096) If backward compatibility with older replicas is a concern, you may want to set the value explicitly to NONE.

• binlog_direct_non_transactional_updates
  Due to concurrency issues, a replica can become inconsistent when a transaction contains updates to both transactional and nontransactional tables. MySQL tries to preserve causality among these statements by writing nontransactional statements to the transaction cache, which is flushed upon commit. However, problems arise when modifications done to nontransactional tables on behalf of a transaction become immediately visible to other connections because these changes may not be written immediately into the binary log.
  Thebinlog_direct_non_transactional_updates variable offers one possible workaround to this issue. By default, this variable is disabled. Enablingbinlog_direct_non_transactional_updates causes updates to nontransactional tables to be written directly to the binary log, rather than to the transaction cache.
  binlog_direct_non_transactional_updates works only for statements that are replicated using the statement-based binary logging format; that is, it works only when the value ofbinlog_format isSTATEMENT, or whenbinlog_format isMIXED and a given statement is being replicated using the statement-based format. This variable has no effect when the binary log format isROW, or whenbinlog_format is set toMIXED and a given statement is replicated using the row-based format.
  Important
  Before enabling this variable, you must make certain that there are no dependencies between transactional and nontransactional tables; an example of such a dependency would be the statement INSERT INTO myisam_table SELECT * FROM innodb_table. Otherwise, such statements are likely to cause the replica to diverge from the source.
  This variable has no effect when the binary log format isROW or MIXED.
• binlog_error_action

  Command-Line Format	--binlog-error-action[=value]
  System Variable	binlog_error_action
  Scope	Global
  Dynamic	Yes
  Type	Enumeration
  Default Value	ABORT_SERVER
  Valid Values	IGNORE_ERRORABORT_SERVER
  Controls what happens when the server encounters an error such as not being able to write to, flush or synchronize the binary log, which can cause the source's binary log to become inconsistent and replicas to lose synchronization.
  In MySQL 5.7.7 and higher, this variable defaults toABORT_SERVER, which makes the server halt logging and shut down whenever it encounters such an error with the binary log. On restart, recovery proceeds as in the case of an unexpected server halt (seeSection 16.3.2, “Handling an Unexpected Halt of a Replica”).
  When binlog_error_action is set toIGNORE_ERROR, if the server encounters such an error it continues the ongoing transaction, logs the error then halts logging, and continues performing updates. To resume binary logginglog_bin must be enabled again, which requires a server restart. This setting provides backward compatibility with older versions of MySQL.
  In previous releases this variable was namedbinlogging_impossible_mode.

• binlog_format

  Command-Line Format	--binlog-format=format
  System Variable	binlog_format
  Scope	Global, Session
  Dynamic	Yes
  Type	Enumeration
  Default Value	ROW
  Valid Values	MIXEDSTATEMENTROW
  This system variable sets the binary logging format, and can be any one of STATEMENT,ROW, or MIXED. SeeSection 16.2.1, “Replication Formats”. The setting takes effect when binary logging is enabled on the server, which is the case when thelog_bin system variable is set to ON. In MySQL 5.7, binary logging is not enabled by default, and you enable it using the--log-bin option.
  binlog_format can be set at startup or at runtime, except that under some conditions, changing this variable at runtime is not possible or causes replication to fail, as described later.
  Prior to MySQL 5.7.7, the default format wasSTATEMENT. In MySQL 5.7.7 and higher, the default is ROW.Exception: In NDB Cluster, the default is MIXED; statement-based replication is not supported for NDB Cluster.
  Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. SeeSection 5.1.8.1, “System Variable Privileges”.
  The rules governing when changes to this variable take effect and how long the effect lasts are the same as for other MySQL server system variables. For more information, see Section 13.7.4.1, “SET Syntax for Variable Assignment”.
  When MIXED is specified, statement-based replication is used, except for cases where only row-based replication is guaranteed to lead to proper results. For example, this happens when statements contain loadable functions or the UUID() function.
  For details of how stored programs (stored procedures and functions, triggers, and events) are handled when each binary logging format is set, seeSection 23.7, “Stored Program Binary Logging”.
  There are exceptions when you cannot switch the replication format at runtime:

  • From within a stored function or a trigger.
  • If the session is currently in row-based replication mode and has open temporary tables.
  • From within a transaction.
    Trying to switch the format in those cases results in an error.
    Changing the logging format on a replication source server does not cause a replica to change its logging format to match. Switching the replication format while replication is ongoing can cause issues if a replica has binary logging enabled, and the change results in the replica usingSTATEMENT format logging while the source is using ROW or MIXED format logging. A replica is not able to convert binary log entries received in ROW logging format toSTATEMENT format for use in its own binary log, so this situation can cause replication to fail. For more information, seeSection 5.4.4.2, “Setting The Binary Log Format”.
    The binary log format affects the behavior of the following server options:
  • --replicate-do-db
  • --replicate-ignore-db
  • --binlog-do-db
  • --binlog-ignore-db
    These effects are discussed in detail in the descriptions of the individual options.
• binlog_group_commit_sync_delay

  Command-Line Format	--binlog-group-commit-sync-delay=#
  System Variable	binlog_group_commit_sync_delay
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	0
  Minimum Value	0
  Maximum Value	1000000
  Unit	microseconds
  Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. By defaultbinlog_group_commit_sync_delay is set to 0, meaning that there is no delay. Settingbinlog_group_commit_sync_delay to a microsecond delay enables more transactions to be synchronized together to disk at once, reducing the overall time to commit a group of transactions because the larger groups require fewer time units per group.
  When sync_binlog=0 orsync_binlog=1 is set, the delay specified bybinlog_group_commit_sync_delay is applied for every binary log commit group before synchronization (or in the case ofsync_binlog=0, before proceeding). Whensync_binlog is set to a value n greater than 1, the delay is applied after every n binary log commit groups.
  Settingbinlog_group_commit_sync_delay can increase the number of parallel committing transactions on any server that has (or might have after a failover) a replica, and therefore can increase parallel execution on the replicas. To benefit from this effect, the replica servers must haveslave_parallel_type=LOGICAL_CLOCK set, and the effect is more significant whenbinlog_transaction_dependency_tracking=COMMIT_ORDER is also set. It is important to take into account both the source's throughput and the replicas' throughput when you are tuning the setting forbinlog_group_commit_sync_delay.
  Settingbinlog_group_commit_sync_delay can also reduce the number of fsync() calls to the binary log on any server (source or replica) that has a binary log.
  Note that settingbinlog_group_commit_sync_delay increases the latency of transactions on the server, which might affect client applications. Also, on highly concurrent workloads, it is possible for the delay to increase contention and therefore reduce throughput. Typically, the benefits of setting a delay outweigh the drawbacks, but tuning should always be carried out to determine the optimal setting.

• binlog_group_commit_sync_no_delay_count

  Command-Line Format	--binlog-group-commit-sync-no-delay-count=#
  System Variable	binlog_group_commit_sync_no_delay_count
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	0
  Minimum Value	0
  Maximum Value	100000
  The maximum number of transactions to wait for before aborting the current delay as specified bybinlog_group_commit_sync_delay. Ifbinlog_group_commit_sync_delay is set to 0, then this option has no effect.

• binlog_max_flush_queue_time

  Command-Line Format	--binlog-max-flush-queue-time=#
  Deprecated	Yes
  System Variable	binlog_max_flush_queue_time
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	0
  Minimum Value	0
  Maximum Value	100000
  Unit	microseconds
  Formerly, this controlled the time in microseconds to continue reading transactions from the flush queue before proceeding with group commit. In MySQL 5.7, this variable no longer has any effect.
  binlog_max_flush_queue_time is deprecated as of MySQL 5.7.9, and is marked for eventual removal in a future MySQL release.

• binlog_order_commits

  Command-Line Format	--binlog-order-commits[={OFF|ON}]
  System Variable	binlog_order_commits
  Scope	Global
  Dynamic	Yes
  Type	Boolean
  Default Value	ON
  When this variable is enabled on a replication source server (which is the default), transaction commit instructions issued to storage engines are serialized on a single thread, so that transactions are always committed in the same order as they are written to the binary log. Disabling this variable permits transaction commit instructions to be issued using multiple threads. Used in combination with binary log group commit, this prevents the commit rate of a single transaction being a bottleneck to throughput, and might therefore produce a performance improvement.
  Transactions are written to the binary log at the point when all the storage engines involved have confirmed that the transaction is prepared to commit. The binary log group commit logic then commits a group of transactions after their binary log write has taken place. Whenbinlog_order_commits is disabled, because multiple threads are used for this process, transactions in a commit group might be committed in a different order from their order in the binary log. (Transactions from a single client always commit in chronological order.) In many cases this does not matter, as operations carried out in separate transactions should produce consistent results, and if that is not the case, a single transaction ought to be used instead.
  If you want to ensure that the transaction history on the source and on a multithreaded replica remains identical, setslave_preserve_commit_order=1 on the replica.

• binlog_row_image

  Command-Line Format	--binlog-row-image=image_type
  System Variable	binlog_row_image
  Scope	Global, Session
  Dynamic	Yes
  Type	Enumeration
  Default Value	full
  Valid Values	full (Log all columns)minimal (Log only changed columns, and columns needed to identify rows)noblob (Log all columns, except for unneeded BLOB and TEXT columns)
  For MySQL row-based replication, this variable determines how row images are written to the binary log.
  In MySQL row-based replication, each row change event contains two images, a “before” image whose columns are matched against when searching for the row to be updated, and an “after” image containing the changes. Normally, MySQL logs full rows (that is, all columns) for both the before and after images. However, it is not strictly necessary to include every column in both images, and we can often save disk, memory, and network usage by logging only those columns which are actually required.
  Note
  When deleting a row, only the before image is logged, since there are no changed values to propagate following the deletion. When inserting a row, only the after image is logged, since there is no existing row to be matched. Only when updating a row are both the before and after images required, and both written to the binary log.
  For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without anyNULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.
  You can cause the server to log full or minimal rows using the binlog_row_image system variable. This variable actually takes one of three possible values, as shown in the following list:

  • full: Log all columns in both the before image and the after image.
  • minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image where a value was specified by the SQL statement, or generated by auto-increment.
  • noblob: Log all columns (same asfull), except forBLOB andTEXT columns that are not required to identify rows, or that have not changed.
    Note
    This variable is not supported by NDB Cluster; setting it has no effect on the logging ofNDB tables.
    The default value is full.
    When using minimal ornoblob, deletes and updates are guaranteed to work correctly for a given table if and only if the following conditions are true for both the source and destination tables:
  • All columns must be present and in the same order; each column must use the same data type as its counterpart in the other table.
  • The tables must have identical primary key definitions.
    (In other words, the tables must be identical with the possible exception of indexes that are not part of the tables' primary keys.)
    If these conditions are not met, it is possible that the primary key column values in the destination table may prove insufficient to provide a unique match for a delete or update. In this event, no warning or error is issued; the source and replica silently diverge, thus breaking consistency.
    Setting this variable has no effect when the binary logging format is STATEMENT. Whenbinlog_format isMIXED, the setting forbinlog_row_image is applied to changes that are logged using row-based format, but this setting has no effect on changes logged as statements.
    Setting binlog_row_image on either the global or session level does not cause an implicit commit; this means that this variable can be changed while a transaction is in progress without affecting the transaction.
• binlog_rows_query_log_events

  Command-Line Format	--binlog-rows-query-log-events[={OFF|ON}]
  System Variable	binlog_rows_query_log_events
  Scope	Global, Session
  Dynamic	Yes
  Type	Boolean
  Default Value	OFF
  This system variable affects row-based logging only. When enabled, it causes the server to write informational log events such as row query log events into its binary log. This information can be used for debugging and related purposes, such as obtaining the original query issued on the source when it cannot be reconstructed from the row updates.
  These informational events are normally ignored by MySQL programs reading the binary log and so cause no issues when replicating or restoring from backup. To view them, increase the verbosity level by using mysqlbinlog's--verbose option twice, either as -vv or --verbose --verbose.

• binlog_stmt_cache_size

  Command-Line Format	--binlog-stmt-cache-size=#
  System Variable	binlog_stmt_cache_size
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	32768
  Minimum Value	4096
  Maximum Value (64-bit platforms)	18446744073709547520
  Maximum Value (32-bit platforms)	4294963200
  Unit	bytes
  Block Size	4096
  This variable determines the size of the cache for the binary log to hold nontransactional statements issued during a transaction.
  Separate binary log transaction and statement caches are allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large nontransactional statements during transactions, you can increase this cache size to get better performance. TheBinlog_stmt_cache_use andBinlog_stmt_cache_disk_use status variables can be useful for tuning the size of this variable. See Section 5.4.4, “The Binary Log”.
  The binlog_cache_size system variable sets the size for the transaction cache.

• binlog_transaction_dependency_tracking

  Command-Line Format	--binlog-transaction-dependency-tracking=value
  Introduced	5.7.22
  System Variable	binlog_transaction_dependency_tracking
  Scope	Global
  Dynamic	Yes
  Type	Enumeration
  Default Value	COMMIT_ORDER
  Valid Values	COMMIT_ORDERWRITESETWRITESET_SESSION
  The source of dependency information that the source uses to determine which transactions can be executed in parallel by the replica's multithreaded applier. This variable can take one of the three values described in the following list:

  • COMMIT_ORDER: Dependency information is generated from the source's commit timestamps. This is the default.
  • WRITESET: Dependency information is generated from the source's write set, and any transactions which write different tuples can be parallelized.
  • WRITESET_SESSION: Dependency information is generated from the source's write set, and any transactions that write different tuples can be parallelized, with the exception that no two updates from the same session can be reordered.
    In WRITESET orWRITESET_SESSION mode, transactions can commit out of order unless you also setslave_preserve_commit_order=1.
    For some transactions, the WRITESET andWRITESET_SESSION modes cannot improve on the results that would have been returned inCOMMIT_ORDER mode. This is the case for transactions that have empty or partial write sets, transactions that update tables without primary or unique keys, and transactions that update parent tables in a foreign key relationship. In these situations, the source uses COMMIT_ORDER mode to generate the dependency information instead.
    The value of this variable cannot be set to anything other than COMMIT_ORDER iftransaction_write_set_extraction is OFF. You should also note that the value of transaction_write_set_extraction cannot be changed if the current value ofbinlog_transaction_dependency_tracking isWRITESET orWRITESET_SESSION. If you change the value, the new value does not take effect on replicas until after the replica has been stopped and restarted withSTOP SLAVE andSTART SLAVE statements.
    The number of row hashes to be kept and checked for the latest transaction to have changed a given row is determined by the value ofbinlog_transaction_dependency_history_size.
• binlog_transaction_dependency_history_size

  Command-Line Format	--binlog-transaction-dependency-history-size=#
  Introduced	5.7.22
  System Variable	binlog_transaction_dependency_history_size
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	25000
  Minimum Value	1
  Maximum Value	1000000
  Sets an upper limit on the number of row hashes which are kept in memory and used for looking up the transaction that last modified a given row. Once this number of hashes has been reached, the history is purged.

• expire_logs_days

  Command-Line Format	--expire-logs-days=#
  System Variable	expire_logs_days
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	0
  Minimum Value	0
  Maximum Value	99
  Unit	days
  The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.4, “MySQL Server Logs”.
  To remove binary log files manually, use thePURGE BINARY LOGS statement. See Section 13.4.1.1, “PURGE BINARY LOGS Statement”.

• log_bin

  System Variable	log_bin
  Scope	Global
  Dynamic	No
  Type	Boolean
  Whether the binary log is enabled. If the--log-bin option is used, then the value of this variable is ON; otherwise it is OFF. This variable reports only on the status of binary logging (enabled or disabled); it does not actually report the value to which--log-bin is set.
  See Section 5.4.4, “The Binary Log”.

• log_bin_basename
  Holds the base name and path for the binary log files, which can be set with the --log-bin server option. The maximum variable length is 256. In MySQL 5.7, the default base name is the name of the host machine with the suffix -bin. The default location is the data directory.
• log_bin_index

  Command-Line Format	--log-bin-index=file_name
  System Variable	log_bin_index
  Scope	Global
  Dynamic	No
  Type	File name
  Holds the base name and path for the binary log index file, which can be set with the--log-bin-index server option. The maximum variable length is 256.

• log_bin_trust_function_creators

  Command-Line Format	--log-bin-trust-function-creators[={OFF|ON}]
  System Variable	log_bin_trust_function_creators
  Scope	Global
  Dynamic	Yes
  Type	Boolean
  Default Value	OFF
  This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that causes unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with theDETERMINISTIC characteristic, or with theREADS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 23.7, “Stored Program Binary Logging”.

• log_bin_use_v1_row_events

  Command-Line Format	--log-bin-use-v1-row-events[={OFF|ON}]
  System Variable	log_bin_use_v1_row_events
  Scope	Global
  Dynamic	Yes
  Type	Boolean
  Default Value	OFF
  Whether Version 2 binary logging is in use. If this variable is 0 (disabled, the default), Version 2 binary log events are in use. If this variable is 1 (enabled), the server writes the binary log using Version 1 logging events (the only version of binary log events used in previous releases), and thus produces a binary log that can be read by older replicas.
  MySQL 5.7 uses Version 2 binary log row events by default. However, Version 2 events cannot be read by MySQL Server releases prior to MySQL 5.6.6. Enablinglog_bin_use_v1_row_events causes mysqld to write the binary log using Version 1 logging events.
  This variable is read-only at runtime. To switch between Version 1 and Version 2 binary event binary logging, it is necessary to setlog_bin_use_v1_row_events at server startup.
  Other than when performing upgrades of NDB Cluster Replication,log_bin_use_v1_row_events is chiefly of interest when setting up replication conflict detection and resolution usingNDB$EPOCH_TRANS() as the conflict detection function, which requires Version 2 binary log row events. Thus, this variable and--ndb-log-transaction-id are not compatible.
  Note
  MySQL NDB Cluster 7.5 uses Version 2 binary log row events by default. You should keep this mind when planning upgrades or downgrades, and for setups using NDB Cluster Replication.
  For more information, seeSection 21.7.11, “NDB Cluster Replication Conflict Resolution”.

• log_builtin_as_identified_by_password
  This variable affects binary logging of user-management statements. When enabled, the variable has the following effects:
  • Binary logging for CREATE USER statements involving built-in authentication plugins rewrites the statements to include an IDENTIFIED BY PASSWORD clause.
  • SET PASSWORD statements are logged as SET PASSWORD statements, rather than being rewritten to ALTER USER statements.
  • SET PASSWORD statements are changed to log the hash of the password instead of the supplied cleartext (unencrypted) password.
    Enabling this variable ensures better compatibility for cross-version replication with 5.6 and pre-5.7.6 replicas, and for applications that expect this syntax in the binary log.
• log_slave_updates

  Command-Line Format	--log-slave-updates[={OFF|ON}]
  System Variable	log_slave_updates
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF
  Whether updates received by a replica server from a source server should be logged to the replica's own binary log.
  Normally, a replica does not log to its own binary log any updates that are received from a source server. Enabling this variable causes the replica to write the updates performed by its replication SQL thread to its own binary log. For this option to have any effect, the replica must also be started with the--log-bin option to enable binary logging. See Section 16.1.6, “Replication and Binary Logging Options and Variables”.
  log_slave_updates is enabled when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

A -> B -> C  

Here, A serves as the source for the replica B, and B serves as the source for the replica C. For this to work, B must be both a source_and_ a replica. You must start bothA and B with--log-bin to enable binary logging, and B withlog_slave_updates enabled so that updates received from A are logged by B to its binary log.

• log_statements_unsafe_for_binlog

  Command-Line Format	--log-statements-unsafe-for-binlog[={OFF|ON}]
  Introduced	5.7.11
  System Variable	log_statements_unsafe_for_binlog
  Scope	Global
  Dynamic	Yes
  Type	Boolean
  Default Value	ON
  If error 1592 is encountered, controls whether the generated warnings are added to the error log or not.

• master_verify_checksum

  Command-Line Format	--master-verify-checksum[={OFF|ON}]
  System Variable	master_verify_checksum
  Scope	Global
  Dynamic	Yes
  Type	Boolean
  Default Value	OFF
  Enabling this variable causes the source to verify events read from the binary log by examining checksums, and to stop with an error in the event of a mismatch.master_verify_checksum is disabled by default; in this case, the source uses the event length from the binary log to verify events, so that only complete events are read from the binary log.

• max_binlog_cache_size

  Command-Line Format	--max-binlog-cache-size=#
  System Variable	max_binlog_cache_size
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value (64-bit platforms)	18446744073709547520
  Default Value (32-bit platforms)	4294967295
  Minimum Value	4096
  Maximum Value (64-bit platforms)	18446744073709547520
  Maximum Value (32-bit platforms)	4294967295
  Unit	bytes
  Block Size	4096
  If a transaction requires more than this many bytes, the server generates a Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage error. Whengtid_mode is notON, the maximum recommended value is 4GB, due to the fact that, in this case, MySQL cannot work with binary log positions greater than 4GB; whengtid_mode is ON, this limitation does not apply, and the server can work with binary log positions of arbitrary size.
  If, because gtid_mode is not ON, or for some other reason, you need to guarantee that the binary log does not exceed a given size maxsize, you should set this variable according to the formula shown here:

max_binlog_cache_size <  
  (((maxsize - max_binlog_size) / max_connections) - 1000) / 1.2  

This calculation takes into account the following conditions:

• The server writes to the binary log as long as the size before it begins to write is less thanmax_binlog_size.
• The server does not write single transactions, but rather groups of transactions. The maximum possible number of transactions in a group is equal tomax_connections.
• The server writes data that is not included in the cache. This includes a 4-byte checksum for each event; while this adds less than 20% to the transaction size, this amount is non-negible. In addition, the server writes a Gtid_log_event for each transaction; each of these events can add another 1 KB to what is written to the binary log.
  max_binlog_cache_size sets the size for the transaction cache only; the upper limit for the statement cache is governed by themax_binlog_stmt_cache_size system variable.
  The visibility to sessions ofmax_binlog_cache_size matches that of thebinlog_cache_size system variable; in other words, changing its value affects only new sessions that are started after the value is changed.
• max_binlog_size

  Command-Line Format	--max-binlog-size=#
  System Variable	max_binlog_size
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	1073741824
  Minimum Value	4096
  Maximum Value	1073741824
  Unit	bytes
  Block Size	4096
  If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB.
  A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger thanmax_binlog_size.
  If max_relay_log_size is 0, the value ofmax_binlog_size applies to relay logs as well.

• max_binlog_stmt_cache_size

  Command-Line Format	--max-binlog-stmt-cache-size=#
  System Variable	max_binlog_stmt_cache_size
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	18446744073709547520
  Minimum Value	4096
  Maximum Value	18446744073709547520
  Unit	bytes
  Block Size	4096
  If nontransactional statements within a transaction require more than this many bytes of memory, the server generates an error. The minimum value is 4096. The maximum and default values are 4GB on 32-bit platforms and 16EB (exabytes) on 64-bit platforms.
  max_binlog_stmt_cache_size sets the size for the statement cache only; the upper limit for the transaction cache is governed exclusively by themax_binlog_cache_size system variable.

• sql_log_bin

  System Variable	sql_log_bin
  Scope	Session
  Dynamic	Yes
  Type	Boolean
  Default Value	ON
  This variable controls whether logging to the binary log is enabled for the current session (assuming that the binary log itself is enabled). The default value isON. To disable or enable binary logging for the current session, set the sessionsql_log_bin variable toOFF or ON.
  Set this variable to OFF for a session to temporarily disable binary logging while making changes to the source you do not want replicated to the replica.
  Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. SeeSection 5.1.8.1, “System Variable Privileges”.
  It is not possible to set the session value ofsql_log_bin within a transaction or subquery.
  Setting this variable to OFF prevents GTIDs from being assigned to transactions in the binary log. If you are using GTIDs for replication, this means that even when binary logging is later enabled again, the GTIDs written into the log from this point do not account for any transactions that occurred in the meantime, so in effect those transactions are lost.
  The global sql_log_bin variable is read only and cannot be modified. The global scope is deprecated; expect it to be removed in a future MySQL release.

• sync_binlog

  Command-Line Format	--sync-binlog=#
  System Variable	sync_binlog
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	1
  Minimum Value	0
  Maximum Value	4294967295
  Controls how often the MySQL server synchronizes the binary log to disk.

  • sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.
  • sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.
  • sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to disk afterN binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss.
    For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:
  • sync_binlog=1.
  • innodb_flush_log_at_trx_commit=1.
    Caution
    Many operating systems and some disk hardware fool the flush-to-disk operation. They may tellmysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.
• transaction_write_set_extraction

  Command-Line Format	--transaction-write-set-extraction[=value]
  System Variable	transaction_write_set_extraction
  Scope	Global, Session
  Dynamic	Yes
  Type	Enumeration
  Default Value	OFF
  Valid Values (≥ 5.7.14)	OFFMURMUR32XXHASH64
  Valid Values (≤ 5.7.13)	OFFMURMUR32
  Defines the algorithm used to generate a hash identifying the writes associated with a transaction. If you are using Group Replication, the hash value is used for distributed conflict detection and handling. On 64-bit systems running Group Replication, we recommend setting this toXXHASH64 in order to avoid unnecessary hash collisions which result in certification failures and the roll back of user transactions. SeeSection 17.3.1, “Group Replication Requirements”.binlog_format must be set to ROW to change the value of this variable. If you change the value, the new value does not take effect on replicas until after the replica has been stopped and restarted with STOP SLAVE and START SLAVE statements.