Movatterモバイル変換

You can select the binary logging format explicitly by starting the MySQL server with--binlog-format=type. The supported values fortype are:

Setting the binary logging format does not activate binary logging for the server. The setting only takes effect when binary logging is enabled on the server, which is the case when thelog_bin system variable is set toON. In MySQL 5.7, binary logging is not enabled by default, and you enable it using the--log-bin option.

The logging format also can be switched at runtime, although note that there are a number of situations in which you cannot do this, as discussed later in this section. Set the global value of thebinlog_format system variable to specify the format for clients that connect subsequent to the change:

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

An individual client can control the logging format for its own statements by setting the session value ofbinlog_format:

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

Changing the globalbinlog_format value requires privileges sufficient to set global system variables. Changing the sessionbinlog_format value requires privileges sufficient to set restricted session system variables. SeeSection 5.1.8.1, “System Variable Privileges”.

There are several reasons why a client might want to set binary logging on a per-session basis:

There are exceptions when you cannot switch the replication format at runtime:

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 loadable functions and with theUUID() function.

Switching the replication format while replication is ongoing can also cause issues. Each MySQL Server can set its own and only its own binary logging format (true whetherbinlog_format is set with global or session scope). This means that changing the logging format on a replication source server does not cause a replica to change its logging format to match. When usingSTATEMENT mode, thebinlog_format system variable is not replicated. When usingMIXED orROW logging mode, it is replicated but is ignored by the replica.

A replica is not able to convert binary log entries received inROW logging format toSTATEMENT format for use in its own binary log. The replica must therefore useROW orMIXED format if the source does. Changing the binary logging format on the source fromSTATEMENT toROW orMIXED while replication is ongoing to a replica withSTATEMENT format can cause replication to fail with errors such asError executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.' Changing the binary logging format on the replica toSTATEMENT format when the source is still usingMIXED orROW format also causes the same type of replication failure. To change the format safely, you must stop replication and ensure that the same change is made on both the source and the replica.

If you are usingInnoDB tables and the transaction isolation level isREAD COMMITTED orREAD UNCOMMITTED, only row-based logging can be used. It ispossible to change the logging format toSTATEMENT, but doing so at runtime leads very rapidly to errors becauseInnoDB can no longer perform inserts.

With the binary log format set toROW, 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 asCREATE TABLE,ALTER TABLE, orDROP 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 8192.