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. System variables used with binary logging are discussed later in this section.
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.
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.5.8.2, “Additional Known Issues”, for the reason).
Otherwise, MySQL uses
as the basename.
host_name
-bin
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 did not specify one with
--log-bin
, MySQL uses
as the file name.
host_name
-bin.index
--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 |
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 19.6, “Binary Logging of Stored Programs”.
Previously, this option was known as
--log-bin-trust-routine-creators
, which is
now deprecated.
--binlog-direct-non-transactional-updates[={0|1}]
Version Introduced | 5.1.44 | |
Command-Line Format | --binlog-direct-non-transactional-updates |
|
Config-File Format | binlog-direct-non-transactional-updates |
|
Permitted Values | ||
Type | boolean |
|
Default | TRUE |
Due to concurrency issues, a slave can become inconsistent when a transaction contains updates to both transactional and non-transactional tables. MySQL tries to preserve causality among these statements by writing non-transactional statements to the transaction cache, which is flushed upon commit. However, problems arise when modifications done to non-transactional 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.1.44,
--binlog-direct-non-transactional-updates
offers one possible workaround to this issue. When this option
is used, it causes updates to non-transactional tables using
the statement-based logging format to be written directly to
the binary log, rather than to the transaction cache. This
does not affect updates made using the row-based logging mode.
Before using this option, you must make certain that there
are no dependencies between transactional and
non-transactional 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.
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.3.3, “Replication Slave Options and Variables”.
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.
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
to be logged while a different database
(or no database) is selected.
some_db.some_table
SET
foo='bar'
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.
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 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.
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.
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, 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.
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.
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 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.
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.
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
. 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.
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.
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
.
You must have the SUPER
privilege to set the global
binlog_format
value. Starting
with MySQL 5.1.29, you must have the
SUPER
privilege to set either
the global or session
binlog_format
value.
(Bug#39106)
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 12.4.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 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
.
This does not occur for MySQL 5.1.8 and later.
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.
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) | ||
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. platforms. As of MySQL 5.1.36, the maximum value is 4GB on all platforms.
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 |
|
Range | 4096-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.
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).
User Comments
Add your own comment.