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

16.1.3.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.4, “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.4, “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.

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

    Version Introduced 5.1.5
    Command Line Format --binlog-row-event-max-size=#
    Config File Format binlog-row-event-max-size
    Permitted Values
    Platform Bit Size 32
    Type numeric
    Default 1024
    Range 256-4294967295
    Permitted Values
    Platform Bit Size 64
    Type numeric
    Default 1024
    Range 256-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 16.1.2, “Replication Formats”. This option was added in MySQL 5.1.5.

  • --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.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.1.8.2, “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.4, “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

      This option affects binary logging in the same manner 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.

      Statement-based logging.  Only those statements where the default database (that is, the one selected by USE) is db_name are written to the binary log. 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, if you supply a comma separated list then the list will be treated as the name of a single database.

      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.

      Row-based logging.  Logging is restricted to database db_name. Only changes to tables belonging to db_name are logged; the current database has no effect on this. For example, suppose that the server is started with --replicate-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 february table in the sales database is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, the following statements are not logged when using the row-based logging format and --binlog-do-db=sales: 

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

      Even if the statement USE prices were changed to USE sales, the UPDATE statement's effects would still not be written to the binary log.

      Another important difference in how --binlog-do-db is handled when using the statement-based logging format as opposed to the row-based format occurs with regard to statements that refer to multiple databases. Suppose 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, then the updates to both tables are written to the binary log. However, when using the row-based format, only the changes to table1 logged; since table2 is in a different database, 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 would not be written to the binary log when using statement-based logging. However, if using row-based logging, the UPDATE would change table1, but not table2 — in other words, only tables in the database named by --binlog-do-db are changed, and the choice of current database has no effect on this behavior.

    • --binlog-ignore-db=db_name

      This option affects binary logging in the same manner 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.

      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.

      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, if you supply a comma separated list then the list will be treated as the name of a single database.

      You should not use this option if you are using cross-database updates and you do not want these updates to be logged.

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

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

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

    Note

    Previously, this option was known as --log-bin-trust-routine-creators, which is now deprecated.

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.

  • binlog_cache_size

    Command Line Format --binlog_cache_size=#
    Config File Format binlog_cache_size
    Option Sets Variable Yes, binlog_cache_size
    Variable Name binlog_cache_size
    Variable Scope Global
    Dynamic Variable Yes
    Permitted Values
    Platform Bit Size 32
    Type numeric
    Default 32768
    Range 4096-4294967295
    Permitted Values
    Platform Bit Size 64
    Type numeric
    Default 32768
    Range 4096-18446744073709547520

    The size of the cache to hold the SQL statements for 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, multiple-statement transactions, you can increase this cache size to get more 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”.

    MySQL Enterprise.  For recommendations on the optimum setting for binlog_cache_size subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • binlog_format

    Version Introduced 5.1.5
    Command Line Format --binlog-format
    Config File Format binlog-format
    Option Sets Variable Yes, binlog_format
    Variable Name binlog_format
    Variable Scope Both
    Dynamic Variable Yes
    Permitted Values (>= 5.1.5, <= 5.1.7)
    Type enumeration
    Default STATEMENT
    Valid Values ROW, STATEMENT
    Permitted Values (>= 5.1.8, <= 5.1.11)
    Type enumeration
    Default STATEMENT
    Valid Values ROW, STATEMENT, MIXED
    Permitted Values (>= 5.1.12, <= 5.1.28)
    Type enumeration
    Default MIXED
    Valid Values ROW, STATEMENT, MIXED
    Permitted Values (>= 5.1.29)
    Type enumeration
    Default STATEMENT
    Valid Values ROW, STATEMENT, MIXED

    This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED. binlog_format is set by the --binlog-format option at startup, or by the binlog_format variable at runtime.

    You must have the SUPER privilege to set this variable, which (unlike with most system variables) is true as of MySQL 5.1.29 even for the session value. See Section 16.1.2, “Replication Formats”.

    The startup variable was added in MySQL 5.1.5, and the runtime variable in MySQL 5.1.8. MIXED was added in MySQL 5.1.8.

    The rules governing when changing this variable takes effect and how long the effect lasts are the same as for other MySQL server system variables. See Section 12.5.4, “SET Syntax”, for more information.

    STATEMENT was used by default prior to MySQL 5.1.12; in MySQL 5.1.12, the default was changed to MIXED. In MySQL 5.1.29, the default was changed back to STATEMENT.

    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.

    As with other global variables, to set binlog_format globally, you must have the SUPER privilege. Starting with MySQL 5.1.29, you must also have the SUPER privilege to set binlog_format on the session level. (Bug#39106)

    There are exceptions when you cannot switch the replication format at runtime:

    • From within a stored function or a trigger.

    • If the NDBCLUSTER storage engine is enabled.

    • If the session is currently in row-based replication mode and has open temporary tables.

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

    Before MySQL 5.1.8, switching to row-based replication format would implicitly set --log-bin-trust-function-creators=1 and --innodb_locks_unsafe_for_binlog. MySQL 5.1.8 and later no longer implicitly set these options when row-based replication is used.

    The binlog format affects the behavior of the following server options:

    These effects are discussed in detail in the descriptions of the individual options.

  • 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
    Permitted Values (<= 5.1.35)
    Type numeric
    Default 4294967295
    Range 4096-4294967295
    Permitted Values (>= 5.1.36, <= 5.1.99)
    Type numeric
    Default 18446744073709547520
    Range 4096-18446744073709547520

    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

    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 — in this case, the server relies on the operating system to flush the binary log's contents from 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).