Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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.

SUGGESTION

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 SQL_LOG_BIN=0;
SET bulk_insert_buffer_size = 1024 * 1024 * 256;
LOAD DATA INFILE 'mydata.csv' INTO tb1 ... ;

Step 03 : Make the script LoadSlave.sql

SET SQL_LOG_BIN=0;
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 &
wait

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 &
....
wait

That way

  • both imports are done together
  • binlogs are not bloated
  • CSV file can always be deleted
share|improve this answer
    
Thanks @Rolando, this is very helpful, do I have to do it for each slaves if it has multiple? –  bond Apr 18 '13 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. –  RolandoMySQLDBA Apr 18 '13 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 '13 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 '13 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. –  RolandoMySQLDBA Apr 18 '13 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.

http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html

share|improve this answer
    
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? –  Derek Downey Apr 19 '13 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. –  Michael - sqlbot Apr 19 '13 at 3:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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