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.
Version Introduced | 5.1.5 | ||||||||
Command Line Format | --binlog-row-event-max-size=# |
||||||||
Config File Format | binlog-row-event-max-size |
||||||||
Permitted Values |
|
||||||||
Permitted Values |
|
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 |
|
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
as the basename.
host_name
-bin
Command Line Format | --log-bin-index=name |
||||
Config File Format | log-bin-index |
||||
Permitted Values |
|
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
as the file name.
host_name
-bin.index
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.
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
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, 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.
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 |
|
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”.
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.
Command Line Format | --max-binlog-dump-events=# |
||||
Config File Format | max-binlog-dump-events |
||||
Permitted Values |
|
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 |
|
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.
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 |
|
||||||||
Permitted Values |
|
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) |
|
||||||
Permitted Values (>= 5.1.8, <= 5.1.11) |
|
||||||
Permitted Values (>= 5.1.12, <= 5.1.28) |
|
||||||
Permitted Values (>= 5.1.29) |
|
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.
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) |
|
||||||
Permitted Values (>= 5.1.36, <= 5.1.99) |
|
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.
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 |
|
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.
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 |
|
||||||||
Permitted Values |
|
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.