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.

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.3.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.4, “Open Issues in MySQL”,
for the reason). Otherwise, MySQL uses
host_name-bin
as the basename.

The index file for binary log file names. See
Section 5.3.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.

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

This option affects binary logging in a manner similar to the
way that --replicate-do-db
affects replication.

Tell the server to restrict binary logging to updates for
which the default database is
db_name (that is, the database
selected by USE). All other
databases that are not explicitly mentioned are ignored. If
you use this option, you should ensure that you do updates
only in the default database.

There is an exception to this for CREATE
DATABASE, ALTER
DATABASE, and DROP
DATABASE statements. The server uses the database
named in the statement (not the default database) to decide
whether it should log the statement.

An example of what does not work as you might expect: 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.

Important

To log multiple databases, use this option multiple times,
specifying the option once for each database to be logged.
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.

This option affects binary logging in a manner similar to the
way that --replicate-ignore-db
affects replication.

Tell the server to suppress binary logging of updates for
which the default database is
db_name (that is, the database
selected by USE). If you use
this option, you should ensure that you do updates only in the
default database.

An example of what does not work as you might expect: If the
server is started with
binlog-ignore-db=sales, and you run
USE prices; UPDATE sales.january SET amount = amount
+ 1000;, this statement is
written into the binary log.

Important

To ignore multiple databases, use this option multiple
times, specifying the option once for each database to be
ignored. 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.

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.

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.

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.

Whether updates received by a slave server from a master
server should be logged to the slave's own binary log. Binary
logging must be enabled on the slave for this variable to have
any effect. This variable was added in MySQL 3.23.17. See
Section 14.8.3, “Replication Slave Options and Variables”.

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. This variable was added in MySQL 3.23.29.

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 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. 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). This variable was
added in MySQL 4.1.3.

If the value of sync_binlog
is 0 (the default), no extra flushing is done. The server
relies on the operating system to flush the file contents
occasionally as for any other file.