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 8192. See Section 16.2.1, “Replication Formats”.

Enables binary logging. With binary logging enabled, the
server logs all statements that change data to the binary
log, which is used for backup and replication. The binary
log is a sequence of files with a base name and numeric
extension. For information on the format and management of
the binary log, see Section 5.4.4, “The Binary Log”.

If you supply a value for the --log-bin
option, the value is used as the base name for the log
sequence. The server creates binary log files in sequence by
adding a numeric suffix to the base name. In MySQL 5.7, the
base name defaults to
host_name-bin,
using the name of the host machine. It is recommended that
you specify a base name, so that you can continue to use the
same binary log file names regardless of changes to the
default name.

The default location for binary log files is the data
directory. You can use the --log-bin option
to specify an alternative location, by adding a leading
absolute path name to the base name to specify a different
directory. When the server reads an entry from the binary
log index file, which tracks the binary log files that have
been used, it checks whether the entry contains a relative
path. If it does, the relative part of the path is replaced
with the absolute path set using the
--log-bin option. An absolute path recorded
in the binary log index file remains unchanged; in such a
case, the index file must be edited manually to enable a new
path or paths to be used. (In older versions of MySQL,
manual intervention was required whenever relocating the
binary log or relay log files.) (Bug #11745230, Bug #12133)

Setting this option causes the
log_bin system variable to
be set to ON (or 1),
and not to the base name. The binary log file base name and
any specified path are available as the
log_bin_basename system
variable.

If you specify the --log-bin option without
also specifying a
--server-id, the server is
not allowed to start. (Bug #11763963, Bug #56739)

The index file for binary log file names. See
Section 5.4.4, “The Binary Log”. If you omit the file name, and
if you did not specify one with
--log-bin, MySQL uses
host_name-bin.index
as the file name.

MySQL 5.7 uses Version 2 binary log row events,
which cannot be read by MySQL Server releases prior to MySQL
5.6.6. Setting this option to 1 causes
mysqld to write the binary log using
Version 1 logging events, which is the only version of
binary log events used in previous releases, and thus
produce binary logs that can be read by older slaves.
Setting --log-bin-use-v1-row-events to 0
(the default) causes mysqld to use
Version 2 binary log events.

--log-bin-use-v1-row-events is chiefly of
interest when setting up replication conflict detection and
resolution using NDB$EPOCH_TRANS() as the
conflict detection function, which requires Version 2 binary
log row events. Thus, this option and
--ndb-log-transaction-id are
not compatible.

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.6.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.
You should keep in mind that the format used to log a given
statement may not necessarily be the same as that indicated
by the value of
binlog_format. For example,
DDL statements such as CREATE
TABLE and ALTER
TABLE are always logged as statements, without
regard to the logging format in effect, so the following
statement-based rules for --binlog-do-db
always apply in determining whether or not the statement is
logged.

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

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.

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 that 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. You should keep in mind that the format used to log a
given statement may not necessarily be the same as that
indicated by the value of
binlog_format. For example,
DDL statements such as CREATE
TABLE and ALTER
TABLE are always logged as statements, without
regard to the logging format in effect, so the following
statement-based rules for
--binlog-ignore-db always apply in
determining whether or not the statement is logged.

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.

Prior to MySQL 5.7.2, this option caused any statements
containing fully qualified table names not to be logged if
there was no default database specified (that is, when
SELECTDATABASE() returned
NULL). In MySQL 5.7.2 and later, when
there is no default database, no
--binlog-ignore-db options are applied, and
such statements are always logged. (Bug #11829838, Bug
#60188)

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.

Checksum options.
MySQL supports reading and writing of binary log checksums.
These are enabled using the two options listed here:

Enabling this option causes the master to write checksums
for events written to the binary log. Set to
NONE to disable, or the name of the
algorithm to be used for generating checksums; currently,
only CRC32 checksums are supported, and CRC32 is the
default. You cannot change the setting for this option
within a transaction.

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.5, “Server System Variables”.

The size of the cache to hold changes to 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 transactions, you can increase this cache
size to get better 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.4.4, “The Binary Log”.

binlog_cache_size sets the size for the
transaction cache only; the size of the statement cache is
governed by the
binlog_stmt_cache_size
system variable.

When enabled, this variable causes the master to write a
checksum for each event in the binary log.
binlog_checksum supports the values
NONE (disabled) and
CRC32. The default is
CRC32. You cannot change the value of
binlog_checksum within a transaction.

When binlog_checksum is disabled (value
NONE), the server verifies that it is
writing only complete events to the binary log by writing
and checking the event length (rather than a checksum) for
each event.

Changing the value of this variable causes the binary log to
be rotated; checksums are always written to an entire binary
log file, and never to only part of one.

Setting this variable on the master to a value unrecognized
by the slave causes the slave to set its own
binlog_checksum value to
NONE, and to stop replication with an
error. (Bug #13553750, Bug #61096) If backward compatibility
with older slaves is a concern, you may want to set the
value explicitly to NONE.

Due to concurrency issues, a slave can become inconsistent
when a transaction contains updates to both transactional
and nontransactional tables. MySQL tries to preserve
causality among these statements by writing nontransactional
statements to the transaction cache, which is flushed upon
commit. However, problems arise when modifications done to
nontransactional tables on behalf of a transaction become
immediately visible to other connections because these
changes may not be written immediately into the binary log.

binlog_direct_non_transactional_updates
works only for statements that are replicated using the
statement-based binary logging format; that is,
it works only when the value of
binlog_format is
STATEMENT, or when
binlog_format is
MIXED and a given statement is being
replicated using the statement-based format. This variable
has no effect when the binary log format is
ROW, or when
binlog_format is set to
MIXED and a given statement is replicated
using the row-based format.

Important

Before enabling this variable, you must make certain that
there are no dependencies between transactional and
nontransactional 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.

This variable has no effect when the binary log format is
ROW or MIXED.

Controls what happens when the server encounters an error
such as not being able to write to, flush or synchronize the
binary log, which can cause the master's log to become
inconsistent and replication slaves to lose synchronization.

In MySQL 5.7.7 and later, this variable defaults to
ABORT_SERVER, which makes the server halt
logging and shut down whenever it encounters such an error
with the binary log. Upon server restart, all of the
previously prepared and binary logged transactions are
committed, while any transactions which were prepared but
not binary logged due to the error are aborted.

When binlog_error_action is set to
IGNORE_ERROR, if the server encounters
such an error it continues the ongoing transaction, logs the
error then halts logging, and continues performing updates.
To resume binary logging
log_bin must be enabled
again. This provides backward compatibility with older
versions of MySQL.

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.

Prior to MySQL 5.7.7, the default format was
STATEMENT. In MySQL 5.7.7 and later the
default is ROW.
Exception: In NDB Cluster, the default
is MIXED; statement-based replication is
not supported for NDB Cluster.

You must have the SUPER
privilege to set either the global or session
binlog_format value.

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.

Controls how many microseconds the binary log commit waits
before synchronizing the binary log file to disk. By default
binlog_group_commit_sync_delay
is set to 0, meaning that there is no delay. Setting
binlog_group_commit_sync_delay
to a microsecond delay enables more transactions to be
synchronized together to disk at once, reducing the overall
time to commit a group of transactions because the larger
groups require fewer time units per group.

Setting
binlog_group_commit_sync_delay
can increase the number of parallel committing transactions
on any server that has (or might have after a failover) a
replication slave, and therefore can increase parallel
execution on the replication slaves. To benefit from this
effect, the slave servers must have
slave_parallel_type=LOGICAL_CLOCK
set.
It is important to take into account both the master's
throughput and the slaves' throughput when you are tuning
the setting for
binlog_group_commit_sync_delay.

Setting
binlog_group_commit_sync_delay
can also reduce the number of fsync()
calls to the binary log on any server (master or slave) that
has a binary log.

Note that setting
binlog_group_commit_sync_delay
increases the latency of transactions on the server, which
might affect client applications. Also, on highly concurrent
workloads, it is possible for the delay to increase
contention and therefore reduce throughput. Typically, the
benefits of setting a delay outweigh the drawbacks, but
tuning should always be carried out to determine the optimal
setting.

When this variable is enabled on a master (the default),
transactions are externalized in the same order as they are
written to the binary log. If disabled, transactions may be
committed in parallel. In some cases, disabling this
variable might produce a performance increment.

For MySQL row-based replication, this variable determines
how row images are written to the binary log.

In MySQL row-based replication, each row change event
contains two images, a “before” image whose
columns are matched against when searching for the row to be
updated, and an “after” image containing the
changes. Normally, MySQL logs full rows (that is, all
columns) for both the before and after images. However, it
is not strictly necessary to include every column in both
images, and we can often save disk, memory, and network
usage by logging only those columns which are actually
required.

Note

When deleting a row, only the before image is logged,
since there are no changed values to propagate following
the deletion. When inserting a row, only the after image
is logged, since there is no existing row to be matched.
Only when updating a row are both the before and after
images required, and both written to the binary log.

For the before image, it is necessary only that the minimum
set of columns required to uniquely identify rows is logged.
If the table containing the row has a primary key, then only
the primary key column or columns are written to the binary
log. Otherwise, if the table has a unique key all of whose
columns are NOT NULL, then only the
columns in the unique key need be logged. (If the table has
neither a primary key nor a unique key without any
NULL columns, then all columns must be
used in the before image, and logged.) In the after image,
it is necessary to log only the columns which have actually
changed.

You can cause the server to log full or minimal rows using
the binlog_row_image system variable.
This variable actually takes one of three possible values,
as shown in the following list:

full: Log all columns in both the
before image and the after image.

minimal: Log only those columns in
the before image that are required to identify the row
to be changed; log only those columns in the after image
where a value was specified by the SQL statement, or
generated by auto-increment.

noblob: Log all columns (same as
full), except for
BLOB and
TEXT columns that are not
required to identify rows, or that have not changed.

Note

This variable is not supported by NDB Cluster; setting it
has no effect on the logging of
NDB tables.

The default value is full.

In MySQL 5.5 and earlier, full row images were always used
for both before images and after images. If you need to
replicate from a newer master to a slave running MySQL 5.5
or earlier, the master should always use this value.

When using minimal or
noblob, deletes and updates are
guaranteed to work correctly for a given table if and only
if the following conditions are true for both the source and
destination tables:

All columns must be present and in the same order; each
column must use the same data type as its counterpart in
the other table.

The tables must have identical primary key definitions.

(In other words, the tables must be identical with the
possible exception of indexes that are not part of the
tables' primary keys.)

If these conditions are not met, it is possible that the
primary key column values in the destination table may prove
insufficient to provide a unique match for a delete or
update. In this event, no warning or error is issued; the
master and slave silently diverge, thus breaking
consistency.

Setting this variable has no effect when the binary logging
format is STATEMENT. When
binlog_format is
MIXED, the setting for
binlog_row_image is applied to changes
that are logged using row-based format, but this setting no
effect on changes logged as statements.

Setting binlog_row_image on either the
global or session level does not cause an implicit commit;
this means that this variable can be changed while a
transaction is in progress without affecting the
transaction.

The
binlog_rows_query_log_events
system variable affects row-based logging only. When
enabled, it causes the MySQL Server to write informational
log events such as row query log events into its binary log.
This information can be used for debugging and related
purposes; such as obtaining the original query issued on the
master when it cannot be reconstructed from the row updates.

These events are normally ignored by MySQL programs reading
the binary log and so cause no issues when replicating or
restoring from backup. To view them, increase the verbosity
level by using mysqlbinlog's
--verbose option twice,
either as -vv or --verbose
--verbose.

This variable determines the size of the cache for the
binary log to hold nontransactional statements issued during
a transaction. Separate binary log transaction and statement
caches are 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 nontransactional statements
during transactions, you can increase this cache size to get
better performance. The
Binlog_stmt_cache_use and
Binlog_stmt_cache_disk_use
status variables can be useful for tuning the size of this
variable. See Section 5.4.4, “The Binary Log”.

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.

Holds the base name and path for the binary log files, which
can be set with the --log-bin
server option. In MySQL 5.7, the default base name is the
name of the host machine with the suffix
-bin. The default location is the data
directory.

Shows whether Version 2 binary logging is in use. A value of
1 shows that the server is writing the binary log using
Version 1 logging events (the only version of binary log
events used in previous releases), and thus producing a
binary log that can be read by older slaves. 0 indicates
that Version 2 binary log events are in use.

This variable is read-only. To switch between Version 1 and
Version 2 binary event binary logging, it is necessary to
restart mysqld with the
--log-bin-use-v1-row-events
option.

Other than when performing upgrades of NDB Cluster
Replication, --log-bin-use-v1-events is
chiefly of interest when setting up replication conflict
detection and resolution using
NDB$EPOCH_TRANS(), which requires Version
2 binary row event logging. Thus, this option and
--ndb-log-transaction-id are
not compatible.

Note

MySQL NDB Cluster 7.5 uses Version 2 binary log row events
by default. You should keep this mind when planning
upgrades or downgrades, and for setups using NDB Cluster
Replication.

Enabling this variable causes the master to examine
checksums when reading from the binary log.
master_verify_checksum is disabled by
default; in this case, the master uses the event length from
the binary log to verify events, so that only complete
events are read from the binary log.

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

max_binlog_cache_size sets the size for
the transaction cache only; the upper limit for the
statement cache is governed by the
max_binlog_stmt_cache_size
system variable.

The visibility to sessions of
max_binlog_cache_size matches that of the
binlog_cache_size system
variable; in other words, changing its value affects only
new sessions that are started after the value is changed.

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 nontransactional statements within a transaction require
more than this many bytes of memory, the server generates an
error. The minimum value is 4096. The maximum and default
values are 4GB on 32-bit platforms and 16EB (exabytes) on
64-bit platforms.

max_binlog_stmt_cache_size sets the size
for the statement cache only; the upper limit for the
transaction cache is governed exclusively by the
max_binlog_cache_size
system variable.

Controls the number of binary log commit groups to collect
before synchronizing the binary log to disk. When
sync_binlog=0, the binary
log is never synchronized to disk, and when
sync_binlog is set to a
value greater than 0 this number of binary log commit groups
is periodically synchronized to disk. When
sync_binlog=1, all
transactions are synchronized to the binary log before they
are committed. Therefore, even in the event of an unexpected
restart, any transactions that are missing from the binary
log are only in prepared state. This causes the
server's automatic recovery routine to roll back those
transactions. This guarantees that no transaction is lost
from the binary log, and is the safest option. However this
can have a negative impact on performance because of an
increased number of disk writes. Using a higher value
improves performance, but with the increased risk of data
loss.

When sync_binlog=0 or
sync_binlog is greater than
1, transactions are committed without having been
synchronized to disk. Therefore, in the event of a power
failure or operating system crash, it is possible that the
server has committed some transactions that have not been
synchronized to the binary log. Therefore it is impossible
for the recovery routine to recover these transactions and
they will be lost from the binary log.

Prior to MySQL 5.7.7, the default value of
sync_binlog was 0, which
configures no synchronizing to disk—in this case, the
server relies on the operating system to flush the binary
log's contents from time to time as for any other file.
MySQL 5.7.7 and later use a default value of 1, which is the
safest choice, but as noted above can impact performance.