Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 47.1Mb
PDF (A4) - 47.1Mb
PDF (RPM) - 42.4Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.9Mb
HTML Download (RPM) - 9.4Mb
Man Pages (TGZ) - 226.8Kb
Man Pages (Zip) - 333.5Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

17.5.1.19 Replication and LOAD DATA

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

If you do use LOAD DATA when binlog_format=STATEMENT is set, a temporary file containing the data is created on the replication slave where the changes are applied. The slave then uses a LOAD DATA INFILE statement to apply the changes. If binary log encryption is active on the server, note that this temporary file is not encrypted. When encryption is required, be sure to use row-based or mixed binary logging format instead, which do not create the temporary files.

If a PRIVILEGE_CHECKS_USER account has been used to help secure the replication channel (see Section 17.3.3, “Replication Privilege Checks”), for a LOAD DATA INFILE operation logged in statement format, the account would require the INSERT privilege on the relevant table and also the FILE privilege. However, it is strongly recommended that you do not give the FILE privilege to the account, and instead ensure that the event is logged in row-based format, so that this privilege is not needed. If you do need to recover from a replication error involving a LOAD DATA INFILE statement, and the replicated event is trusted, you could grant the FILE privilege to the PRIVILEGE_CHECKS_USER account temporarily, removing it after the replicated event has been applied.

When mysqlbinlog reads log events for LOAD 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, see Section 4.6.8, “mysqlbinlog — Utility for Processing Binary Log Files”.