19.5.1.19 Replication and LOAD DATA (original) (raw)

19.5.1.19 Replication and LOAD DATA

LOAD DATA is considered unsafe for statement-based logging (seeSection 19.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”). Whenbinlog_format=MIXED is set, the statement is logged in row-based format. Whenbinlog_format=STATEMENT is set, note that LOAD DATA does not generate a warning, unlike other unsafe statements.

If you use LOAD DATA withbinlog_format=STATEMENT, each replica on which the changes are to be applied creates a temporary file containing the data. The replica then uses aLOAD DATA statement to apply the changes. This temporary file is not encrypted, even if binary log encryption is active on the source, If encryption is required, use row-based or mixed binary logging format instead, for which replicas do not create the temporary file.

If a PRIVILEGE_CHECKS_USER account has been used to help secure the replication channel (seeSection 19.3.3, “Replication Privilege Checks”), it is strongly recommended that you log LOAD DATA operations using row-based binary logging (binlog_format=ROW). IfREQUIRE_ROW_FORMAT is set for the channel, row-based binary logging is required. With this logging format, the FILE privilege is not needed to execute the event, so do not give thePRIVILEGE_CHECKS_USER account this privilege. If you need to recover from a replication error involving aLOAD DATA INFILE operation logged in statement format, and the replicated event is trusted, you could grant the FILE privilege to thePRIVILEGE_CHECKS_USER account temporarily, removing it after the replicated event has been applied.

When mysqlbinlog reads log events forLOAD DATA statements logged in statement-based format, a generated local file is created in a temporary directory. These temporary files are not automatically removed by mysqlbinlog or any other MySQL program. If you do use LOAD DATA statements with statement-based binary logging, you should delete the temporary files yourself after you no longer need the statement log. For more information, seeSection 6.6.9, “mysqlbinlog — Utility for Processing Binary Log Files”.