16.1.2.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.3, “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.

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

  • --binlog-do-db=db_name

    Command-Line Format--binlog-do-db=name
    Permitted ValuesTypestring

    This option affects binary logging in a manner similar to the way that --replicate-do-db affects replication.

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

    Important

    To log multiple databases, use this option multiple times, specifying the option once for each database to be logged. 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.

  • --binlog-ignore-db=db_name

    Command-Line Format--binlog-ignore-db=name
    Permitted ValuesTypestring

    This option affects binary logging in a manner similar to the way that --replicate-ignore-db affects replication.

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

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=#
    Permitted ValuesTypenumeric
    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
    Permitted ValuesTypeboolean
    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”.

  • log_bin

    System VariableNamelog_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.3, “The Binary Log”.

  • log_slave_updates

    Command-Line Format--log-slave-updates
    System VariableNamelog_slave_updates
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    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 16.1.2.3, “Replication Slave Options and Variables”.

  • max_binlog_cache_size

    Command-Line Format--max_binlog_cache_size=#
    System VariableNamemax_binlog_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typenumeric
    Default4294967295
    Min Value4096
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default18446744073709547520
    Min Value4096
    Max Value18446744073709547520

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

    In MySQL 5.0, a change in max_binlog_cache_size takes immediate effect for all active sessions.

  • max_binlog_size

    Command-Line Format--max_binlog_size=#
    System VariableNamemax_binlog_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default1073741824
    Min Value4096
    Max Value1073741824

    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.

  • sync_binlog

    Introduced5.0.1
    Command-Line Format--sync-binlog=#
    System VariableNamesync_binlog
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typenumeric
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default0
    Min Value0
    Max Value18446744073709547520

    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.