Bookmark and Share
How does "LOAD DATA LOCAL INFILE" work?
(Publish Date: 2012-9-6 12:46pm, Total Visits: 1205, Today: 2, This Week: 5, This Month: 6)

How does "load data infile" work in different binlog_format?

-- statement binlog format
 
When a Master server executes LOAD DATA LOCAL INFILE
 
    mysqld on the Master will...
        complete the LOAD DATA LOCAL INFILE command
        record the LOAD DATA LOCAL INFILE command in its binary logs
 
mysqld on the Slave will do the following:
 I/O Thread reads the binlog from the master and writes LOAD DATA LOCAL INFILE in the relay logs
                I/O Thread copies the import file from the master and generates a temp file (same size on the master) under mysql tmpdir (defined in my.cnf) needed for LOAD DATA LOCAL INFILE
SQL Thread executes LOAD DATA LOCAL INFILE using the mainifested temp file
                SQL Thread deletes the temp file after LOAD DATA LOCAL INFILE completed
                SQL Thread updates Seconds_Behind_Master based on when the Master completed LOAD DATA LOCAL INFILE command
 
 
-- MIXED binlog format
 
When a Master server executes LOAD DATA LOCAL INFILE
 
    mysqld on the Master will...
        complete the LOAD DATA LOCAL INFILE command
        record the LOAD DATA LOCAL INFILE command its binary logs
        dump the enitre data file content (row by row) it just imported into its binary logs
 
    mysqld on the Slave will do the following:
        I/O Thread reads binlog files from the master and writes its content in the relay logs
        SQL Thread reads the contents of the relay logs and loads into the database
        SQL Thread updates Seconds_Behind_Master based on when the Master completed LOAD DATA LOCAL INFILE command