Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does 'LOAD DATA INFILE' work in statement-based replication?

In MySQL, using statement based replication I assume that every SQL statement gets executed on the slave exactly the same way as on the master. However, when I run the statement:

LOAD DATA INFILE 'filename' INTO SomeTable;

I'm not sure if the file needs to exist on the slave the same way at it does on the master. Can someone point me to the documentation of this feature?

like image 999
Avi Avatar asked Dec 10 '08 13:12

Avi


1 Answers

It appears that the file you load with LOAD DATA INFILE on the master are automatically transferred via the replication log from the master to the replica. The replica loads these files when it gets to the LOAD DATA INFILE in the statement-based replication queue.

I'm inferring this from a couple of statements in the docs:

16.1: Backing Up Replication Slaves

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave's data. These files are always needed to resume replication after you restore the slave's data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the --slave-load-tmpdir option.

16.1.2.3: Replication Slave Options and Variables

When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some filesystem that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to use the --relay-log option to place the relay logs in that filesystem.

like image 182
Bill Karwin Avatar answered Sep 23 '22 12:09

Bill Karwin