学步园

mysql的二进制事务日志和其他的dbms的事务日志是类似的东西，是数据库备份、恢复、还原，高级的复制、镜像等功能的核心构件。

mysql的二进制事务日志是一系列有base_name.数字序列组成的。日志系列在日志大小超过max_binlog_size时自动产生——注意实际的二进制日志文件的大小可能超过该设置的大小，因为一个事务日志记录不能跨越文件。

mysql的二进制事务日志是独立于存储引擎的，和innodb事务存储引擎自身的事务日志不同

mysql的二进制事务日志是先于锁释放、事务commit进行记录的！记录且只记录 有效 的数据 更新！非事务表的数据更新是立即进行记录的！5.1.20后，某些系统变量的值如sql_mode、sql_auto_is_null等也进行记录，为了保持数据的一致！

mysql的二进制事务日志不是同步写入磁盘的，是缓存的；可以配置！其内存缓存不足时，使用磁盘临时文件。

 

设置二进制事务日志的记录的格式

命令、配置选项：【--】binlog-format={statement|row|mixed}　　

动态全局系统变量：binlog_format={statement|row|mixed}

动态会话系统变量：binlog_format={statement|row|mixed}　　--仅仅影响当前连接会话的记录格式

 

 

 

启用二进制事务日志：

命令、配置选项：【--】log-bin[=base_name]

 

 

截断二进制事务日志：

reset master;

purge binary logs;

 

记录二进制事务日志的判定规则：

第一步：是否设置了--binlog_do_db、--binlog_ignore_db 或两者？是的话进行下一步测试；　　--如果没有设置，记录所有数据库的事务

第二步：是否选择了（默认）数据库（use db_name）？是的话进行下一步测试；　　--如果没有设置，不记录事务日志，结束测试！

第三步：如果设置了--binlog-do-db，（默认）数据库是否匹配设置？是的话写事务日志，结束测试；否的话不记录事务日志，结束测试！

第三步：如果没有设置--binlog-do-db，而设置了--binlog-ignore-db，（默认）数据库是否匹配设置？是的话不写事务日志，结束测试；否的话记录事务日志，结束测试！　　--注意：--binlog-ignore-db选项在设置了--binlog-do-db时，不会起任何作用，形同虚设！

 

 

二进制事务日志的状态及调整

系统状态变量：binglog_cache_use：指示二进制日志系统缓存（内存或临时文件）中存储的事务日志数目

系统状态变量：binlog_cache_disk_use：指示二进制日志系统缓存使用的临时文件的大小

全局动态系统变量：binlog_cache_size、max_binlog_cache_size用来设置二进制日志的系统缓存，合理设置！

全局动态系统变量：sync_binlog=n：指示mysql待n次写入缓存后 才写入 磁盘。实际上，即使n=1，对于innodb来说仍有可能是数据不一致，这是需要设置--innodb-support-xa=1

 

附：

 

The Binary Log

The binary log contains all statements that update data. It also contains
statements that potentially could have updated it (for example, a DELETE which matched no rows), unless row-based logging
is used. Statements are stored in the form of “events”
that describe the modifications. The binary log also contains information about
how long each statement took that updated data. The binary log has two important
purposes:

• For replication, the binary log is used on master replication servers as a
  record of the statements to be sent to slave servers. The master server sends
  the events contained in its binary log to its slaves, which execute those events
  to make the same data changes that were made on the master. See Section 16.4,
  “Replication Implementation”.

• Certain data recovery operations require use of the binary log. After a
  backup file has been restored, the events in the binary log that were recorded
  after the backup was made are re-executed. These events bring databases up to
  date from the point of the backup. See Section 6.2.2, “Using Backups for
  Recovery”.

The binary log is not used for statements such as SELECT or SHOW that do not modify
data. If you want to log all statements (for example, to identify a problem
query), use the general query log. See Section 5.2.3, “The General Query Log”.

The binary log should be protected because logged statements might contain
passwords. See Section 5.5.6.1, “Administrator
Guidelines for Password Security”.

