# replication of load data infile

**URL:** <https://forums.percona.com/t/replication-of-load-data-infile/452>\
**Category:** Other MySQL® Questions\
**Created:** [September 15, 2007, 8:07am UTC](https://forums.percona.com/t/replication-of-load-data-infile/452 "2007-09-15T08:07:48Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![natbro](https://avatars.discourse-cdn.com/v4/letter/n/e19adc/32.png) [@natbro](https://forums.percona.com/u/natbro)\
**Post date:** [September 15, 2007, 8:07am UTC](https://forums.percona.com/t/replication-of-load-data-infile/452/1 "2007-09-15T08:07:48Z")

</div>

anybody know of an option to cause LOAD DATA INFILE to replicate purely as a statement and not transfer the contents of the file through the binlog?

e.g. you could out-of-band pre-populate your master & slaves with a horrendously large .CSV file in /tmp and then issue the LOAD DATA INFILE without incurring a binlog and replication lag hit, just IO?

we sometimes have some very large bulk updates happening, and the congestion they cause an otherwise healthy replication chain would be nice to avoid.

i seem to remember such an option or default behavior in the misty past of MySQL 3, but it seems to replicate contents now in 5.0.

yes, i know doing such a thing fairly obviates the point of a binlog to completely recover your state.

if not, the only alternative i can think of is to do LOAD DATA INFILE into replication excluded tables and manually issue the statement on the master & each slave.

thanks for any insights.

---

<div class="post-metadata">

**Author:** ![RichardBronosky](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/richardbronosky/32/758_2.png) [@RichardBronosky](https://forums.percona.com/u/RichardBronosky)\
**Post date:** [August 29, 2008, 2:23pm UTC](https://forums.percona.com/t/replication-of-load-data-infile/452/2 "2008-08-29T14:23:16Z")

</div>

I don’t believe there is an option for that, but I have had similar needs and solved it with a interim load. Since the interim load effectively doubles the I/O and processing required, I use a dev server. Here is a pseudo-transcript of what the process may look like:

workstation:~$ scp new\_data.csv devbox:/dev/shm #because I like using tmpfs (~RAMDISK) for thisworkstation:~$ ssh devboxdevbox:~$ mysqldump -h prodbox --no-data schema\_name table\_name | mysql test\_schemadevbox:~$ mysql -e "create schema temp\_schema; use temp\_schema; load data local infile ‘/dev/shm/new\_data.csv’ into table table\_name"devbox:~$ mysqldump --no-create-info temp\_schema table\_name | mysql -h prodbox schema\_namedevbox:~$ mysql -e “drop schema temp\_schema”

Another alternative which I have used in the past is to write a custom AWK script that converts each line of the csv file into an insert statement. I’ll post it here if I can find it.