Rechercher dans le manuel MySQL
5.4.4.2 Setting The Binary Log Format
You can select the binary logging format explicitly by starting
the MySQL server with
--binlog-format=
.
The supported values for type
type
are:
STATEMENT
causes logging to be statement based.ROW
causes logging to be row based. This is the default.MIXED
causes logging to use mixed format.
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 the binlog_format
system variable to specify the format for clients that connect
subsequent to the change:
An individual client can control the logging format for its own
statements by setting the session value of
binlog_format
:
Changing the global
binlog_format
value requires
privileges sufficient to set global system variables. Changing
the session binlog_format
value
requires privileges sufficient to set restricted session system
variables. See Section 5.1.9.1, “System Variable Privileges”.
There are several reasons why a client might want to set binary logging on a per-session basis:
A session that makes many small changes to the database might want to use row-based logging.
A session 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:
The replication format cannot be changed from within a stored function or a trigger.
If the
NDB
storage engine is enabled.If a session has open temporary tables, the replication format cannot be changed for the session (
SET @@SESSION.binlog_format
).If any replication channel has open temporary tables, the replication format cannot be changed globally (
SET @@GLOBAL.binlog_format
orSET @@PERSIST.binlog_format
).If any replication channel applier thread is currently running, the replication format cannot be changed globally (
SET @@GLOBAL.binlog_format
orSET @@PERSIST.binlog_format
).
Trying to switch the replication format in any of these cases
(or attempting to set the current replication format) results in
an error. You can, however, use PERSIST_ONLY
(SET @@PERSIST_ONLY.binlog_format
) to change
the replication format at any time, because this action does not
modify the runtime global system variable value, and takes
effect only after a server restart.
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 and mixed replication, they are not logged.
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 whether
binlog_format
is set with
global or session scope). This means that changing the logging
format on a replication master does not cause a slave to change
its logging format to match. When using
STATEMENT
mode, the
binlog_format
system variable
is not replicated. When using MIXED
or
ROW
logging mode, it is replicated but is
ignored by the slave.
A replication slave is not able to convert binary log entries
received in ROW
logging format to
STATEMENT
format for use in its own binary
log. The slave must therefore use ROW
or
MIXED
format if the master does. Changing the
binary logging format on the master from
STATEMENT
to ROW
or
MIXED
while replication is ongoing to a slave
with STATEMENT
format can cause replication
to fail with errors such as Error 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 slave to STATEMENT
format when the master
is still using MIXED
or
ROW
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
master and the slave.
If you are using InnoDB
tables and
the transaction isolation level is READ
COMMITTED
or READ
UNCOMMITTED
, only row-based logging can be used. It is
possible to change the logging format to
STATEMENT
, but doing so at runtime leads very
rapidly to errors because InnoDB
can no
longer perform inserts.
With the binary log 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
.
When row-based binary logging is used, the
binlog_row_event_max_size
system variable and its corresponding startup option
--binlog-row-event-max-size
set a
soft limit on the maximum size of row events. The default value
is 8192 bytes, and the value can only be changed at server
startup. Where possible, rows stored in the binary log are
grouped into events with a size not exceeding the value of this
setting. If an event cannot be split, the maximum size can be
exceeded.
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.
When using statement-based logging for replication, 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 nondeterministic; 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.4.7, “Known Issues in MySQL”.
For information about logs kept by replication slaves, see Section 17.2.4, “Replication Relay and Status Logs”.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-binary-log-setting.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.