Rechercher dans le manuel MySQL
17.1.6.4 Binary Logging Options and Variables
You can use the mysqld options and system variables that are described in this section to affect the operation of the binary log as well as to control which statements are written to the binary log. For additional information about the binary log, see Section 5.4.4, “The Binary Log”. For additional information about using MySQL server options and system variables, see Section 5.1.7, “Server Command Options”, and Section 5.1.8, “Server System Variables”.
Startup Options Used with Binary Logging
The following list describes startup options for enabling and configuring the binary log. System variables used with binary logging are discussed later in this section.
-
Property Value Command-Line Format --binlog-row-event-max-size=#
System Variable (>= 8.0.14) binlog_row_event_max_size
Scope (>= 8.0.14) Global Dynamic (>= 8.0.14) No SET_VAR
Hint Applies (>= 8.0.14)No Type Integer Default Value 8192
Minimum Value 256
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
When row-based binary logging is used, this setting is a soft limit on the maximum size of a row-based binary log event, in bytes. Where possible, rows stored in the binary log are grouped into events with a size not exceeding the value of this setting. If an event cannot be split, the maximum size can be exceeded. The value must be (or else gets rounded down to) a multiple of 256. The default is 8192 bytes.
-
Property Value Command-Line Format --log-bin=file_name
Type File name Specifies the base name to use for binary log files. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension. The
--log-bin
option value is the base name for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the base name.If you do not supply the
--log-bin
option, MySQL usesbinlog
as the default base name for the binary log files. For compatibility with earlier releases, if you supply the--log-bin
option with no string or with an empty string, the base name defaults to
, using the name of the host machine.host_name
-binThe default location for binary log files is the data directory. You can use the
--log-bin
option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory. When the server reads an entry from the binary log index file, which tracks the binary log files that have been used, it checks whether the entry contains a relative path. If it does, the relative part of the path is replaced with the absolute path set using the--log-bin
option. An absolute path recorded in the binary log index file remains unchanged; in such a case, the index file must be edited manually to enable a new path or paths to be used. The binary log file base name and any specified path are available as thelog_bin_basename
system variable.In earlier MySQL versions, binary logging was disabled by default, and was enabled if you specified the
--log-bin
option. From MySQL 8.0, binary logging is enabled by default, whether or not you specify the--log-bin
option. The exception is if you use mysqld to initialize the data directory manually by invoking it with the--initialize
or--initialize-insecure
option, when binary logging is disabled by default. It is possible to enable binary logging in this case by specifying the--log-bin
option. When binary logging is enabled, thelog_bin
system variable, which shows the status of binary logging on the server, is set to ON.To disable binary logging, you can specify the
--skip-log-bin
or--disable-log-bin
option at startup. If either of these options is specified and--log-bin
is also specified, the option specified later takes precedence. When binary logging is disabled, thelog_bin
system variable is set to OFF.When GTIDs are in use on the server, if you disable binary logging when restarting the server after an abnormal shutdown, some GTIDs are likely to be lost, causing replication to fail. In a normal shutdown, the set of GTIDs from the current binary log file is saved in the
mysql.gtid_executed
table. Following an abnormal shutdown where this did not happen, during recovery the GTIDs are added to the table from the binary log file, provided that binary logging is still enabled. If binary logging is disabled for the server restart, the server cannot access the binary log file to recover the GTIDs, so replication cannot be started. Binary logging can be disabled safely after a normal shutdown.The
--log-slave-updates
and--slave-preserve-commit-order
options require binary logging. If you disable binary logging, either omit these options, or specify--skip-log-slave-updates
and--skip-slave-preserve-commit-order
. MySQL disables these options by default when--skip-log-bin
or--disable-log-bin
is specified. If you specify--log-slave-updates
or--slave-preserve-commit-order
together with--skip-log-bin
or--disable-log-bin
, a warning or error message is issued.In MySQL 5.7, a server ID had to be specified when binary logging was enabled, or the server would not start. In MySQL 8.0, the
server_id
system variable is set to 1 by default. The server can now be started with this default server ID when binary logging is enabled, but an informational message is issued if you do not specify a server ID explicitly using the--server-id
option. For servers that are used in a replication topology, you must specify a unique nonzero server ID for each server.For information on the format and management of the binary log, see Section 5.4.4, “The Binary Log”.
-
Property Value Command-Line Format --log-bin-index=file_name
System Variable log_bin_index
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type File name The name for the binary log index file, which contains the names of the binary log files. By default, it has the same location and base name as the value specified for the binary log files using the
--log-bin
option, plus the extension.index
. If you do not specify--log-bin
, the default binary log index file name isbinlog.index
. If you specify--log-bin
option with no string or an empty string, the default binary log index file name is
, using the name of the host machine.host_name
-bin.indexFor information on the format and management of the binary log, see Section 5.4.4, “The Binary Log”.
--log-bin-trust-function-creators[={0|1}]
Property Value Command-Line Format --log-bin-trust-function-creators[={OFF|ON}]
System Variable log_bin_trust_function_creators
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
This option sets the corresponding
log_bin_trust_function_creators
system variable. If no argument is given, the option sets the variable to 1.log_bin_trust_function_creators
affects how MySQL enforces restrictions on stored function and trigger creation. See Section 24.7, “Stored Program Binary Logging”.--log-bin-use-v1-row-events[={OFF|ON}]
Property Value Command-Line Format --log-bin-use-v1-row-events[={OFF|ON}]
Deprecated 8.0.18 System Variable log_bin_use_v1_row_events
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
This option is deprecated. Setting the option to
ON
enabled row-based replication with slaves running MySQL Server 5.5 and earlier by writing the binary log using Version 1 binary log row events, instead of Version 2 binary log row events which are the default from MySQL 5.6.
Statement selection options. The options in the following list affect which statements are written to the binary log, and thus sent by a replication master server to its slaves. There are also options for slave servers that control which statements received from the master should be executed or ignored. For details, see Section 17.1.6.3, “Replication Slave Options and Variables”.
-
Property Value Command-Line Format --binlog-do-db=name
Type String This option affects binary logging in a manner similar to the way that
--replicate-do-db
affects replication.The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of
--replicate-do-db
depend on whether statement-based or row-based replication is in use. You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value ofbinlog_format
. For example, DDL statements such asCREATE TABLE
andALTER TABLE
are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for--binlog-do-db
always apply in determining whether or not the statement is logged.Statement-based logging. Only those statements are written to the binary log where the default database (that is, the one selected by
USE
) isdb_name
. To specify more than one database, use this option multiple times, once for each database; however, doing so does not cause cross-database statements such asUPDATE
to be logged while a different database (or no database) is selected.some_db.some_table
SET foo='bar'WarningTo specify multiple databases you must use multiple instances of this option. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list.
An example of what does not work as you might expect when using statement-based logging: If the server is started with
--binlog-do-db=sales
and you issue the following statements, theUPDATE
statement is not logged:The main reason for this “just check the default database” behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table
DELETE
statements or multiple-tableUPDATE
statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.Another case which may not be self-evident occurs when a given database is replicated even though it was not specified when setting the option. If the server is started with
--binlog-do-db=sales
, the followingUPDATE
statement is logged even thoughprices
was not included when setting--binlog-do-db
:Because
sales
is the default database when theUPDATE
statement is issued, theUPDATE
is logged.Row-based logging. Logging is restricted to database
db_name
. Only changes to tables belonging todb_name
are logged; the default database has no effect on this. Suppose that the server is started with--binlog-do-db=sales
and row-based logging is in effect, and then the following statements are executed:The changes to the
february
table in thesales
database are logged in accordance with theUPDATE
statement; this occurs whether or not theUSE
statement was issued. However, when using the row-based logging format and--binlog-do-db=sales
, changes made by the followingUPDATE
are not logged:Even if the
USE prices
statement were changed toUSE sales
, theUPDATE
statement's effects would still not be written to the binary log.Another important difference in
--binlog-do-db
handling for statement-based logging as opposed to the row-based logging occurs with regard to statements that refer to multiple databases. Suppose that the server is started with--binlog-do-db=db1
, and the following statements are executed:If you are using statement-based logging, the updates to both tables are written to the binary log. However, when using the row-based format, only the changes to
table1
are logged;table2
is in a different database, so it is not changed by theUPDATE
. Now suppose that, instead of theUSE db1
statement, aUSE db4
statement had been used:In this case, the
UPDATE
statement is not written to the binary log when using statement-based logging. However, when using row-based logging, the change totable1
is logged, but not that totable2
—in other words, only changes to tables in the database named by--binlog-do-db
are logged, and the choice of default database has no effect on this behavior. -
Property Value Command-Line Format --binlog-ignore-db=name
Type String This option affects binary logging in a manner similar to the way that
--replicate-ignore-db
affects replication.The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of
--replicate-ignore-db
depend on whether statement-based or row-based replication is in use. You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value ofbinlog_format
. For example, DDL statements such asCREATE TABLE
andALTER TABLE
are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for--binlog-ignore-db
always apply in determining whether or not the statement is logged.Statement-based logging. Tells the server to not log any statement where the default database (that is, the one selected by
USE
) isdb_name
.When there is no default database, no
--binlog-ignore-db
options are applied, and such statements are always logged. (Bug #11829838, Bug #60188)Row-based format. Tells the server not to log updates to any tables in the database
db_name
. The current database has no effect.When using statement-based logging, the following example does not work as you might expect. Suppose that the server is started with
--binlog-ignore-db=sales
and you issue the following statements:The
UPDATE
statement is logged in such a case because--binlog-ignore-db
applies only to the default database (determined by theUSE
statement). Because thesales
database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based logging, theUPDATE
statement's effects are not written to the binary log, which means that no changes to thesales.january
table are logged; in this instance,--binlog-ignore-db=sales
causes all changes made to tables in the master's copy of thesales
database to be ignored for purposes of binary logging.To specify more than one database to ignore, use this option multiple times, once for each database. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list.
You should not use this option if you are using cross-database updates and you do not want these updates to be logged.
Checksum options. MySQL supports reading and writing of binary log checksums. These are enabled using the two options listed here:
--binlog-checksum={NONE|CRC32}
Property Value Command-Line Format --binlog-checksum=type
Type String Default Value CRC32
Valid Values NONE
CRC32
Enabling this option causes the master to write checksums for events written to the binary log. Set to
NONE
to disable, or the name of the algorithm to be used for generating checksums; currently, only CRC32 checksums are supported, and CRC32 is the default. You cannot change the setting for this option within a transaction.--master-verify-checksum={0|1}
Property Value Command-Line Format --master-verify-checksum[={OFF|ON}]
Type Boolean Default Value OFF
Enabling this option causes the master to verify events from the binary log using checksums, and to stop with an error in the event of a mismatch. Disabled by default.
To control reading of checksums by the slave (from the relay)
log, use the
--slave-sql-verify-checksum
option.
Testing and debugging options. The following binary log options are used in replication testing and debugging. They are not intended for use in normal operations.
-
Property Value Command-Line Format --max-binlog-dump-events=#
Type Integer Default Value 0
This option is used internally by the MySQL test suite for replication testing and debugging.
-
Property Value Command-Line Format --sporadic-binlog-dump-fail[={OFF|ON}]
Type Boolean Default Value OFF
This option is used internally by the MySQL test suite for replication testing and debugging.
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 using
SET
.
Server options used to control binary logging are listed earlier
in this section.
-
Property Value Command-Line Format --binlog-cache-size=#
System Variable binlog_cache_size
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 32768
Minimum Value 4096
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
The size of the memory buffer to hold changes to the binary log during a transaction. When binary logging is enabled on the server (with the
log_bin
system variable set to ON), a binary log cache is allocated for each client if the server supports any transactional storage engines. If the data for the transaction exceeds the space in the memory buffer, the excess data is stored in a temporary file. When binary log encryption is active on the server, the memory buffer is not encrypted, but (from MySQL 8.0.17) any temporary file used to hold the binary log cache is encrypted. After each transaction is committed, the binary log cache is reset by clearing the memory buffer and truncating the temporary file if used.If you often use large transactions, you can increase this cache size to get better performance by reducing or eliminating the need to write to temporary files. The
Binlog_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. -
Property Value Command-Line Format --binlog-checksum=name
System Variable binlog_checksum
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value CRC32
Valid Values NONE
CRC32
When enabled, this variable causes the master 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 master to a value unrecognized by the slave causes the slave to set its own
binlog_checksum
value toNONE
, and to stop replication with an error. (Bug #13553750, Bug #61096) If backward compatibility with older slaves is a concern, you may want to set the value explicitly toNONE
. binlog_direct_non_transactional_updates
Property Value Command-Line Format --binlog-direct-non-transactional-updates[={OFF|ON}]
System Variable binlog_direct_non_transactional_updates
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Due to concurrency issues, a slave 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.
The
binlog_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.As of MySQL 8.0.14, setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
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.ImportantBefore 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 slave to diverge from the master.This variable has no effect when the binary log format is
ROW
orMIXED
.-
Property Value Command-Line Format --binlog-encryption[={OFF|ON}]
Introduced 8.0.14 System Variable binlog_encryption
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Enables encryption for binary log files and relay log files on this server.
OFF
is the default.ON
sets encryption on for binary log files and relay log files. Binary logging does not need to be enabled on the server to enable encryption, so you can encrypt the relay log files on a slave that has no binary log. To use encryption, a keyring plugin must be installed and configured to supply MySQL Server's keyring service. For instructions to do this, see Section 6.4.4, “The MySQL Keyring”. Any supported keyring plugin can be used to store binary log encryption keys.When you first start the server with binary log encryption enabled, a new binary log encryption key is generated before the binary log and relay logs are initialized. This key is used to encrypt a file password for each binary log file (if the server has binary logging enabled) and relay log file (if the server has replication channels), and further keys generated from the file passwords are used to encrypt the data in the files. Relay log files are encrypted for all channels, including Group Replication applier channels and new channels that are created after encryption is activated. The binary log index file and relay log index file are never encrypted.
If you activate encryption while the server is running, a new binary log encryption key is generated at that time. The exception is if encryption was active previously on the server and was then disabled, in which case the binary log encryption key that was in use before is used again. The binary log file and relay log files are rotated immediately, and file passwords for the new files and all subsequent binary log files and relay log files are encrypted using this binary log encryption key. Existing binary log files and relay log files still present on the server are not automatically encrypted, but you can purge them if they are no longer needed.
If you deactivate encryption by changing the
binlog_encryption
system variable toOFF
, the binary log file and relay log files are rotated immediately and all subsequent logging is unencrypted. Previously encrypted files are not automatically decrypted, but the server is still able to read them.SUPER
privileges or theBINLOG_ENCRYPTION_ADMIN
privilege are required to activate or deactivate encryption while the server is running. Group Replication applier channels are not included in the relay log rotation request, so unencrypted logging for these channels does not start until their logs are rotated in normal use.For more information on binary log file and relay log file encryption, see Section 17.3.10, “Encrypting Binary Log Files and Relay Log Files”.
-
Property Value Command-Line Format --binlog-error-action[=value]
System Variable binlog_error_action
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value ABORT_SERVER
Valid Values IGNORE_ERROR
ABORT_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 master's binary log to become inconsistent and replication slaves to lose synchronization.
This variable defaults to
ABORT_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 (see Section 17.3.2, “Handling an Unexpected Halt of a Replication Slave”).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. -
Property Value Command-Line Format --binlog-expire-logs-seconds=#
Introduced 8.0.1 System Variable binlog_expire_logs_seconds
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value (>= 8.0.11) 2592000
Default Value (<= 8.0.4) 0
Minimum Value 0
Maximum Value 4294967295
Sets the binary log expiration period in seconds. After their expiration period ends, binary log files can be automatically removed. Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.4, “MySQL Server Logs”.
The default binary log expiration period is 2592000 seconds, which equals 30 days (30*24*60*60 seconds). The default applies if neither
binlog_expire_logs_seconds
nor the deprecated system variableexpire_logs_days
has a value set at startup. If a non-zero value for one of the variablesbinlog_expire_logs_seconds
orexpire_logs_days
is set at startup, this value is used as the binary log expiration period. If a non-zero value for both of those variables is set at startup, the value forbinlog_expire_logs_seconds
is used as the binary log expiration period, and the value forexpire_logs_days
is ignored with a warning message.To disable automatic purging of the binary log, specify a value of 0 explicitly for
binlog_expire_logs_seconds
, and do not specify a value forexpire_logs_days
. For compatibility with earlier releases, automatic purging is also disabled if you specify a value of 0 explicitly forexpire_logs_days
and do not specify a value forbinlog_expire_logs_seconds
. In that case, the default forbinlog_expire_logs_seconds
is not applied.To remove binary log files manually, use the
PURGE BINARY LOGS
statement. See Section 13.4.1.1, “PURGE BINARY LOGS Syntax”. -
Property Value Command-Line Format --binlog-format=format
System Variable binlog_format
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value ROW
Valid Values ROW
STATEMENT
MIXED
This variable sets the binary logging format, and can be any one of
STATEMENT
,ROW
, orMIXED
. See Section 17.2.1, “Replication Formats”.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.The default is
ROW
. Exception: In NDB Cluster, the default isMIXED
; 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. See Section 5.1.9.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.5.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 user-defined functions (UDF) or theUUID()
function.For details of how stored programs (stored procedures and functions, triggers, and events) are handled when each binary logging format is set, see Section 24.7, “Stored Program Binary Logging”.
There are exceptions when you cannot switch the replication format at runtime:
The replication format cannot be changed from within a stored function or a trigger.
If a session has open temporary tables, the replication format cannot be changed for the session (
SET @@SESSION.binlog_format
).If any replication channel has open temporary tables, the replication format cannot be changed globally (
SET @@GLOBAL.binlog_format
orSET @@PERSIST.binlog_format
).If any replication channel applier thread is currently running, the replication format cannot be changed globally (
SET @@GLOBAL.binlog_format
orSET @@PERSIST.binlog_format
).
Trying to switch the replication format in any of these cases (or attempting to set the current replication format) results in an error. You can, however, use
PERSIST_ONLY
(SET @@PERSIST_ONLY.binlog_format
) to change the replication format at any time, because this action does not modify the runtime global system variable value, and takes effect only after a server restart.Switching the replication format at runtime is not recommended when any temporary tables exist, because temporary tables are logged only when using statement-based replication, whereas with row-based replication and mixed replication, they are not logged.
Changing the logging format on a replication master does not cause a replication slave to change its logging format to match. Switching the replication format while replication is ongoing can cause issues if a replication slave has binary logging enabled, and the change results in the slave using
STATEMENT
format logging while the master is usingROW
orMIXED
format logging. A replication slave is not able to convert binary log entries received inROW
logging format toSTATEMENT
format for use in its own binary log, so this situation can cause replication to fail. For more information, see Section 5.4.4.2, “Setting The Binary Log Format”.The binary log format affects the behavior of the following server options:
These effects are discussed in detail in the descriptions of the individual options.
binlog_group_commit_sync_delay
Property Value Command-Line Format --binlog-group-commit-sync-delay=#
System Variable binlog_group_commit_sync_delay
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 0
Minimum Value 0
Maximum Value 1000000
Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. By default
binlog_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.Setting
binlog_group_commit_sync_delay
can increase the number of parallel committing transactions on any server that has (or might have after a failover) a replication slave, and therefore can increase parallel execution on the replication slaves. To benefit from this effect, the slave 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 master's throughput and the slaves' throughput when you are tuning the setting forbinlog_group_commit_sync_delay
.Setting
binlog_group_commit_sync_delay
can also reduce the number offsync()
calls to the binary log on any server (master or slave) that has a binary log.Note that setting
binlog_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
Property Value Command-Line Format --binlog-group-commit-sync-no-delay-count=#
System Variable binlog_group_commit_sync_no_delay_count
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 0
Minimum Value 0
Maximum Value 1000000
The maximum number of transactions to wait for before aborting the current delay as specified by
binlog_group_commit_sync_delay
. Ifbinlog_group_commit_sync_delay
is set to 0, then this option has no effect.-
Property Value Command-Line Format --binlog-max-flush-queue-time=#
Deprecated Yes System Variable binlog_max_flush_queue_time
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 0
Minimum Value 0
Maximum Value 100000
binlog_max_flush_queue_time
is deprecated, and is marked for eventual removal in a future MySQL release. Formerly, this system variable controlled the time in microseconds to continue reading transactions from the flush queue before proceeding with group commit. It no longer has any effect. -
Property Value Command-Line Format --binlog-order-commits[={OFF|ON}]
System Variable binlog_order_commits
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value ON
When this variable is enabled on a replication master (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. When
binlog_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 master and on a multithreaded replication slave remains identical, set
slave_preserve_commit_order=1
on the replication slave. binlog_rotate_encryption_master_key_at_startup
Property Value Command-Line Format --binlog-rotate-encryption-master-key-at-startup[={OFF|ON}]
Introduced 8.0.14 System Variable binlog_rotate_encryption_master_key_at_startup
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Specifies whether or not the binary log master key is rotated at server startup. The binary log master key is the binary log encryption key that is used to encrypt file passwords for the binary log files and relay log files on the server. When a server is started for the first time with binary log encryption enabled (
binlog_encryption=ON
), a new binary log encryption key is generated and used as the binary log master key. If thebinlog_rotate_encryption_master_key_at_startup
system variable is also set toON
, whenever the server is restarted, a further binary log encryption key is generated and used as the binary log master key for all subsequent binary log files and relay log files. If thebinlog_rotate_encryption_master_key_at_startup
system variable is set toOFF
, which is the default, the existing binary log master key is used again after the server restarts. For more information on binary log encryption keys and the binary log master key, see Section 17.3.10, “Encrypting Binary Log Files and Relay Log Files”.-
Property Value Command-Line Format --binlog-row-event-max-size=#
System Variable (>= 8.0.14) binlog_row_event_max_size
Scope (>= 8.0.14) Global Dynamic (>= 8.0.14) No SET_VAR
Hint Applies (>= 8.0.14)No Type Integer Default Value 8192
Minimum Value 256
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
When row-based binary logging is used, this setting is a soft limit on the maximum size of a row-based binary log event, in bytes. Where possible, rows stored in the binary log are grouped into events with a size not exceeding the value of this setting. If an event cannot be split, the maximum size can be exceeded. The value must be (or else gets rounded down to) a multiple of 256. The default is 8192 bytes.
This global system variable is read-only and can be set only at server startup. Its value can therefore only be modified by using the
PERSIST_ONLY
keyword or the@@persist_only
qualifier with theSET
statement. -
Property Value Command-Line Format --binlog-row-image=image_type
System Variable binlog_row_image
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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.
Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
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.
NoteWhen 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.
NoteThis variable is not supported by NDB Cluster; setting it has no effect on the logging of
NDB
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 master and slave 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 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. -
Property Value Command-Line Format --binlog-row-metadata=metadata_type
Introduced 8.0.1 System Variable binlog_row_metadata
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value MINIMAL
Valid Values FULL
(All metadata is included)MINIMAL
(Limit included metadata)Configures the amount of table metadata added to the binary log when using row-based logging. When set to
MINIMAL
, the default, only metadata related toSIGNED
flags, column character set and geometry types are logged. When set toFULL
complete metadata for tables is logged, such as column name,ENUM
orSET
string values,PRIMARY KEY
information, and so on.The extended metadata serves the following purposes:
Slaves use the metadata to transfer data when its table structure is different from the master's.
External software can use the metadata to decode row events and store the data into external databases, such as a data warehouse.
-
Property Value Command-Line Format --binlog-row-value-options=#
Introduced 8.0.3 System Variable binlog_row_value_options
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Set Default Value ''
Valid Values PARTIAL_JSON
When set to
PARTIAL_JSON
, this enables use of a space-efficient binary log format for updates that modify only a small portion of a JSON document, which causes row-based replication to write only the modified parts of the JSON document to the after-image for the update in the binary log (rather than writing the full document). This works for anUPDATE
statement which modifies a JSON column using any sequence ofJSON_SET()
,JSON_REPLACE()
, andJSON_REMOVE()
. If the modification requires more space than the full document, or if the server is unable to generate a partial update, the full document is used instead.Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
PARTIAL_JSON
is the only supported value; to unsetbinlog_row_value_options
, set its value to the empty string.binlog_row_value_options=PARTIAL_JSON
takes effect only when binary logging is enabled andbinlog_format
is set toROW
orMIXED
. Statement-based replication always logs only the modified parts of the JSON document, regardless of any value set forbinlog_row_value_options
. To maximize the amount of space saved, usebinlog_row_image=NOBLOB
orbinlog_row_image=MINIMAL
together with this option.binlog_row_image=FULL
saves less space than either of these, since the full JSON document is stored in the before-image, and the partial update is stored only in the after-image.mysqlbinlog output includes partial JSON updates in the form of events encoded as base-64 strings using
BINLOG
statements. If the--verbose
option is specified, mysqlbinlog displays the partial JSON updates as readable JSON using pseudo-SQL statements.MySQL Replication generates an error if a modification cannot be applied to the JSON document on the slave. This includes a failure to find the path. Be aware that, even with this and other safety checks, if a JSON document on a slave has diverged from that on the master and a partial update is applied, it remains theoretically possible to produce a valid but unexpected JSON document on the slave.
-
Property Value Command-Line Format --binlog-rows-query-log-events[={OFF|ON}]
System Variable binlog_rows_query_log_events
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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 master when it cannot be reconstructed from the row updates.
Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
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
. -
Property Value Command-Line Format --binlog-stmt-cache-size=#
System Variable binlog_stmt_cache_size
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 32768
Minimum Value 4096
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
The size of the memory buffer for the binary log to hold nontransactional statements issued during a transaction. When binary logging is enabled on the server (with the
log_bin
system variable set to ON), separate binary log transaction and statement caches are allocated for each client if the server supports any transactional storage engines. If the data for the nontransactional statements used in the transaction exceeds the space in the memory buffer, the excess data is stored in a temporary file. When binary log encryption is active on the server, the memory buffer is not encrypted, but (from MySQL 8.0.17) any temporary file used to hold the binary log cache is encrypted. After each transaction is committed, the binary log statement cache is reset by clearing the memory buffer and truncating the temporary file if used.If you often use large nontransactional statements during transactions, you can increase this cache size to get better performance by reducing or eliminating the need to write to temporary files. The
Binlog_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
Property Value Command-Line Format --binlog-transaction-dependency-tracking=value
Introduced 8.0.1 System Variable binlog_transaction_dependency_tracking
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value COMMIT_ORDER
Valid Values COMMIT_ORDER
WRITESET
WRITESET_SESSION
The source of dependency information that the master uses to determine which transactions can be executed in parallel by the slave'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 master's commit timestamps. This is the default. This mode is also used for any transactions without write sets, even if this variable's isWRITESET
orWRITESET_SESSION
; this is also the case for transactions updating tables without primary keys and transactions updating tables having foreign key constraints.WRITESET
: Dependency information is generated from the master's write set, and any transactions which write different tuples can be parallelized.WRITESET_SESSION
: Dependency information is generated from the master's write set, but no two updates from the same session can be reordered.
WRITESET
andWRITESET_SESSION
modes do not deliver any transaction dependencies that are newer than those that would have been returned inCOMMIT_ORDER
mode.The value of this variable cannot be set to anything other than
COMMIT_ORDER
iftransaction_write_set_extraction
isOFF
. You should also note that the value oftransaction_write_set_extraction
cannot be changed if the current value ofbinlog_transaction_dependency_tracking
isWRITESET
orWRITESET_SESSION
.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 of
binlog_transaction_dependency_history_size
.binlog_transaction_dependency_history_size
Property Value Command-Line Format --binlog-transaction-dependency-history-size=#
Introduced 8.0.1 System Variable binlog_transaction_dependency_history_size
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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.
-
Property Value Command-Line Format --expire-logs-days=#
Deprecated 8.0.3 System Variable expire_logs_days
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value (>= 8.0.11) 0
Default Value (>= 8.0.2, <= 8.0.4) 30
Default Value (<= 8.0.1) 0
Minimum Value 0
Maximum Value 99
Specifies the number of days before automatic removal of binary log files.
expire_logs_days
is deprecated, and will be removed in a future release. Instead, usebinlog_expire_logs_seconds
, which sets the binary log expiration period in seconds. If you do not set a value for either system variable, the default expiration period is 30 days. Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.4, “MySQL Server Logs”.Any non-zero value that you specify for
expire_logs_days
is ignored ifbinlog_expire_logs_seconds
is also specified, and the value ofbinlog_expire_logs_seconds
is used instead as the binary log expiration period. A warning message is issued in this situation. A non-zero value forexpire_logs_days
is only applied as the binary log expiration period ifbinlog_expire_logs_seconds
is not specified or is specified as 0.To disable automatic purging of the binary log, specify a value of 0 explicitly for
binlog_expire_logs_seconds
, and do not specify a value forexpire_logs_days
. For compatibility with earlier releases, automatic purging is also disabled if you specify a value of 0 explicitly forexpire_logs_days
and do not specify a value forbinlog_expire_logs_seconds
. In that case, the default forbinlog_expire_logs_seconds
is not applied.To remove binary log files manually, use the
PURGE BINARY LOGS
statement. See Section 13.4.1.1, “PURGE BINARY LOGS Syntax”. -
Shows the status of binary logging on the server, either enabled (
ON
) or disabled (OFF
). With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication.ON
means that the binary log is available,OFF
means that it is not in use. The--log-bin
option can be used to specify a base name and location for the binary log.In earlier MySQL versions, binary logging was disabled by default, and was enabled if you specified the
--log-bin
option. From MySQL 8.0, binary logging is enabled by default, with thelog_bin
system variable set toON
, whether or not you specify the--log-bin
option. The exception is if you use mysqld to initialize the data directory manually by invoking it with the--initialize
or--initialize-insecure
option, when binary logging is disabled by default. It is possible to enable binary logging in this case by specifying the--log-bin
option.If the
--skip-log-bin
or--disable-log-bin
option is specified at startup, binary logging is disabled, with thelog_bin
system variable set toOFF
. If either of these options is specified and--log-bin
is also specified, the option specified later takes precedence.For information on the format and management of the binary log, see Section 5.4.4, “The Binary Log”.
-
Property Value System Variable log_bin_basename
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type File name Holds the base name and path for the binary log files, which can be set with the
--log-bin
server option. In MySQL 8.0, if the--log-bin
option is not supplied, the default base name isbinlog
. For compatibility with MySQL 5.7, if the--log-bin
option is supplied with no string or with an empty string, the default base name is
, using the name of the host machine. The default location is the data directory.host_name
-bin -
Property Value Command-Line Format --log-bin-index=file_name
System Variable log_bin_index
Scope Global Dynamic No SET_VAR
Hint AppliesNo 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. log_bin_trust_function_creators
Property Value Command-Line Format --log-bin-trust-function-creators[={OFF|ON}]
System Variable log_bin_trust_function_creators
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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 will cause 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 theCREATE ROUTINE
orALTER ROUTINE
privilege. A setting of 0 also enforces the restriction that a function must be declared with theDETERMINISTIC
characteristic, or with theREADS SQL DATA
orNO 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 24.7, “Stored Program Binary Logging”.-
Property Value Command-Line Format --log-bin-use-v1-row-events[={OFF|ON}]
Deprecated 8.0.18 System Variable log_bin_use_v1_row_events
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
This read-only system variable is deprecated. Setting the system variable to
ON
(using the--log-bin-use-v1-row-events
option) enabled row-based replication with slaves running MySQL Server 5.5 and earlier by writing the binary log using Version 1 binary log row events, instead of Version 2 binary log row events which are the default from MySQL 5.6. log_builtin_as_identified_by_password
Property Value Command-Line Format --log-builtin-as-identified-by-password[={OFF|ON}]
Removed 8.0.11 System Variable log_builtin_as_identified_by_password
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
This system variable was removed in MySQL 8.0.11.
-
Property Value Command-Line Format --log-slave-updates[={OFF|ON}]
System Variable log_slave_updates
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value (>= 8.0.3) ON
Default Value (<= 8.0.2) OFF
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect. See Section 17.1.6, “Replication and Binary Logging Options and Variables”.
This system variable is set on by default, and is read-only. If you need to prevent the slave server from logging updates, specify
--skip-log-slave-updates
when you start the slave, or specifylog_slave_updates=OFF
in the configuration file for the slave. log_statements_unsafe_for_binlog
Property Value Command-Line Format --log-statements-unsafe-for-binlog[={OFF|ON}]
System Variable log_statements_unsafe_for_binlog
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value ON
If error 1592 is encountered, controls whether the generated warnings are added to the error log or not.
-
Property Value Command-Line Format --master-verify-checksum[={OFF|ON}]
System Variable master_verify_checksum
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Enabling this variable causes the master to examine checksums when reading from the binary log.
master_verify_checksum
is disabled by default; in this case, the master uses the event length from the binary log to verify events, so that only complete events are read from the binary log. -
Property Value Command-Line Format --max-binlog-cache-size=#
System Variable max_binlog_cache_size
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 18446744073709551615
Minimum Value 4096
Maximum Value 18446744073709551615
If a transaction requires more than this many bytes of memory, the server generates a Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage error. The minimum value is 4096. The maximum possible value is 16EiB (exbibytes). The maximum recommended value is 4GB; this is due to the fact that MySQL currently cannot work with binary log positions greater than 4GB.
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 of
max_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. -
Property Value Command-Line Format --max-binlog-size=#
System Variable max_binlog_size
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 1073741824
Minimum Value 4096
Maximum Value 1073741824
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. Encrypted binary log files have an additional 512-byte header, which is included in
max_binlog_size
.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 than
max_binlog_size
.If
max_relay_log_size
is 0, the value ofmax_binlog_size
applies to relay logs as well.With GTIDs in use on the server, when
max_binlog_size
is reached, if the system tablemysql.gtid_executed
cannot be accessed to write the GTIDs from the current binary log file, the binary log cannot be rotated. In this situation, the server responds according to itsbinlog_error_action
setting. IfIGNORE_ERROR
is set, an error is logged on the server and binary logging is halted, or ifABORT_SERVER
is set, the server shuts down. -
Property Value Command-Line Format --max-binlog-stmt-cache-size=#
System Variable max_binlog_stmt_cache_size
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 18446744073709547520
Minimum Value 4096
Maximum Value 18446744073709547520
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. -
Property Value Introduced 8.0.1 System Variable original_commit_timestamp
Scope Session Dynamic Yes SET_VAR
Hint AppliesNo Type Numeric For internal use by replication. When re-executing a transaction on a slave, this is set to the time when the transaction was committed on the original master, measured in microseconds since the epoch. This allows the original commit timestamp to be propagated throughout a replication topology.
Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
-
Property Value System Variable sql_log_bin
Scope Session Dynamic Yes SET_VAR
Hint AppliesNo 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 is
ON
. To disable or enable binary logging for the current session, set the sessionsql_log_bin
variable toOFF
orON
.Set this variable to
OFF
for a session to temporarily disable binary logging while making changes to the master you do not want replicated to the slave.Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
It is not possible to set the session value of
sql_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. -
Property Value Command-Line Format --sync-binlog=#
System Variable sync_binlog
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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=
, whereN
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:CautionMany operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld 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
Property Value Command-Line Format --transaction-write-set-extraction[=value]
System Variable transaction_write_set_extraction
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value (>= 8.0.2) XXHASH64
Default Value OFF
Valid Values OFF
MURMUR32
XXHASH64
Defines the algorithm used to hash the writes extracted during a transaction. If you are using Group Replication, this variable must be set to
XXHASH64
because the process of extracting the writes from a transaction is required for conflict detection on all group members. See Section 18.9.1, “Group Replication Requirements”.As of MySQL 8.0.14, setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
NoteThe value of this variable cannot be changed when
binlog_transaction_dependency_tracking
is set to either ofWRITESET
orWRITESET_SESSION
.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-replication-options-binary-log.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.