MySQL 在 docker-compose 启动时创建数据库

Create database on docker-compose startup

提问by Pedrog

I would like to create a MySQL database using environment variables in docker-compose.yml file, but it is not working. I have the following code:

我想在 docker-compose.yml 文件中使用环境变量创建一个 MySQL 数据库，但它不起作用。我有以下代码：

# The Database
database:
  image: mysql:5.7
  volumes:
    - dbdata:/var/lib/mysql
  restart: always
  environment:
    MYSQL_ROOT_PASSWORD: secret
    MYSQL_DATABASE: homestead
    MYSQL_USER: root
    MYSQL_PASSWORD: secret
  ports:
    - "33061:3306"

Could someone explain the function of this vars?

有人可以解释一下这个 vars 的功能吗？

回答by marszczybrew

There is also an option to provide an init file for mysqlcontainer which will be applied each time a container is created.

还有一个选项可以为mysql容器提供一个 init 文件，每次创建容器时都会应用该文件。

database:
    image: mysql:5.7
    ports:
        - "33061:3306"
    command: --init-file /data/application/init.sql
    volumes:
        - ./init.sql:/data/application/init.sql
    environment:
        MYSQL_ROOT_USER: root
        MYSQL_ROOT_PASSWORD: secret
        MYSQL_DATABASE: homestead
        MYSQL_USER: root
        MYSQL_PASSWORD: secret

Such file (init.sql) could contain your initial database structure and data - for example:

这样的文件 ( init.sql) 可以包含您的初始数据库结构和数据 - 例如：

CREATE DATABASE IF NOT EXISTS dev;
CREATE DATABASE IF NOT EXISTS test;
USE dev;
CREATE TABLE IF NOT EXISTS (...);

回答by Sebastiaan Renkens

The database is probably already initialized and the configuration is stored in /var/lib/mysql. Since you defined a volume for that location the config will survive a restart. The MySQL image will not reconfigure the database over and over again, it only does this once.

数据库可能已经初始化并且配置存储在/var/lib/mysql. 由于您为该位置定义了一个卷，因此配置将在重新启动后继续存在。MySQL 映像不会一遍又一遍地重新配置数据库，它只会执行一次。

volumes: - dbdata:/var/lib/mysql

volumes: - dbdata:/var/lib/mysql

If your database is empty you can reset the database by performing docker-compose down -vwhere the -vremoves the volumes defined in the volume section. See https://docs.docker.com/compose/reference/down/. On the next docker-compose upthe MySQL image will start fresh and will initialize the database with the configuration you've provided throug the environment section.

如果您的数据库为空，您可以通过执行docker-compose down -vwhere-v删除卷部分中定义的卷来重置数据库。请参阅https://docs.docker.com/compose/reference/down/。接下来docker-compose up，MySQL 映像将重新启动，并将使用您通过环境部分提供的配置初始化数据库。

回答by jonhid

Answering your question ...

回答你的问题...

One thing I use to do when building a new docker container is understand what the image I pull from does when is builded.

在构建新的 docker 容器时，我经常做的一件事是了解我从中提取的图像在构建时的作用。

In your docker-compose.yml tou have this

在你的 docker-compose.yml 你有这个

# The Database
database:
  image: mysql:5.7

This is the image you pull from, "mysql:5.7"

这是您从中提取的图像，“mysql:5.7”

Dockerhub is a repository where you can find info of this images.

Dockerhub 是一个存储库，您可以在其中找到此图像的信息。

Do a google search "mysql:5.7 dockerhub"

谷歌搜索“mysql:5.7 dockerhub”

First result is https://hub.docker.com/_/mysql/

第一个结果是https://hub.docker.com/_/mysql/

There you have your image 5.7, if you click on 5.7 you have this

你有你的图像 5.7，如果你点击 5.7 你有这个

https://github.com/docker-library/mysql/blob/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7/Dockerfile

https://github.com/docker-library/mysql/blob/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7/Dockerfile

Which is the Dockerfile from the image, you can have a look at interesting things that happen when building the image.

这是镜像中的Dockerfile，你可以看看构建镜像时发生的有趣的事情。

One of this is ENTRYPOINT ["docker-entrypoint.sh"]

其中之一是 ENTRYPOINT ["docker-entrypoint.sh"]

This is the file that got executed when image is ready

这是图像准备好时执行的文件

I you go one level up in the repo you will see this file

你在 repo 中上一级你会看到这个文件

https://github.com/docker-library/mysql/tree/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7

https://github.com/docker-library/mysql/tree/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7

The you can see your environment variables being used to create new database etc...

您可以看到您的环境变量被用于创建新数据库等...

file_env 'MYSQL_DATABASE'
        if [ "$MYSQL_DATABASE" ]; then
            echo "CREATE DATABASE IF NOT EXISTS \`$MYSQL_DATABASE\` ;" | "${mysql[@]}"
            mysql+=( "$MYSQL_DATABASE" )
