Skip navigation links
16.1.3 Common Replication Administration Tasks »
Section Navigation      [Toggle]

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

Startup options used with binary logging.  The following list describes startup options for enabling and configuring the binary log. Many of these options can be reset while the server is running by using the CHANGE MASTER TO statement. Others, can be set only when the slave server starts. System variables used with binary logging are discussed later in this section.

  • --log-bin[=base_name]

    Command Line Format --log-bin
    Config File Format log-bin
    Variable Name log_bin
    Variable Scope Global
    Dynamic Variable No
      Permitted Values
    Type filename
    Default OFF

    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.3, “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 Additional Known Issues, for the reason). Otherwise, MySQL uses host_name-bin as the basename.

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

    Command Line Format --log-bin-index=name
    Config File Format log-bin-index
      Permitted Values
    Type filename
    Default OFF

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

  • 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.

    • --binlog-do-db=db_name

      Tell the server to restrict binary logging to updates for which the default database is db_name (that is, the database selected by USE). All other databases that are not explicitly mentioned are ignored. If you use this option, you should ensure that you do updates only in the default database.

      There is an exception to this for CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

      An example of what does not work as you might expect: If the server is started with binlog-do-db=sales, and you run USE prices; UPDATE sales.january SET amount=amount+1000;, this statement is not written into the binary log.

      Important

      To log multiple databases, use this option multiple times, specifying the option once for each database to be logged.

    • --binlog-ignore-db=db_name

      Tell the server to suppress binary logging of updates for which the default database is db_name (that is, the database selected by USE). If you use this option, you should ensure that you do updates only in the default database.

      As with the --binlog-do-db option, there is an exception for the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

      An example of what does not work as you might expect: If the server is started with binlog-ignore-db=sales, and you run USE prices; UPDATE sales.january SET amount = amount + 1000;, this statement is written into the binary log.

      Important

      To ignore multiple databases, use this option multiple times, specifying the option once for each database to be ignored.

    Additional server options that can be used to control logging also affect the binary log. For more information about these, see Section 5.1.2, “Server Command Options”. For more information about how the options in the previous list are applied, see Section 5.2.3, “The Binary Log”.

    There are also options for slave servers that control which statements received from the master should be executed or ignored. For details, see Section 16.1.2.3, “Replication Slave Options and Variables”.

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

    Version Introduced 5.0.16
    Command Line Format --log-bin-trust-function-creators
    Config File Format log-bin-trust-function-creators
    Option Sets Variable Yes, log_bin_trust_function_creators
    Variable Name log_bin_trust_function_creators
    Variable Scope Global
    Dynamic Variable Yes
      Permitted Values
    Type boolean
    Default FALSE

    With no argument or an argument of 1, this option sets the log_bin_trust_function_creators system variable to 1. With an argument of 0, this option sets the system variable to 0. log_bin_trust_function_creators affects how MySQL enforces restrictions on stored function and trigger creation. See Section 18.5, “Binary Logging of Stored Programs”.

    This option was added in MySQL 5.0.16.

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

    Version Introduced 5.0.6
    Version Deprecated 5.0.16
    Command Line Format --log-bin-trust-routine-creators
    Config File Format log-bin-trust-routine-creators
    Option Sets Variable Yes, log_bin_trust_routine_creators
    Variable Name log-bin-trust-routine-creators
    Variable Scope Global
    Dynamic Variable Yes
    Deprecated 5.0.16, by log-bin-trust-function-creators
      Permitted Values
    Type boolean
    Default FALSE

    This is the old name for --log-bin-trust-function-creators. Before MySQL 5.0.16, it also applies to stored procedures, not just stored functions and sets the log_bin_trust_routine_creators system variable. As of 5.0.16, this option is deprecated. It is recognized for backward compatibility but its use results in a warning.

    This option was added in MySQL 5.0.6.

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

    Command Line Format --max-binlog-dump-events=#
    Config File Format max-binlog-dump-events
      Permitted Values
    Type numeric
    Default 0

    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
    Config File Format sporadic-binlog-dump-fail
      Permitted Values
    Type boolean
    Default FALSE

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

System variables used with the binary log.  The following system variables are used for controlling replication slave servers. Those that can be set are specified using SET. Server options used with replication slaves are listed earlier in this section.

  • log_slave_updates

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

  • max_binlog_cache_size

    Command Line Format --max_binlog_cache_size=#
    Config File Format max_binlog_cache_size
    Option Sets Variable Yes, max_binlog_cache_size
    Variable Name max_binlog_cache_size
    Variable Scope Global
    Dynamic Variable Yes

    If a multiple-statement 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 and default values are 4GB on 32-bit platforms and 16 PB (petabytes) on 64-bit platforms.

  • max_binlog_size

    Command Line Format --max_binlog_size=#
    Config File Format max_binlog_size
    Option Sets Variable Yes, max_binlog_size
    Variable Name max_binlog_size
    Variable Scope Global
    Dynamic Variable Yes
      Permitted Values
    Type numeric
    Default 1073741824
    Min Value 4096

    If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). You cannot set this variable to more than 1GB or to less than 4096 bytes. The 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 logs larger than max_binlog_size.

    If max_relay_log_size is 0, the value of max_binlog_size applies to relay logs as well.

  • sync_binlog

    Version Introduced 5.0.1
    Command Line Format --sync-binlog=#
    Config File Format sync_binlog
    Option Sets Variable Yes, sync_binlog
    Variable Name sync_binlog
    Variable Scope Global
    Dynamic Variable Yes
      Permitted Values
    Platform Bit Size 32
    Type numeric
    Default 0
    Range 0-4294967295
      Permitted Values
    Platform Bit Size 64
    Type numeric
    Default 0
    Range 0-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. 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).

    If the value of sync_binlog is 0 (the default), no extra flushing is done. The server relies on the operating system to flush the file contents occasionally as for any other file.