I am trying to load data into mysql database form CSV file. I found that we could use LOAD DATA INFILE command to do it. But as per the mysql documentation it is not replication safe. (See here)

Is there a better way to do it rather than to do it via application?

2 Answers 2


You are absolutely right. it is not replication-safe. In fact, I wrote about how LOAD DATA INFILE replicates back on Jan 22, 2012 : MySql shell command not replicated to slave. Basically, the entire data file is stored in the binary logs, replicated to the Slave, manifested as a text file in /tmp, and the LOAD DATA INFILE is executed.

What could help is setting sync-binlog=1 before running LOAD DATA INFILE. Notwithstanding, Replication of a large CSV file is at the mercy of replication and the network.


For a Text File called mydata.csv, you are better off doing the following:

Step 01 : cp mydata.csv to mydata.csv2

Step 02 : Make the script LoadMaster.sql

SET bulk_insert_buffer_size = 1024 * 1024 * 256;
LOAD DATA INFILE 'mydata.csv' INTO tb1 ... ;

Step 03 : Make the script LoadSlave.sql

SET bulk_insert_buffer_size = 1024 * 1024 * 256;
LOAD DATA INFILE 'mydata.csv2' INTO tb1 ... ;

Step 04: Load both in parallel

mysql -hIPMaster -u... -p... < LoadMaster.sql &
mysql -hIPSlave  -u... -p... < LoadSlave.sql &

If you have multiple slaves, you could do this:

mysql -hIPMaster -u... -p... < LoadMaster.sql &
mysql -hIPSlave1  -u... -p... < LoadSlave.sql &
mysql -hIPSlave2  -u... -p... < LoadSlave.sql &
mysql -hIPSlave3  -u... -p... < LoadSlave.sql &

That way

  • both imports are done together
  • binlogs are not bloated
  • CSV file can always be deleted
  • Thanks @Rolando, this is very helpful, do I have to do it for each slaves if it has multiple?
    – bond
    Apr 18, 2013 at 19:47
  • I am afraid so it you want each Slave loaded without replication. You could do it serial fashion as well if network traffic is a concern. Apr 18, 2013 at 19:49
  • all the slaves are internal, so i am not concerned about the network traffic.. I just want to make sure the data is replicated in all the instances.
    – bond
    Apr 18, 2013 at 19:55
  • i am a newbie on this.. so when u run the commands in step 4, the sql that gets run in master.. doesn't it try to run it in slave automatically by replication? how do we ensure the sql is only run in master and is not replication as we run it paralleled?
    – bond
    Apr 18, 2013 at 20:03
  • That's why The first line of each SQL script is SET SQL_LOG_BIN=0;. That prevents an SQL statement from being recorded in the binary logs. If a Master does not record an SQL statement, then it does not get replicated. Apr 18, 2013 at 20:05

Another answer has already been accepted, but I disagree with that answer, because your question first requires an understanding of the meaning of the term "unsafe" and the phrase "considered unsafe" when it is used related to MySQL replication.

When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement and its effects can be replicated correctly using statement-based format. http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html (emphasis added)

There is no such thing as an "unsafe" statement when you are using MIXED or ROW-based replication, because the entire premise of "unsafe" is related to how statement-based logging and replication work.

Something "considered unsafe" doesn't mean it shouldn't be done -- it means the optimizer will consider it unsafe to log in statement-based mode and therefore that it will be automatically replicated using the row-based replication logging format if row-based logging is available -- that is, if you are not using binlog_format = STATEMENT -- which you should not be doing unless you have a specific reason to use it. If you are not aware of a specific reason to use it, then you most likely do not have one, and you should used MIXED logging, so that the optimizer can choose how to replicate each statement.


  • I agree with your statement about 'unsafe'. But curious from a performance standpoint, which is better for a large-ish LOAD DATA INFILE command: replicating via row-based, or parallel LOAD DATA commands run on each server as in @Rolando's answer? Apr 19, 2013 at 1:52
  • I haven't benchmarked the other method. I rarely use LOAD DATA INFILE, since I often deal in files that aren't plain text, and at any rate I prefer to have an external application parse the file and do primitive structural validation. In some cases, my "loader" applications are designed with the ability to monitor Seconds_Behind_Master on the slaves while they are running, and will actually throttle themselves to write less aggressively to the master if or when a replication lag occurs. Apr 19, 2013 at 3:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.