fi

回答by lvthillo

For version 2 of docker-compose you'll .ymlor .yamlcan look like this:

对于 docker-compose 的第 2 版，您将.yml或.yaml可以如下所示：

version: '2'
volumes:
 dbdata:

services:
 mysql:
  image: mysql:5.7
  container_name: mysql
  volumes:
    - dbdata:/var/lib/mysql
  restart: always
  environment:
    - MYSQL_ROOT_PASSWORD=secret
    - MYSQL_DATABASE=homestead
    - MYSQL_USER=root
    - MYSQL_PASSWORD=secret
  ports:
    - "33061:3306"

start it with docker-compose up -dand check:

开始docker-compose up -d并检查：

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                     NAMES
a3567fb78d0d        mysql:5.7           "docker-entrypoint..."   2 minutes ago       Up 2 minutes        0.0.0.0:33061->3306/tcp   mysql

docker exec -it a3567fb78d0d bash
root@a3567fb78d0d:/# mysql -u root -p homestead
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| homestead          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Your volume will be persisted in docker volume nameoffolder_dbdata(/var/lib/docker/volumes/...)

您的卷将保留在 docker volume nameoffolder_dbdata( /var/lib/docker/volumes/...)

回答by Gene S

If I understand your question correctly, you want to to have a container with a specific database in it. Like have a MySQL container with CREATE DATABASE mydb, etc. already executed. If so you need to use docker-entrypoint-initdb.d: https://docs.docker.com/samples/library/mysql/#docker-secrets

如果我正确理解您的问题，您希望有一个包含特定数据库的容器。就像CREATE DATABASE mydb已经执行了一个 MySQL 容器，等等。如果是这样，您需要使用docker-entrypoint-initdb.d：https: //docs.docker.com/samples/library/mysql/#docker-secrets

When the official MySQL container is started for the first time, a new database will be created first. Then it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. So all you need to do is create /docker-entrypoint-initdb.ddirectory and put your initialisation script there.

官方 MySQL 容器第一次启动时，会先创建一个新的数据库。然后它将执行在 .sh 中找到的扩展名为 .sh、.sql 和 .sql.gz 的文件/docker-entrypoint-initdb.d。所以你需要做的就是创建/docker-entrypoint-initdb.d目录并将你的初始化脚本放在那里。

回答by Jason M.

The official MySQL docker image added support for init scripts in their base image. They document the feature under their "Initializing a fresh instance" on the Docker Hub page.

官方 MySQL docker 镜像在其基础镜像中添加了对 init 脚本的支持。他们在 Docker Hub 页面上的“初始化新实例”下记录了该功能。

Here are the steps I took to solve creating multiple database and users in the MySQL docker image:

以下是我为解决在 MySQL docker 映像中创建多个数据库和用户而采取的步骤：

1. Create the init file (the Docker image recognizes .sh, .sql, and .sql.gz files) named setup.sqlin the local directory named .docker
2. Place the commands inside setup.sql(see below for an example)
3. Mount the setup script into the directory fwithin the docker-compose.yamlfile (see below for an example)
4. Run docker-compose up -dand MySQL will run the code inside setup.sql

1. 创建init文件（泊坞窗图像识别.SH，.SQL和.sql.gz文件）命名的setup.sql本地目录命名.docker
2. 将命令放在里面setup.sql（参见下面的示例）
3. 将安装脚本挂载到文件f内的docker-compose.yaml目录中（参见下面的示例）
4. 运行docker-compose up -d，MySQL 会运行里面的代码setup.sql

Note: the script will run files alphabetically, so keep that in mind.

注意：脚本将按字母顺序运行文件，因此请记住这一点。

Example docker-compose.yaml

示例 docker-compose.yaml

version: "3.5"
services:
    mysql:
        image: mysql
        ports:
            - 3306:3306
        environment:
            MYSQL_ROOT_PASSWORD: SomeRootPassword1!
            MYSQL_USER: someuser
            MYSQL_PASSWORD: Password1!
            MYSQL_DATABASE: somedatabase
        volumes:
            - .docker/setup.sql:/docker-entrypoint-initdb.d/setup.sql
            - db_data:/var/lib/mysql
volumes:
    db_data:

Example setup.sql

示例 setup.sql

-- create the databases
CREATE DATABASE IF NOT EXISTS projectone;

-- create the users for each database
CREATE USER 'projectoneuser'@'%' IDENTIFIED BY 'somepassword';
GRANT CREATE, ALTER, INDEX, LOCK TABLES, REFERENCES, UPDATE, DELETE, DROP, SELECT, INSERT ON `projectone`.* TO 'projectoneuser'@'%';

FLUSH PRIVILEGES;