MySql shell command not replicated to slave

Question

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?

• Derek Downey: Does it work through replication when run through other means (phpadmin, mysql shell [not passed through the -e option], etc) ?
• Ran: it works when im running it through mysql shell. im using mysql version: 5.1.49
• Jack Douglas: @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 :-)
• Ran: @jack logged in to both accounts with same id
• Jack Douglas: 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?

Answer 1

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.

Answer 2

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.

• RolandoMySQLDBA: Excellent reference, +1 !!!

Answer 3

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\`);"