16.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.6, “Server Command Options”, and Section 5.1.7, “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.

  • --binlog-row-event-max-size=N

    Property Value
    Command-Line Format --binlog-row-event-max-size=#
    Type Integer
    Default Value 8192
    Minimum Value 256
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    Specify the maximum size of a row-based binary log event, in bytes. Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256. The default is 8192. See Section 16.2.1, “Replication Formats”.

  • --log-bin[=base_name]

    Property Value
    Command-Line Format --log-bin=file_name
    Type File name

    Enables binary logging. 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. For information on the format and management of the binary log, see Section 5.4.4, “The Binary Log”.

    If you supply a value for the --log-bin option, the value is used as the base name for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the base name. In MySQL 5.7, the base name defaults to host_name-bin, using the name of the host machine. It is recommended that you specify a base name, so that you can continue to use the same binary log file names regardless of changes to the default name.

    The 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. (In older versions of MySQL, manual intervention was required whenever relocating the binary log or relay log files.) (Bug #11745230, Bug #12133)

    Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the base name. The binary log file base name and any specified path are available as the log_bin_basename system variable.

    If you specify the --log-bin option without also specifying the server_id system variable, the server is not allowed to start. (Bug #11763963, Bug #56739)

    When GTIDs are in use on the server, if binary logging is not enabled 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.

  • --log-bin-index[=file_name]

    Property Value
    Command-Line Format --log-bin-index=file_name
    System Variable log_bin_index
    Scope Global
    Dynamic No
    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 is binlog.index. If you omit the file name and do not specify one with --log-bin, the default binary log index file name is host_name-bin.index, using the name of the host machine.

    For information on the format and management of the binary log, see Section 5.4.4, “The Binary Log”.

Statement selection options.  The options in the following list affect which statements are written to the binary log, and thus sent by a replication source server to its replicas. There are also options for replica servers that control which statements received from the source should be executed or ignored. For details, see Section 16.1.6.3, “Replica Server Options and Variables”.

  • --binlog-do-db=db_name

    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 of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER 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) is db_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 as UPDATE some_db.some_table SET foo='bar' to be logged while a different database (or no database) is selected.

    Warning

    To 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, the UPDATE statement is not logged:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;

    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-table UPDATE 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 following UPDATE statement is logged even though prices was not included when setting --binlog-do-db:

    USE sales;
    UPDATE prices.discounts SET percentage = percentage + 10;

    Because sales is the default database when the UPDATE statement is issued, the UPDATE is logged.

    Row-based logging.  Logging is restricted to database db_name. Only changes to tables belonging to db_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:

    USE prices;
    UPDATE sales.february SET amount=amount+100;

    The changes to the february table in the sales database are logged in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, when using the row-based logging format and --binlog-do-db=sales, changes made by the following UPDATE are not logged:

    USE prices;
    UPDATE prices.march SET amount=amount-25;

    Even if the USE prices statement were changed to USE sales, the UPDATE 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:

    USE db1;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

    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 the UPDATE. Now suppose that, instead of the USE db1 statement, a USE db4 statement had been used:

    USE db4;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

    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 to table1 is logged, but not that to table2—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.

  • --binlog-ignore-db=db_name

    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 of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER 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) is db_name.

    Prior to MySQL 5.7.2, this option caused any statements containing fully qualified table names not to be logged if there was no default database specified (that is, when SELECT DATABASE() returned NULL). In MySQL 5.7.2 and higher, 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:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;

    The UPDATE statement is logged in such a case because --binlog-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based logging, the UPDATE statement's effects are not written to the binary log, which means that no changes to the sales.january table are logged; in this instance, --binlog-ignore-db=sales causes all changes made to tables in the source's copy of the sales 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 source 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.

