Creating a MYSQL table in a Docker container with a SQL file

Question

I have created a MySQL docker container with

sudo docker run -d --name db-mysql -e MYSQL_ROOT_PASSWORD=mypassword -e MYSQL_DATABASE=DB mysql

I have schema.sql to create the schema

DROP DATABASE IF EXISTS `DB`;
CREATE DATABASE `DB`;
USE `DB`;

DROP TABLE IF EXISTS `Occurrences`;
CREATE TABLE `Occurrences` (
  `IdOccurrence` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Min_probability` double NOT NULL,
  PRIMARY KEY (`IdOccurrence`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
INSERT INTO `Occurrences` VALUES (1,'Frequent',20),(2,'Probable',10),(5,'Possible',1),(6,'Rare',0.1),(7,'Remote',0);

And try to import it (in Ubuntu 18.04) with

sudo docker exec db-mysql mysql -uroot -pmypassword DB < schema.sql

However, I get this warning

mysql: [Warning] Using a password on the command line interface can be insecure.

But the database DB remains empty.

Where is my error?

Just to give another idea: Most programming languages have some sort of library to execute database migrations. If this mysql docker is part of another application, you may consider moving the migration one level up. — maio290
– maio290, Commented Mar 10, 2020 at 11:34
@maio290 Do you know where can I find how to migrate with mysql.connector in Python? — Medical physicist
– Medical physicist, Commented Mar 10, 2020 at 11:44
I am mostly on the Java and php side of development, so unfortunately, no. — maio290
– maio290, Commented Mar 10, 2020 at 11:45
You might need -h 127.0.0.1 -p 3306 for host and port if they're different to default — Jonathan Clark
– Jonathan Clark, Commented Mar 10, 2020 at 12:45

Mikael Kjær · Accepted Answer · 2020-03-10 13:57:22Z

As stated in the documentation for the docker image you can initialize a container with a sql script:

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

So if you start your container with the following it should startup with your schema:

sudo docker run -d --name db-mysql -e MYSQL_ROOT_PASSWORD=mypassword -e MYSQL_DATABASE=DB -v $PWD/schema.sql:/docker-entrypoint-initdb.d/schema.sql mysql

Collectives™ on Stack Overflow

Creating a MYSQL table in a Docker container with a SQL file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related