Question 84097 in Docker Question posted in
The official documentation can be found here.

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

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

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    what’is error log？ please check error message

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
  4. 0

    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’;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search