MySQL - binlog format
binlog_format
This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED. See Section 16.1.2, “Replication Formats”. binlog_format is set by the --binlog-format option at startup, or by the binlog_format 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.
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, “SETSyntax”, 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:
| Command-Line Format | --binlog-format=format |
| Option-File Format | binlog-format=format |
| Option Sets Variable | Yes, binlog_format |
| Variable Name | binlog_format |
| Variable Scope | Global, Session |
| Dynamic Variable | Yes |
| Permitted Values | |
| Type | enumeration |
| Default | STATEMENT |
| Valid Values | ROW STATEMENT MIXED |
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.
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, “SETSyntax”, 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 NDBCLUSTER storage engine is enabled.
- 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.
- --replicate-do-db
- --replicate-ignore-db
- --binlog-do-db
- --binlog-ignore-db
These effects are discussed in detail in the descriptions of the individual options.
Comments