[MYSQL] Create database and import sql file with Dockerfile

Docker Desktop
1

Hi all !

I’m trying to create a mysql container with a Dockerfile and create a database by importing the sql files.

I’m on windows 10 PRO with Docker for Windows.
Docker version -> Docker version 17.03.0-ce, build 60ccb22

Here is my dockerfile :

FROM mysql

# Environment variables
ENV MYSQL_ROOT_PASSWORD secretadmin

# Allows you to change the value of "max_allowed_packet"
ADD ["mysqlconf/gatewaymy.cnf", "/etc/mysql/conf.d/conf_mysql.cnf"]

# Create Database
RUN	mkdir /usr/sql
RUN	chmod 644 /usr/sql

ADD ["sql/sources.sql", "/usr/sql/sources.sql"]

RUN /etc/init.d/mysql start && \
        mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "CREATE DATABASE lba" && \
    	mysql -u root -p${MYSQL_ROOT_PASSWORD} -D lba < /usr/sql/sources.sql && \
    	rm -rd /usr/sql && \

When i execute my docker-compose i have a sql error :

Step 7/7 : RUN /etc/init.d/mysql start &&       mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "CREATE DATABASE lba" &&      mysql -u root -p${MYSQL_ROOT_PASSWORD} -D lba < /usr/sql/sources.sql &&         rm -rd /usr/sql &&
 ---> Running in aa9ebdc6831d
/bin/sh: 1: Syntax error: end of file unexpected
ERROR: Service 'mysql' failed to build: The command '/bin/sh -c /etc/init.d/mysql start &&      mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "CREATE DATABASE lba" &&      mysql -u root -p${MYSQL_ROOT_PASSWORD} -D lba < /usr/sql/sources.sql && rm -rd /usr/sql &&' returned a non-zero code: 2

I don’t understand why i have the command ’ /bin/sh -c … ’

If anyone has an idea to solve my problem, Thanks a lot ! :slight_smile:

2

because of this additional

at the end of your last RUN Statement

1 Like
3

Ty for the answer !

I no longer have the error, but apparently this did not work since the command create database lba did not even work.

Indeed, in phpmyadmin, I do not find my database lba.

Strange, right?

PS: sorry for my english, i’m french :smile:

By changing with:

RUN /etc/init.d/mysql start
RUN mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "CREATE DATABASE lba"
RUN mysql -u root -p${MYSQL_ROOT_PASSWORD} -D lba < /usr/sql/sources.sql

I get an error with socket :

Step 8/9 : RUN mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "CREATE DATABASE lba"
 ---> Running in e870dc8837f2
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: Service 'mysql' failed to build: The command '/bin/sh -c mysql -u root -p${MYSQL_ROOT_PASSWORD} -e "CREATE DATABASE lba"' returned a non-zero code: 1
4

your first RUN statements startsthe database in background and creates an image based on this
your second RUN starts the image. the previously started background does not run. So any attempt to access the database fails then.

if you want to start the database and import something, then you need to do it in one RUN statement. Probably also start database and wait a fair amount of time, before try to access teh database.

5

I am using the below Dockerfile, getting the same issue, neither user “comeon” is being created nor dump is getting imported.
FROM mysql:5.7

# ROOT PASSWORD
ENV MYSQL_ROOT_PASSWORD=mypassword

#ENV MYSQL_DATABASE=sampledb
ENV MYSQL_USER=sample-username
ENV MYSQL_PASSWORD=sapassword


ENV MYSQL_DATA_DIR=/var/lib/mysql \
    MYSQL_RUN_DIR=/run/mysqld \
    MYSQL_LOG_DIR=/var/log/mysql

ADD ["db_dump.sql", "/tmp/dump.sql"]

RUN /etc/init.d/mysql start && \
         mysql -u root -p$MYSQL_ROOT_PASSWORD  -e "GRANT ALL PRIVILEGES ON *.* TO 'comeon'@'%' IDENTIFIED BY 'password';FLUSH PRIVILEGES;" && \
        mysql -u root -p${MYSQL_ROOT_PASSWORD}  < /tmp/dump.sql

#PORT
EXPOSE 3306
6

try to put it into a script and try to put a sleep (later replaced by a busy wait probably) between mysql start and your import.

7

tried that already, didnt work out.
Mysql start, mysql user create and import in a script, copied it to container, changed permissions to execute. but didnt help.

8

Check “Initializing a fresh instance” in https://hub.docker.com/_/mysql/ . You just need to mount *.sh or *.sql in /docker-entrypoint-initdb.d then the files are treated on launching, respectively.

By the way I found the answer at https://qiita.com/furu8ma/items/75e5b1df29fef04ec7f1 , thanks Qiita-er.

1 Like
9

Thanks @piglovesyou, you saved me! I’ve tried and tried for ages to figure out how to do that, finally!
I think it’s a bummer that they didn’t document that on their Docker Hub image page / github.
Now all set with this line in my Dockerfile:

COPY ./wordpress.sql /docker-entrypoint-initdb.d/
1 Like
10

can you please share your docker file.
I am facing same problem. I tried many thing but unable to create and import data base using Dockerfile so can you please help me.
Dockerfile:

FROM mysql
COPY wpt-db.sql /docker-entrypoint-initdb.d/wpt-db.sql
RUN /bin/bash -c “/usr/bin/mysqld_safe --skip-grant-tables &” &&
sleep 5 &&
mysql -u root -e “CREATE DATABASE mydb” &&
mysql -u root mydb </docker-entrypoint-initdb.d/wpt-db.sql
RUN pwd
CMD [“mysqld”]

11

It is better to put init.sql into a local directory and mount to directory /docker-entrypoint-initdb.d/ during the stage of creating container.
like below. $home is my local directory, I have put ddl.sql in it.

$ docker run --name mysql57
-e MYSQL_ROOT_PASSWORD=root
-p 3306:3306
-v $home/conf:/etc/mysql/conf.d
-v $home/data:/var/lib/mysql
-v $home/init:/docker-entrypoint-initdb.d
-p 2222:22
-d mysql:5.7.22

12

make sure your script is named init.sql when you map it to /docker-entrypoint-initdb.d

13

This worked for me. The key was adding the sql script to docker entrypoint folder and the SQL command to enable local load data work

Dockerfile

FROM mysql/mysql-server:5.7

ENV MYSQL_ALLOW_EMPTY_PASSWORD=true
ENV MYSQL_USER=brett
ENV MYSQL_PASSWORD=supersecret
ENV MYSQL_DATABASE=billsDB

COPY ./sql-scripts/ /docker-entrypoint-initdb.d/

COPY ./sql-scripts/billSchema.sql .
COPY ./sql-scripts/bills.csv .

/sql-scripts/billSchema.sql

CREATE DATABASE IF NOT EXISTS billsDB;

USE billsDB;

CREATE TABLE bills (

bill_name varchar(255) NOT NULL,

amount int NOT NULL,

dateDue int NOT NULL,

source varchar(255) NOT NULL,

isPaid int NOT NULL

);
SET GLOBAL local_infile=1;

LOAD DATA LOCAL INFILE '/bills.csv'

INTO TABLE bills

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\r\n'

IGNORE 1 ROWS

(bill_name,amount,dateDue,source,isPaid)