17.1.3.4 Binary Log Options and Variables

Startup Options Used with Binary Logging

System Variables Used with Binary Logging

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.2.4, “The Binary Log”. For additional information about using MySQL server options and system variables, see Section 5.1.3, “Server Command Options”, and Section 5.1.4, “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

    Command-Line Format--binlog-row-event-max-size=#
    Option-File Formatbinlog-row-event-max-size
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default1024
    Range256 .. 4294967295
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default1024
    Range256 .. 18446744073709547520

    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 1024. See Section 17.1.2, “Replication Formats”.

  • --log-bin[=base_name]

    Command-Line Format--log-bin
    Option-File Formatlog-bin
    System Variable Namelog_bin
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name

    Enable binary logging. The server logs all statements that change data to the binary log, which is used for backup and replication. See Section 5.2.4, “The Binary Log”.

    The option value, if given, is the basename for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the basename. It is recommended that you specify a basename (see Section B.5.8, “Known Issues in MySQL”, for the reason). Otherwise, MySQL uses host_name-bin as the basename.

    In MySQL 5.5.20 and later, when the server reads an entry from the index file, it checks whether the entry contains a relative path, and if it does, the relative part of the path in replaced with the absolute path set using the --log-bin option. An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. Previous to MySQL 5.5.20, 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 basename. This is a known issue; see Bug #19614 for more information.

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

    Command-Line Format--log-bin-index=name
    Option-File Formatlog-bin-index
     Permitted Values
    Typefile name

    The index file for binary log file names. See Section 5.2.4, “The Binary Log”. If you omit the file name, and if you did not specify one with --log-bin, MySQL uses host_name-bin.index as the file name.

  • --log-bin-trust-function-creators[={0|1}]

    Command-Line Format--log-bin-trust-function-creators
    Option-File Formatlog-bin-trust-function-creators
    System Variable Namelog_bin_trust_function_creators
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultFALSE

    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 20.7, “Binary Logging of Stored Programs”.

  • --log-bin-use-v1-row-events[={0|1}]

    Introduced5.5.15-ndb-7.2.1
    Command-Line Format--log-bin-use-v1-row-events[={0|1}]
    Option-File Formatlog-bin-use-v1-row-events
    System Variable Namelog_bin_use_v1_row_events
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    Default0

    Version 2 binary log row events are used by default beginning with MySQL Cluster NDB 7.2.1; however, Version 2 events cannot be read by previous MySQL Cluster releases. Setting --log-bin-use-v1-row-events to 1 causes mysqld to write the binary log using Version 1 logging events, which is the only version of binary log events used in previous releases, and thus produce binary logs that can be read by older slaves.

    The value used for this option can be obtained from the read-only log_bin_use_v1_row_events system variable.

    --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 option and --ndb-log-transaction-id are not compatible.

    Note

    Version 2 binary log row events are also available in MySQL Cluster NDB 7.0.27 and MySQL Cluster NDB 7.1.6 and later MySQL Cluster NDB 7.0 and 7.1 releases. However, prior to MySQL Cluster NDB 7.2.1, Version 1 events are the default (and so the default value for this option is 1 in those versions). You should keep this mind when planning upgrades for setups using MySQL Cluster Replication.

    --log-bin-use-v1-row-events is not supported in mainline MySQL Server 5.5 releases.

    For more information, see Section 18.6.11, “MySQL Cluster Replication Conflict Resolution”.

  • --log-short-format

    Command-Line Format--log-short-format
    Option-File Formatlog-short-format
     Permitted Values
    Typeboolean
    DefaultFALSE

    Log less information to the binary log and slow query log, if they have been activated.

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.3.3, “Replication Slave Options and Variables”.

  • --binlog-do-db=db_name

    Command-Line Format--binlog-do-db=name
    Option-File Formatbinlog-do-db
     Permitted Values
    Typestring

    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

    Command-Line Format--binlog-ignore-db=name
    Option-File Formatbinlog-ignore-db
     Permitted Values
    Typestring

    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.5.32, 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.5.32 and later, 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 master'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.

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

    Command-Line Format--max-binlog-dump-events=#
    Option-File Formatmax-binlog-dump-events
     Permitted Values
    Typenumeric
    Default0

    This option is used internally by the MySQL test suite for replication testing and debugging.

  • --sporadic-binlog-dump-fail

    Command-Line Format--sporadic-binlog-dump-fail
    Option-File Formatsporadic-binlog-dump-fail
     Permitted Values
    Typeboolean
    DefaultFALSE

    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. For information about the sql_log_bin and sql_log_off variables, see Section 5.1.4, “Server System Variables”.

  • binlog_cache_size

    Command-Line Format--binlog_cache_size=#
    Option-File Formatbinlog_cache_size
    System Variable Namebinlog_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default32768
    Range4096 .. 4294967295
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default32768
    Range4096 .. 18446744073709547520

    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.2.4, “The Binary Log”.

    In MySQL 5.5.3, a separate binary log cache (the binary log statement cache) was introduced for nontransactional statements and in MySQL 5.5.3 through 5.5.8, this variable sets.the size for both caches. This means that, in these MySQL versions, the total memory used for these caches is double the value set for binlog_cache_size.

    Beginning with MySQL 5.5.9, binlog_cache_size sets the size for the transaction cache only, and the size of the statement cache is governed by the binlog_stmt_cache_size system variable.

  • binlog_direct_non_transactional_updates

    Introduced5.5.2
    Command-Line Format--binlog_direct_non_transactional_updates[=value]
    Option-File Formatbinlog_direct_non_transactional_updates
    System Variable Namebinlog_direct_non_transactional_updates
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultOFF

    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.

    Beginning with MySQL 5.5.2, 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 slave to diverge from the master.

    Beginning with MySQL 5.5.5, this variable has no effect when the binary log format is ROW or MIXED. (Bug #51291)

  • binlog_format

    Command-Line Format--binlog-format=format
    Option-File Formatbinlog-format
    System Variable Namebinlog_format
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Typeenumeration
    DefaultSTATEMENT
    Valid ValuesROW
    STATEMENT
    MIXED

    This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED. See Section 17.1.2, “Replication Formats”. binlog_format is set by the --binlog-format option at startup, or by the binlog_format system variable at runtime.

    Note

    While you can change the logging format at runtime, it is not recommended that you change it while replication is ongoing. This is due in part to the fact that slaves do not honor the master's binlog_format setting; a given MySQL Server can change only its own logging format.

    In MySQL 5.5, the default format is STATEMENT.

    Exception.  In MySQL Cluster NDB 7.2.1 through MySQL Cluster NDB 7.2.12, the default for this variable is STATEMENT. In MySQL Cluster NDB 7.2.13 and later, when the NDB storage engine is enabled, the default is MIXED. (Bug #16417224) See also Section 18.6.6, “Starting MySQL Cluster Replication (Single Replication Channel)”, and Section 18.6.7, “Using Two Replication Channels for MySQL Cluster Replication”.

    You must have the SUPER privilege to set either the global or session binlog_format value.

    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. See Section 13.7.4, “SET Syntax”, for more information.

    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. An exception to this rule is that MIXED always uses statement-based replication for stored functions and triggers.

    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.

    • Beginning with MySQL 5.5.3, within a transaction. (Bug #47863)

    Trying to switch the format in those cases results in an error.

    Note

    Prior to MySQL Cluster NDB 7.2.1, it was also not possible to change the binary logging format at runtime when the NDBCLUSTER storage engine was enabled. In MySQL Cluster NDB 7.2.1 and later, this restriction is removed.

    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_stmt_cache_size

    Introduced5.5.9
    Command-Line Format--binlog_stmt_cache_size=#
    Option-File Formatbinlog_stmt_cache_size
    System Variable Namebinlog_stmt_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default32768
    Range4096 .. 4294967295
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default32768
    Range4096 .. 18446744073709547520

    Beginning with MySQL 5.5.9, this variable determines the size of the cache for the binary log to hold nontransactional statements issued during a transaction. In MySQL 5.5.3 and later, 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 more 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.2.4, “The Binary Log”.

    In MySQL 5.5.3 through 5.5.8, the size for both caches is set using binlog_cache_size. This means that, in these MySQL versions, the total memory used for these caches is double the value set for binlog_cache_size. Beginning with MySQL 5.5.9, binlog_cache_size sets the size for the transaction cache only.

  • log_bin

    System Variable Namelog_bin
    Variable ScopeGlobal
    Dynamic VariableNo

    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.2.4, “The Binary Log”.

  • log_bin_use_v1_row_events

    Introduced5.5.15-ndb-7.2.1
    Command-Line Format--log-bin-use-v1-row-events[={0|1}]
    Option-File Formatlog_bin_use_v1_row_events
    System Variable Namelog_bin_use_v1_row_events
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    Default0

    Shows whether Version 2 binary logging, available beginning with MySQL Cluster NDB 7.2.1, is in use. A value of 1 shows that the server is writing the binary log using Version 1 logging events (the only version of binary log events used in previous releases), and thus producing a binary log that can be read by older slaves. 0 indicates that Version 2 binary log events are in use.

    This variable is read-only. To switch between Version 1 and Version 2 binary event binary logging, it is necessary to restart mysqld with the --log-bin-use-v1-row-events option.

    Other than when performing upgrades of MySQL Cluster Replication, --log-bin-use-v1-events is chiefly of interest when setting up replication conflict detection and resolution using NDB$EPOCH_TRANS(), which requires Version 2 binary row event logging. Thus, this option and --ndb-log-transaction-id are not compatible.

    Note

    MySQL Cluster NDB 7.2.1 and later use Version 2 binary log row events by default (and so the default value for this variable changes to 0 in those versions). You should keep this mind when planning upgrades for setups using MySQL Cluster Replication.

    This variable is not supported in mainline MySQL Server 5.5.

    For more information, see Section 18.6.11, “MySQL Cluster Replication Conflict Resolution”.

  • log_slave_updates

    Command-Line Format--log-slave-updates
    Option-File Formatlog_slave_updates
    System Variable Namelog_slave_updates
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    DefaultFALSE

    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.3, “Replication and Binary Logging Options and Variables”.

  • max_binlog_cache_size

    Command-Line Format--max_binlog_cache_size=#
    Option-File Formatmax_binlog_cache_size
    System Variable Namemax_binlog_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default18446744073709547520
    Range4096 .. 18446744073709547520

    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 16EB (exabytes). The maximum recommended value is 4GB; this is due to the fact that MySQL currently cannot work with binary log positions greater than 4GB.

    Note

    Prior to MySQL 5.5.28, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).

    In MySQL 5.5.3, a separate binary log cache (the binary log statement cache) was introduced for nontransactional statements and in MySQL 5.5.3 through 5.5.8, this variable sets.the upper limit for both caches. This means that, in these MySQL versions, the effective maximum for these caches is double the value set for max_binlog_cache_size.

    Beginning with MySQL 5.5.9, max_binlog_cache_size sets the size for the transaction cache only, and the upper limit for the statement cache is governed by the max_binlog_stmt_cache_size system variable.

    Also beginning with MySQL 5.5.9, the session visibility of the max_binlog_cache_size system variable matches that of the binlog_cache_size system variable: In MySQL 5.5.8 and earlier releases, a change in max_binlog_cache_size took immediate effect; in MySQL 5.5.9 and later, a change in max_binlog_cache_size takes effect only for new sessions that started after the value is changed.

  • max_binlog_size

    Command-Line Format--max_binlog_size=#
    Option-File Formatmax_binlog_size
    System Variable Namemax_binlog_size
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default1073741824
    Range4096 .. 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.

    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 of max_binlog_size applies to relay logs as well.

  • max_binlog_stmt_cache_size

    Introduced5.5.9
    Command-Line Format--max_binlog_stmt_cache_size=#
    Option-File Formatmax_binlog_stmt_cache_size
    System Variable Namemax_binlog_stmt_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default18446744073709547520
    Range4096 .. 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.

    Note

    Prior to MySQL 5.5.28, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).

    In MySQL 5.5.3, a separate binary log cache (the binary log statement cache) was introduced for nontransactional statements and in MySQL 5.5.3 through 5.5.8, this variable sets.the upper limit for both caches. This means that, in these MySQL versions, the effective maximum for these caches is double the value set for max_binlog_cache_size.

    Beginning with MySQL 5.5.9, max_binlog_stmt_cache_size sets the size for the statement cache only, and the upper limit for the transaction cache is governed exclusively by the max_binlog_cache_size system variable.

  • sync_binlog

    Command-Line Format--sync-binlog=#
    Option-File Formatsync_binlog
    System Variable Namesync_binlog
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default0
    Range0 .. 4294967295
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default0
    Range0 .. 18446744073709547520

    If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).