Import csv data into mysql table from Docker (Bash support)-CodePudding

I have a shell script which orchestrates some commands to load a csv into Mysql in Docker.

The data.csv i copy it over to the container along with the shell script run_mysql.sh. Later i do CMD [ "./run_mysql.sh" ] in the Docker to run the shell.

The shell script looks like this.

# Allow  query from outside
sed -i '31 s/bind-address/#bind-address/' /etc/mysql/mysql.conf.d/mysqld.cnf

# start service
sudo service mysql start

# run the sql script to create the table
mysql -u root < create_tables.sql 

# Following is required to load csv into the table
mysql --local-infile=1 -u root -p1

SET GLOBAL local_infile=1;
use ship_history;
LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE ship_movements FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
while [ true ]; do sleep 60; done

In Docker however commands like SET, USE, LOAD DATA LOCAL INFILE does not run, since these are not Bash commands and i have to manually go into the container and run these to load the csv.

Is there a way i can use bash commands to load csv into the table in a Container without having to do Docker exec -it /bin/bash and load it manually ?

Also tried this mysqlimport --columns='head -n 1 data.csv' --ignore-lines=1 dbname ship_movements but doesnt recognise the dbname.

Thanks for your help

CodePudding user response：

I think you are getting those errors because you need to run those commands within mysql and not in bash.

mysql -u mysqlUser -p -e 'your query here'

Maybe you could try doing something like this but adapting it to your command

CodePudding user response：

The fix is with this mysql command which runs in Docker. This way you can orchestrate csv imports into mysql.

mysql -u root --local_infile=1 -e "use databasename" -e "
      LOAD DATA LOCAL INFILE '$file'
      INTO TABLE table_name 
      FIELDS TERMINATED BY ',' 
      OPTIONALLY ENCLOSED BY '\"' 
      LINES TERMINATED BY '\n' 
      IGNORE 1 LINES 
      (COL1, COL2);"

CodePudding user response：

for example

CREATE TABLE product ( id varchar(255) NOT NULL, name varchar(255) NOT NULL, price double(5,2) NOT NULL )

container id 7123ea69ff42 and check your mysql container secure-file-priv is/home/

docker cp /path/to/product.txt 7123ea69ff42:/home/product.txt

mysql --local-infile=1 -uroot -p

LOAD DATA INFILE "/home/product.txt" IGNORE INTO TABLE product FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

CodePudding user response：

what'is error log？ please check error message