To control reading of checksums by the replica (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.

  • --max-binlog-dump-events=N

    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.

  • --sporadic-binlog-dump-fail

    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.

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 using SET. Server options used to control binary logging are listed earlier in this section.

  • binlog_cache_size

    Property Value
    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) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    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. The Binlog_cache_use and Binlog_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 the binlog_stmt_cache_size system variable.

  • binlog_checksum

    Property Value
    Command-Line Format --binlog-checksum=name
    System Variable binlog_checksum
    Scope Global
    Dynamic Yes
    Type String
    Default Value CRC32
    Valid Values

    NONE

    CRC32

    When enabled, this variable causes the source to write a checksum for each event in the binary log. binlog_checksum supports the values NONE (disabled) and CRC32. The default is CRC32. You cannot change the value of binlog_checksum within a transaction.

    When binlog_checksum is disabled (value NONE), 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 own binlog_checksum value to NONE, 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

    Property Value
    Command-Line Format --binlog-direct-non-transactional-updates[={OFF|ON}]
    System Variable binlog_direct_non_transactional_updates
    Scope Global, Session
    Dynamic Yes
    Type Boolean
    Default Value OFF

    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.

    The binlog_direct_non_transactional_updates variable offers one possible workaround to this issue. By default, this variable is disabled. Enabling binlog_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 of binlog_format is STATEMENT, or when binlog_format is MIXED and a given statement is being replicated using the statement-based format. This variable has no effect when the binary log format is ROW, or when binlog_format is set to MIXED 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 is ROW or MIXED.

  • binlog_error_action

    Property Value
    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_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 source's binary log to become inconsistent and replicas to lose synchronization.

    In MySQL 5.7.7 and higher, 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 16.3.2, “Handling an Unexpected Halt of a Replica”).

    When binlog_error_action is set to IGNORE_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 logging log_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 named binlogging_impossible_mode.

  • binlog_format

    Property Value
    Command-Line Format --binlog-format=format
    System Variable binlog_format
    Scope Global, Session
    Dynamic Yes
    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, or MIXED. See Section 16.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.

    Prior to MySQL 5.7.7, the default format was STATEMENT. 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. See Section 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 user-defined functions (UDF) 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, see Section 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 using STATEMENT 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 to STATEMENT 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
    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. Setting binlog_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 or sync_binlog=1 is set, the delay specified by binlog_group_commit_sync_delay is applied for every binary log commit group before synchronization (or in the case of sync_binlog=0, before proceeding). When sync_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 replica, and therefore can increase parallel execution on the replicas. To benefit from this effect, the replica servers must have slave_parallel_type=LOGICAL_CLOCK set, and the effect is more significant when binlog_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 for binlog_group_commit_sync_delay.

    Setting binlog_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 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
    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. If binlog_group_commit_sync_delay is set to 0, then this option has no effect.

  • binlog_max_flush_queue_time

    Property Value
    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

    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

    Property Value
    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. 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 source and on a multithreaded replica remains identical, set slave_preserve_commit_order=1 on the replica.

  • binlog_row_image

    Property Value
    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 any NULL 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 as full), except for BLOB and TEXT 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 of NDB tables.

    The default value is full.

    When using minimal or noblob, 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. When binlog_format is MIXED, the setting for binlog_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

    Property Value
    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

    Property Value
    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) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    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. The Binlog_stmt_cache_use and Binlog_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 5.7.22
    System Variable binlog_transaction_dependency_tracking
    Scope Global
    Dynamic Yes
    Type Enumeration
    Default Value COMMIT_ORDER
    Valid Values

    COMMIT_ORDER

    WRITESET

    WRITESET_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. This mode is also used for any transactions without write sets, even if this variable's is WRITESET or WRITESET_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 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, but no two updates from the same session can be reordered.

    WRITESET and WRITESET_SESSION modes do not deliver any transaction dependencies that are newer than those that would have been returned in COMMIT_ORDER mode.

    The value of this variable cannot be set to anything other than COMMIT_ORDER if transaction_write_set_extraction is OFF. You should also note that the value of transaction_write_set_extraction cannot be changed if the current value of binlog_transaction_dependency_tracking is WRITESET or WRITESET_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 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

    Property Value
    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

    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 the PURGE BINARY LOGS statement. See Section 13.4.1.1, “PURGE BINARY LOGS Statement”.

  • log_bin

    Property Value
    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

    Property Value
    System Variable log_bin_basename
    Scope Global
    Dynamic No
    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 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

    Property Value
    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.

  • 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
    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 the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS 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

    Property Value
    Command-Line Format --log-bin-use-v1-row-events[={OFF|ON}]
    System Variable log_bin_use_v1_row_events
    Scope Global
    Dynamic No
    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. Enabling log_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 set log_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 using NDB$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, see Section 21.6.11, “NDB Cluster Replication Conflict Resolution”.

  • log_builtin_as_identified_by_password

    Property Value
    Command-Line Format --log-builtin-as-identified-by-password[={OFF|ON}]
    System Variable log_builtin_as_identified_by_password
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value OFF

    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

    Property Value
    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 both A and B with --log-bin to enable binary logging, and B with log_slave_updates enabled so that updates received from A are logged by B to its binary log.

  • log_statements_unsafe_for_binlog

    Property Value
    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