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 have a shell script that looks something like that:

 #!/bin/bash
 mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv'
 INTO TABLE db_name.db_table FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'  
(\`col1\`,\`col2\`,\`col3\`);"

the problem is that when I run this script on my master server the call doesnt get replicated to the slave server (even though /srv/some_file.csv exists on both servers).

any ideas how to make shell command replicate over servers?

share|improve this question

migrated from serverfault.com Jan 16 '12 at 14:51

This question came from our site for professional system and network administrators.

    
Does it work through replication when run through other means (phpadmin, mysql shell [not passed through the -e option], etc) ? –  Derek Downey Jan 16 '12 at 15:08
    
it works when im running it through mysql shell. im using mysql version: 5.1.49 –  Ran Jan 16 '12 at 15:18
    
@Ran please can you log in to dba.se using the same OpenID you use on SF? That'll create you a user here and we can then merge your dba.se account with that one - please ping me here if you do it :-) –  Jack Douglas Jan 16 '12 at 15:30
    
@jack logged in to both accounts with same id –  Ran Jan 16 '12 at 15:41
    
that doesn't seem to have worked - it should have linked the 'ghost' user to your combined SF/dba account automatically. If you succeed in joining dba.se with the same OpenID you have your other accounts on (here, we can then do the merge - can you try again? –  Jack Douglas Jan 16 '12 at 17:10
show 2 more comments

3 Answers 3

It might depend on your replication logging.

In MySQL 5.5.6 and later, LOAD DATA INFILE is considered unsafe (see Section 15.1.2.3, “Determination of Safe and Unsafe Statements in Binary Logging”). It causes a warning when using statement-based logging format, and is logged using row-based format when using mixed-format logging. [src]

The same applies to 5.1.52 in the 5.1 version (instead of 5.5.6).

So, if you are replicating with statement-based logging and running version >=5.1.52 or >=5.5.6, it probably will not replicate.

share|improve this answer
    
Excellent reference, +1 !!! –  RolandoMySQLDBA Mar 1 '12 at 19:54
add comment

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:

  1. Relay_Log_Space was growing
  2. Seconds_Behind_Master was still 0
  3. 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.

share|improve this answer
add comment

When I have seen things like this before it has been related to replication filtering. If there is any database or table level filtering - on the master or slave - it is necessary to select the appropriate DB with use before running any commands you wish to replicate. Try something like:

#!/bin/bash

mysql -udb_user -pdb_password -e "use db_name; LOAD DATA LOCAL INFILE '/srv/some_file.csv'
  INTO TABLE db_name.db_table FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'  
  (\`col1\`,\`col2\`,\`col3\`);"
share|improve this answer
add comment

Your Answer

 
discard

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