LOAD DATA INFILEis not written to the binary log like other statements. It is written as one or more events in a packed format, not as a cleartext statement in the binary log. The events indicate what options are present in the statement and how to process the data file.

Historically, there seem to have been at least four event sequences for representingLOAD DATA INFILEoperations.

1) In MySQL 3.23, there was only one event:Load_log_event(type codeLOAD_EVENT= 6).Load_log_eventonly contains the filename, not the file itself. When the slave sees aLoad_log_event, it requests that the master send the file in a separate connection. This has the drawback that the binary log is not self-contained: If the file has been removed on the master or the slave cannot access the master, the file transfer fails.

2) In MySQL 4.0.0, the file contents were included in the binary log. Several new event types were introduced:Create_file_log_event(type codeCREATE_FILE_EVENT= 8),Append_block_log_event(type codeAPPEND_BLOCK_EVENT= 9),Execute_load_log_event(type codeEXEC_LOAD_EVENT= 10), andDelete_file_log_event(type codeDELETE_FILE_EVENT= 11). The event sequence is:

TheCreate_file_log_eventcontains the options toLOAD DATA INFILE. This was a design flaw since the file cannot be loaded until theExec_load_log_eventis seen. To work around this, the slave, when executing theCreate_file_log_event, wrote theCreate_file_log_eventto a temporary file. When theExecute_load_log_eventwas seen, this temporary file was read back so that theLOAD DATA INFILEstatement could be constructed.

Append_block_log_eventis used for files larger than a threshold. In this case, the file is split and the pieces are sent in separate events. The threshold is around 2^17 = 131072 bytes.

CREATE_FILE_EVENTtells the slave to create a temporary file and fill it with a first data block. Later, zero or moreAPPEND_BLOCK_EVENTevents append blocks to this temporary file.EXEC_LOAD_EVENTtells the slave to load the temporary file into the table, orDELETE_FILE_EVENTtells the slave not to do the load and to delete the temporary file.DELETE_FILE_EVENToccurs when theLOAD DATAfailed on the master: On the master we start to write loaded blocks to the binary log before the end of the statement. If for some reason there is an error, we must tell the slave to abort the load.

MySQL 4.0.0 also introduced theNEW_LOAD_EVENT= 12 type code.

If a slave reads aNEW_LOAD_EVENTfrom a binlog, it will use it as aLOAD_EVENT(but allowing longer separator names). Lost in the mysteries of time is the knowledge of whether there was ever a server version capable of writingNEW_LOAD_EVENT.

3) The originalLoad_log_eventused one character for each of the delimiters (FIELDS TERMINATED BY, and so forth). At an unknown point in the version history, the format was modified to allow multiple-character strings as separators. This uses the same class,Load_log_event, but has the type codeNEW_LOAD_EVENT= 12. This affectsCreate_file_log_event, since that inherits from Load_log_event. So the new feature inLoad_log_eventallowsCreate_file_log_eventto use multiple-character delimiters.

>1,2,3 >4,5,6 >7,8,9 >10,11,12

load data infile '/m/tmp/u.txt' replace into table x fields terminated by ',' optionally enclosed by '"' escaped by '\\' lines starting by '>' terminated by '\n' ignore 2 lines (a,b,c);

00000180: db4f 153f 0801 0000 .........O.?.... 00000190: 006f 0000 0088 0100 0000 0004 0000 0000 .o.............. 000001a0: 0000 0002 0000 0001 0403 0000 0003 0000 ................ 000001b0: 0001 2c01 2201 0a01 3e01 5c06 0101 0161 ..,."...>.\....a 000001c0: 0062 0063 0078 0074 6573 7400 2f6d 2f74 .b.c.x.test./m/t 000001d0: 6d70 2f75 2e74 7874 003e 312c 322c 330a mp/u.txt.>1,2,3. 000001e0: 3e34 2c35 2c36 0a3e 372c 382c 390a 3e31 >4,5,6.>7,8,9.>1 000001f0: 302c 3131 2c31 32db 4f15 3f0a 0100 0000 0,11,12.O.?..... 00000200: 1700 0000 f701 0000 0000 0300 0000 ..............