MySql shell command not replicated to slave
Have I got a stunning revelation for you on this problem. I saw this type problem before. I have a MySQL client at my employer's web hosting company with this situation :

The client, running MySQL 5.1.37, has two DB servers in Circular Replication (call it M1 and M2)

He performed a LOAD DATA LOCAL INFILE with a 50GB CSV file on M1

Running SHOW SLAVE STATUS\G several times on M2, Log Files and Positions were not moving.

Then, I noticed something very disturbing. When I ran SHOW SLAVE STATUS\G several more times on M2, I noticed this pattern in the output:


Relay_Log_Space was growing
Seconds_Behind_Master was still 0
Still, none of the Log Files or positions were changing


I was stunned because I did not know what was happening. A few hours later, I ran SHOW PROCESSLIST;. To my surprise, there it was : LOAD DATA LOCAL INFILE in the SQL Thread, but the file name was a temp file in the /tmp folder. All of a sudden, replication was more than 6 hours behind. Then, is dawned on me what was actually happening.

I learned that day that for mysqld to replicate LOAD DATA LOCAL INFILE, this is happens under the hood:

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 it just imported into its binary logs

mysqld on the Slave will do the following:

I/O Thread sees LOAD DATA LOCAL INFILE in the relay logs
I/O Thread create the import file need for LOAD DATA LOCAL INFILE
I/O Thread copies the contents of all relay logs making up the import file, storing it in /tmp
SQL Thread updates Seconds_Behind_Master based on when the Master completed LOAD DATA LOCAL INFILE command
SQL Thread executes LOAD DATA LOCAL INFILE using the mainifested temp file



In order to verfiy this is indeed happening:


On the Master 

create a MyISAM table in the test database called mycsv
Run LOAD DATA LOCAL INFILE to import a 5 line file CSV file into test.mycsv

On the Slave

See if test.mycsv exists with the imported data in it
See if any file exists in /tmp that has the exact same size as the original import file on the Master



If that is what happened, test that again against a 10 GB file.

While it is replicating, goto into the OS in /var/lib/mysql and do ls -l and look for the relay logs. You should see 10 consecutive relay logs 1GB. That will show it for sure because under normal circumstances, every relay log context switch skips by 3 files not 1. Seeing multiple 1GB relay logs reveal that the data for the LOAD DATA LOCAL INFILE recently executed on the Master is currently being shipped over.

I hope this solves the mystery you have been experiencing.