Linux bash MySQL load infile

Question

I'm trying to simplify a load data local infile, by putting it into a .sh file, and bash to run it.

Here is my count_portal.sh file

mysql -h host -u root -p password 
load data local infile
"/workplace/user/dump/count_portal.txt" 
replace into table test.icqa_count_portal 
fields terminated by '\t' lines terminated by '\n' ignore 1 lines;

And here is my bash script

bash /home/user/Desktop/count_portal.sh I get an output that doesn't do what it is designed to do. When I simply make the count_portal.sh contain mysql -h host it longs in when running the script.

How I would go about making this .sh work. Currently it doesn't read anything after mysql-h host -u root -p password. It appears load data local infile isn't recognized or not working. Although it does work independently while connected to mysql. — Nomad
– Nomad, Commented Dec 5, 2013 at 21:21

Nomad · Accepted Answer · 2013-12-07 17:25:35Z

1

I figured it out. Here is my file.

#!/bin/bash
/usr/bin/mysql --host=host --user=root --password=password --database=test<<EOFMYSQL
load data local infile '/workplace/user/ETLdump/count_portal.txt' replace INTO TABLE count_portal fields terminated by '\t' LINES
TERMINATED BY '\n' ignore 1 lines;
EOFMYSQL

Works flawlessly!

answered Dec 7, 2013 at 17:25

Nomad

2783 gold badges11 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RustProof Labs · Accepted Answer · 2013-12-05 21:48:55Z

0

Try removing the space between the user and password switches. So it would be something like:

mysql -h host -uroot -ppassword etc....

The spaces seem to cause problems for me when doing similar things inside bash scripts.

answered Dec 5, 2013 at 21:48

RustProof Labs

1,29810 silver badges9 bronze badges

Comments

PasteBT · Accepted Answer · 2013-12-05 23:19:33Z

0

missed -e ?

mysql -h host -u root -ppassword dbname -e "load data local infile '/workplace/user/dump/count_portal.txt' replace into table test.icqa_count_portal fields terminated by '\t' lines terminated by '\n' ignore 1 lines;"

edited Dec 5, 2013 at 23:19

answered Dec 5, 2013 at 21:39

PasteBT

2,20817 silver badges18 bronze badges

3 Comments

Nomad Over a year ago

Tried this same result, just gave me a bunch of options of example commands.

PasteBT Over a year ago

@Spartacus38 Emm, two more things, should no space between -p and pasword, and you missed db name.

Nomad Over a year ago

That did, something, it generates a new line. dquote >

Jason Heo · Accepted Answer · 2013-12-06 04:20:07Z

0

I agree with @PasteBT, his answer should work well. I think there are escape problem or shell variable is empty.

Have you tried this?:

echo "LOAD DATA LOCAL INFILE '/workplace/user/dump/count_portal.txt' REPLACE INTO TABLE test.icqa_count_portal FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES;" | mysql -h host -u root -ppassword

and you original shell script is wrong. could you post again what did you do?

answered Dec 6, 2013 at 4:20

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

Comments

vishu9219 · Accepted Answer · 2015-03-31 11:35:01Z

0

mysql -u<username> -p<password> -h<hostname> <db_name> --local_infile=1 -e "use <db_name>" -e"LOAD DATA LOCAL INFILE '<path/file_name>' 
IGNORE INTO TABLE <table_name>
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '\"'"

answered Mar 31, 2015 at 11:35

vishu9219

8137 silver badges15 bronze badges

Collectives™ on Stack Overflow

Linux bash MySQL load infile

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related