importing csv file into mysql docker container

Question

> docker exec -it craig bash  
> mysql -u root -p --local-infile 
> use craigslist;
> 
> LOAD DATA LOCAL INFILE 'C:/Users/hank/Desktop/craigs/data.csv' 
> INTO TABLE books_mags  
> FIELDS TERMINATED BY ',' 
> LINES TERMINATED BY '\n'
> IGNORE 1 ROWS;

I have a csv file on my desktop that I am looking to enter into a MySQL table. But i am having a rough time figuring out the file path errors.

ERROR 2 (HY000): File 'C:/Users/hank/Desktop/craigs/data.csv' not found (OS errno 2 - No such file or directory)

I keep getting this error.

I am using a windows 10 and a docker containter for mySQL.

How can i put my csv into MYSQL?

Answer 1

Directory path C:/Users/hank/Desktop/craigs/data.csv is in your host system(Windows). File system of your docker will be entirely different unless you have mounted the volume. Two ways to access the file inside the container

First approach

You'll have to mount the volume when you start the container

docker run -d image_name -v "C:/Users/hank/Desktop/craigs/:/data"

This is not always recommended.

Second Approach

Copy the file from your host machine to the container using docker cp

See 'docker cp --help'.

Usage:  docker cp [OPTIONS] CONTAINER:SRC_PATH DEST_PATH|-
    docker cp [OPTIONS] SRC_PATH|- CONTAINER:DEST_PATH

Copy files/folders between a container and the local filesystem

It is similar to copying files to remote server using scp command.

Answer 2

1- Get you CSVs into your computer

2- Load them into your container, to manually get them there you can use:

docker cp file container:/<container-directory>

3- Enter your container and execute

> sudo docker exec -it jose_signot_mysql bash
container> mysql -uUser -pPassword
mysql> LOAD DATA LOCAL INFILE '/path/in/container/to/file' into table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

or execute script from outside, using an inline password (security issue)

  sudo docker exec jose_signot_mysql bash mysql -u<User> -p<Password> <databaseName> -e "LOAD DATA LOCAL INFILE '/path/in/container/to/file' into table <tableName> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;"

If you have an issue with secure-file-priv being set, when running this, in your instance check: this https://github.com/docker-library/mysql/issues/447#issuecomment-403393323