The format of the events recorded in the binary log is dependent on the
binary logging format. Three format types are supported, row-based logging,
statement-based logging and mixed-base logging. The binary logging format used
depends on the MySQL version. For more information on logging formats, see Section 5.2.4.1, “Binary Logging
Formats”.

MySQL Enterprise.  The binary log can also be used to
track significant DDL events. Analyzing the binary log in this way is an
integral part of the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

Running the server with the binary log enabled makes performance about 1%
slower. However, the benefits of the binary log for restore operations and in
allowing you to set up replication generally outweigh this minor performance
decrement.

When started with the --log-bin[=base_name] option, mysqld writes a log file containing all SQL
statements that update data (both DDL and DML statements). If no base_name value is given, the default name
is the value of the pid-file option (which by default
is the name of host machine) followed by -bin. If the
basename is given, but not as an absolute path name, the server writes the file
in the data directory. It is recommended that you specify a basename; see Section B.1.8.2, “Additional Known
Issues”, for the reason.

Note

From MySQL 5.1.18 through 5.1.22, “mysql” was used
when no base_name was specified. Also in
these versions, a path given as part of the --log-bin
options was treated as absolute rather than relative. The previous behaviors
were restored in MySQL 5.1.23. (See Bug#28603 and Bug#28597.)

If you supply an extension in the log name (for example, --log-bin=base_name.extension), the
extension is silently removed and ignored.

mysqld appends a numeric extension to the
binary log basename to generate binary log file names. The number increases each
time the server creates a new log file, thus creating an ordered series of
files. The server creates a new file in the series each time it starts or
flushes the logs. The server also creates a new binary log file automatically
when the current log's size reaches max_binlog_size. A binary log file may become larger
than max_binlog_size if you are using large transactions
because a transaction is written to the file in one piece, never split between
files.

To keep track of which binary log files have been used, mysqld also creates a binary log index file
that contains the names of all used binary log files. By default, this has the
same basename as the binary log file, with the extension '.index'. You can change the name of the binary log index
file with the --log-bin-index[=file_name] option. You should not
manually edit this file while mysqld is running; doing so would confuse mysqld.

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY
LOGS. See Section 12.5.6.6, “RESET
Syntax”, and Section 12.6.1.1, “PURGE BINARY LOGS Syntax”.

Writes to the binary log file and binary log index file are handled in the
same way as writes to MyISAM tables. See Section B.1.4.3, “How MySQL Handles a Full Disk”.

The binary log format has some known limitations that can affect recovery
from backups. See Section 16.3.1, “Replication Features
and Issues”.

Binary logging for stored routines and triggers is done as described in Section 19.6, “Binary Logging of Stored Programs”.

A replication slave server by default does not write to its own binary log
any data modifications that are received from the replication master. To log
these modifications, start the slave with the --log-slave-updates option (see also Section 16.1.3.3, “Replication Slave
Options and Variables”).

Evaluation of update selection options.  The server evaluates the
options for logging or ignoring updates to the binary log according to the
following rules:

1. Are there --binlog-do-db or --binlog-ignore-db rules?

   • No: Write the statement to the binary log and exit.

   • Yes: Go to the next step.

2. There are some rules (--binlog-do-db, --binlog-ignore-db, or both). Is there a default
   database (has any database been selected by USE?)?

   • No: Do not write the statement, and
     exit.

   • Yes: Go to the next step.

3. There is a default database. Are there some --binlog-do-db rules?

   • Yes: Does the default database match any of the --binlog-do-db rules?

     • Yes: Write the statement and exit.

     • No: Do not write the statement, and
       exit.

   • No: Go to the next step.

4. There are some --binlog-ignore-db rules. Does the default database
   match any of the --binlog-ignore-db rules?

   • Yes: Do not write the statement, and exit.

   • No: Write the query and exit.

Important

An exception is made in the rules just given for the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements (see Section 16.1.3.4, “Binary Log
Options and Variables”). In those cases, the database being created, altered, or dropped replaces the default
database when determining whether to log or ignore updates.

For example, a slave running with only --binlog-do-db=sales does not write to the binary log
any statement for which the default database is different from sales (in other words, --binlog-do-db can sometimes mean “ignore other databases”).

If you are using replication, you should not delete old binary log files
until you are sure that no slave still needs to use them. For example, if your
slaves never run more than three days behind, once a day you can execute mysqladmin
flush-logs on the master and then remove any logs that are
more than three days old. You can remove the files manually, but it is
preferable to use PURGE BINARY
LOGS, which also safely updates the binary log index file for you
(and which can take a date argument). See Section 12.6.1.1, “PURGE BINARY LOGS Syntax”.

A client that has the SUPER privilege can disable binary logging of its own
statements by using a SET sql_log_bin=0 statement.
See Section 5.1.4, “Session System
Variables”.

You can display the contents of binary log files with the mysqlbinlog utility. This can be useful when
you want to reprocess statements in the log. For example, you can update a MySQL
server from the binary log as follows:

shell> mysqlbinlog log_file | mysql -h server_name

See Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log
Files”, for more information on the mysqlbinlog utility and how to use it. mysqlbinlog also can be used with relay log
files because they are written using the same format as binary log files.

Binary logging is done immediately after a statement completes but before any
locks are released or any commit is done. This ensures that the log is logged in
execution order.

Updates to non-transactional tables are stored in the binary log immediately
after execution. In MySQL 5.1.22 and earlier versions of MySQL 5.1, an UPDATE statement using a stored function that modified
a non-transactional table was not logged if it failed, and an INSERT ... ON
DUPLICATE KEY UPDATE statement that encountered a duplicate key
constraint — but which did not actually change any data — was not logged.
Beginning with MySQL 5.1.23, both of these statements are written to the binary
log. (Bug#23333)

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as BDB or InnoDB tables are cached
until a COMMIT statement is
received by the server. At that point, mysqld writes the entire transaction to the
binary log before the COMMIT is executed.
When the thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement
is bigger than this, the thread opens a temporary file to store the transaction.
The temporary file is deleted when the thread ends.

Modifications to non-transactional tables cannot be rolled back. If a
transaction that is rolled back includes modifications to non-transactional
tables, the entire transaction is logged with a ROLLBACK statement at
the end to ensure that the modifications to those tables are replicated.

The Binlog_cache_use status variable shows the number of
transactions that used this buffer (and possibly a temporary file) for storing
statements. The Binlog_cache_disk_use status variable shows how many of
those transactions actually had to use a temporary file. These two variables can
be used for tuning binlog_cache_size to a large enough value that avoids
the use of temporary files.

The max_binlog_cache_size system variable (default 4GB,
which is also the maximum) can be used to restrict the total size used to cache
a multiple-statement transaction. If a transaction is larger than this many
bytes, it fails and rolls back. The minimum value is 4096.

If you are using the binary log and row based logging, concurrent inserts are
converted to normal inserts for CREATE ... SELECT or
INSERT ...
SELECT statement. This is done to ensure that you can re-create an
exact copy of your tables by applying the log during a backup operation. If you
are using statement based logging then the original statement is written to the
log.

Note that the binary log format is different in MySQL 5.1 from previous
versions of MySQL, due to enhancements in replication. See Section 16.3.2, “Replication
Compatibility Between MySQL Versions”.

By default, the binary log is not synchronized to disk at each write. So if
the operating system or machine (not only the MySQL server) crashes, there is a
chance that the last statements of the binary log are lost. To prevent this, you
can make the binary log be synchronized to disk after every N writes to the binary log, with the sync_binlog
system variable. See Section 5.1.3, “Server System
Variables”. 1 is the safest value for sync_binlog,
but also the slowest. Even with sync_binlog set to 1, there is still the chance of an
inconsistency between the table content and binary log content in case of a
crash. For example, if you are using InnoDB tables
and the MySQL server processes a COMMIT statement, it
writes the whole transaction to the binary log and then commits this transaction
into InnoDB. If the server crashes between those two
operations, the transaction is rolled back by InnoDB
at restart but still exists in the binary log. To resolve this, you should set
--innodb_support_xa to 1. Although this option is
related to the support of XA transactions in InnoDB, it also ensures that the
binary log and InnoDB data files are synchronized.

For this option to provide a greater degree of safety, the MySQL server
should also be configured to synchronize the binary log and the InnoDB logs to disk at every transaction. The InnoDB logs are synchronized by default, and sync_binlog=1 can be used to synchronize the binary log.
The effect of this option is that at restart after a crash, after doing a
rollback of transactions, the MySQL server cuts rolled back InnoDB transactions from the binary log. This ensures that
the binary log reflects the exact data of InnoDB
tables, and so, that the slave remains in synchrony with the master (not
receiving a statement which has been rolled back).

If the MySQL server discovers at crash recovery that the binary log is
shorter than it should have been, it lacks at least one successfully committed
InnoDB transaction. This should not happen if sync_binlog=1 and the disk/file system do an actual sync
when they are requested to (some don't), so the server prints an error message
The binary log <name> is shorter than its expected
size. In this case, this binary log is not correct and replication should
be restarted from a fresh snapshot of the master's data.

For MySQL 5.1.20 and later (and MySQL 5.0.46 and later for backward
compatibility), the session values of the following system variables are written
to the binary log and honored by the replication slave when parsing the binary
log:

• sql_mode

• foreign_key_checks

• unique_checks

• character_set_client

• collation_connection

• collation_database

• collation_server

• sql_auto_is_null

5.2.4.1. Binary Logging
Formats

A number of different logging formats are used to record information in the
binary log. The exact format employed depends on the version of MySQL being
used. There are three logging formats:

• Replication capabilities in MySQL originally were based on propagation of SQL
  statements from master to slave. This is called statement-based logging. You can cause this
  format to be used by starting the server with --binlog-format=STATEMENT.

• In row-based logging, the master writes
  events to the binary log that indicate how individual table rows are affected.
  You can cause the server to use row-based logging by starting it with --binlog-format=ROW.

  Support for row-based logging was added in MySQL 5.1.5.

• A third option is also available: mixed
  logging. With mixed logging, statement-based logging is used by
  default, but the logging mode switches automatically to row-based in certain
  cases as described below. You can cause MySQL to use mixed logging explicitly by
  starting mysqld with the option --binlog-format=MIXED.

  Mixed logging is available beginning with MySQL 5.1.8. In MySQL 5.1.12, it
  become the default logging mode; in 5.1.29, the default was changed back to
  STATEMENT.

  Starting with MySQL 5.1.20, the logging format can also be set or limited by
  the storage engine being used. This helps to eliminate issues when logging, and
  more specifically replicating, certain statements between a master and slave
  which are using different storage engines.

With MySQL's classic statement-based replication, there may be issues with
replicating non-deterministic statements. In deciding whether or not a given
statement is safe for statement-based replication, MySQL determines whether it
can guarantee that the statement can be replicated using the statement-based
format. If MySQL cannot make this guarantee, it marks the statement as
potentially unreliable and issues the warning, Statement
may not be safe to log in statement format.

Prior to MySQL 5.1.36, this warning read, Statement is
not safe to log in statement format. (Bug#42415)

You can avoid these issues by using MySQL's row-based replication instead.

5.2.4.2. Setting The Binary Log
Format

The default binary logging format depends on the version of MySQL you are
using:

• For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based
  logging is used by default.

• For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.

Exception.  For all MySQL Cluster releases using version 6.1 or later
of the NDBCLUSTER storage engine (even those releases
based on MySQL 5.1.29 and later), the default binary log format is MIXED. See Section 17.9, “MySQL Cluster Replication”.

You can force the replication format by starting the MySQL server with --binlog-format=type. When set, all replication
slaves connecting to the server will read the events according to this setting.
The supported values for type are:

• ROW causes replication to be row-based.

• STATEMENT causes replication to be
  statement-based. This is the default for MySQL 5.1.11 and earlier, and MySQL
  5.1.29 and later.

• MIXED causes replication to use mixed format. This
  is the default for MySQL 5.1.12 to 5.1.28.

The logging format also can be switched at runtime. To specify the format
globally for all clients, set the global value of the binlog_format system variable. (To change the global
value, you must have need the SUPER privilege. This is also true for the SESSION value as of MySQL 5.1.29.)

To switch to statement-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 1;

To switch to row-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 2;

To switch to mixed format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 3;

An individual client can control the logging format for its own statements by
setting the session value of binlog_format. For example:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

In addition to switching the logging format manually, a slave server may
switch the format automatically. This
happens when the server is running in either STATEMENT or MIXED format and
encounters a row in the binary log that is written in ROW logging format. In that case, the slave switches to
row-based replication temporarily for that event, and switches back to the
previous format afterwards.

There are two reasons why you might want to set replication logging on a
per-connection basis:

• A thread that makes many small changes to the database might want to use
  row-based logging. A thread that performs updates that match many rows in the
  WHERE clause might want to use statement-based
  logging because it will be more efficient to log a few statements than many
  rows.

• Some statements require a lot of execution time on the master, but result in
  just a few rows being modified. It might therefore be beneficial to replicate
  them using row-based logging.

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 any of these cases results in an error.

Switching the replication format at runtime is not recommended when any
temporary tables exist, because temporary tables are logged only when using
statement-based replication, whereas with row-based replication they are not
logged. With mixed replication, temporary tables are usually logged; exceptions
happen with user-defined functions (UDFs) and with the UUID() function.

With the binlog format set to ROW, many changes
are written to the binary log using the row-based format. Some changes, however,
still use the statement-based format. Examples include all DDL (data definition
language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

The --binlog-row-event-max-size option is available for
servers that are capable of row-based replication. Rows are stored into the
binary log in chunks having a size in bytes not exceeding the value of this
option. The value must be a multiple of 256. The default value is 1024.

Warning

When using statement-based logging in a
replication scenario, it is possible for the data on the master and slave to
become different if a statement is designed in such a way that the data
modification is non-deterministic; that is, it is left
to the will of the query optimizer. In general, this is not a good practice even
outside of replication. For a detailed explanation of this issue, see Section B.1.8.2, “Additional Known
Issues”.

5.2.4.3. Mixed Binary Logging
Format

When running in MIXED mode, automatic switching
from statement-based to row-based replication takes place under the following
conditions:

• When a DML statement updates an NDBCLUSTER table

• When a function contains UUID()

• When 2 or more tables with AUTO_INCREMENT columns
  are updated

• When any INSERT DELAYED
  is executed

• When the body of a view requires row-based replication, the statement
  creating the view also uses it — for example, this occurs when the statement
  creating a view uses the UUID() function

• When a call to a UDF is involved

• If a statement is logged by row and the client that executed the statement
  has any temporary tables, then logging by row is used for all subsequent
  statements (except for those accessing temporary tables) until all temporary
  tables in use by that client are dropped

  This is true whether or not any temporary tables are actually logged

  Temporary tables cannot be logged using the row-based format; thus, once
  row-based logging is used, all subsequent statements using that table are
  unsafe, and we approximate this condition by treating all statements made by
  that client as unsafe until the client no longer holds any temporary tables

• Beginning with MySQL 5.1.23:

  • When FOUND_ROWS() or ROW_COUNT() is used (Bug#12092, Bug#30244)

  • When USER(), CURRENT_USER(), or CURRENT_USER is used (Bug#28086)

• Beginning with MySQL 5.1.24, when a statement refers to one or more system
  variables. (Bug#31168)

• Beginning with MySQL 5.1.34, when the LOAD_FILE() function is used. (Bug#39701)

Note

Starting with MySQL 5.1.20 a warning is generated if you try to log execute a
statement in statement-logging mode that should be logged in row-logging mode.
The warning is shown both in the client (in the output of SHOW WARNINGS) and through the mysqld error log. A warning is added to the SHOW WARNINGS table each time a statement is executed.
However, only the first statement that generated the warning for each client
session is logged to the mysqld error log to prevent
flooding the error log.

Starting with MySQL 5.1.20, in addition to the decisions above, individual
engines can also determine the logging format used when information in a table
is updated. The following table lists the logging formats supported by each
storage engine:

Storage Engine	Row Logging Supported	Statement Logging Supported
ARCHIVE	Yes	Yes
BLACKHOLE	No	Yes
CSV	Yes	Yes
EXAMPLE	Yes	No
FEDERATED	Yes	Yes
HEAP	Yes	Yes
InnoDB	Yes	Yes
MyISAM	Yes	Yes
MERGE	Yes	Yes
NDBCLUSTER	Yes	No

A given storage engine can support either or both logging formats; the
logging capabilities of an individual engine can be further defined as follows:

• If an engine supports row-based logging, then the engine is said to be row-logging capable.

• If an engine supports statement-based logging, then the engine is said to be
  statement-logging capable.

When determining the logging mode to be used, the capabilities of all the
tables affected by the event are combined. The set of affected tables is then
marked according to these rules:

• A set of tables is defined as row logging restricted
  if the tables are row logging capable but not statement logging capable.

• A set of tables is defined as statement logging
  restricted if the tables are statement logging capable but not row logging
  capable.

Once the determination of the possible logging formats required by the
statement is complete it is compared to the current binlog_format setting. The following table is used to
decide how the information is recorded in the binary log or, if appropriate,
whether an error is raised. In the table, a safe operation is defined as one
that is deterministic. A number of rules decide whether the statement is
deterministic or not, as shown in the following table (where RLC stands for “row-logging
capable” and SLC stands for
“statement-logging capable”).

Condition				Action
Safe/unsafe	binlog_format	RLC	SLC	Error/Warning	Logged as
Safe	STATEMENT	N	N	Error: not loggable
Safe	STATEMENT	N	Y		STATEMENT
Safe	STATEMENT	Y	N	Error: not loggable
Safe	STATEMENT	Y	Y		STATEMENT
Safe	MIXED	N	N	Error: not loggable
Safe	MIXED	N	Y		STATEMENT
Safe	MIXED	Y	N		ROW
Safe	MIXED	Y	Y		STATEMENT
Safe	ROW	N	N	Error: not loggable
Safe	ROW	N	Y	Error: not loggable
Safe	ROW	Y	N		ROW
Safe	ROW	Y	Y		ROW
Unsafe	STATEMENT	N	N	Error: not loggable
Unsafe	STATEMENT	N	Y	Warning: unsafe	STATEMENT
Unsafe	STATEMENT	Y	N	Error: not loggable
Unsafe	STATEMENT	Y	Y	Warning: unsafe	STATEMENT
Unsafe	MIXED	N	N	Error: not loggable
Unsafe	MIXED	N	Y	Error: not loggable
Unsafe	MIXED	Y	N		ROW
Unsafe	MIXED	Y	Y		ROW
Unsafe	ROW	N	N	Error: not loggable
Unsafe	ROW	N	Y	Error: not loggable
Unsafe	ROW	Y	N		ROW
Unsafe	ROW	Y	Y		ROW

When a warning is produced by the determination, a standard MySQL warning is
produced (and is available using SHOW WARNINGS).
The information is also written to the mysqld error log. Only one error for each
error instance per client connection is logged. The log message will include the
SQL statement that was attempted.

If a slave server was started with --log-warnings enabled, the slave prints messages to the
error log to provide information about its status, such as the binary log and
relay log coordinates where it starts its job, when it is switching to another
relay log, when it reconnects after a disconnect, and so forth.

5.2.4.4. Logging Format
for Changes to mysql Database
Tables

The contents of the grant tables in the mysql
database can be modified directly (for example, with INSERT or DELETE) or indirectly
(for example, with GRANT or CREATE USER). As of MySQL 5.1.17, statements that
affect mysql database tables are written to the
binary log using the following rules:

• Data manipulation statements that change data in mysql database tables directly are logged according to the
  setting of the binlog_format system variable. This pertains to
  statements such as INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE,
  SELECT, and TRUNCATE.

• Statements that change the mysql database
  indirectly are logged as statements regardless of the value of binlog_format. This pertains to statements such as GRANT, REVOKE, SET PASSWORD, RENAME USER,
  CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all
  forms).

CREATE TABLE ... SELECT is a combination of data
definition and data manipulation. The CREATE TABLE part
is logged using statement format and the SELECT part is logged
according to the value of binlog_format.