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?
Best Answer
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 M1Running
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:Relay_Log_Space
was growingSeconds_Behind_Master
was still 0I 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
LOAD DATA LOCAL INFILE
commandLOAD DATA LOCAL INFILE
command its binary logsLOAD DATA LOCAL INFILE
in the relay logsLOAD DATA LOCAL INFILE
Seconds_Behind_Master
based on when the Master completedLOAD DATA LOCAL INFILE
commandLOAD DATA LOCAL INFILE
using the mainifested temp fileIn order to verfiy this is indeed happening:
LOAD DATA LOCAL INFILE
to import a 5 line file CSV file into test.mycsvIf 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 theLOAD DATA LOCAL INFILE
recently executed on the Master is currently being shipped over.I hope this solves the mystery you have been experiencing.