How to automatically execute SQL statements when MySQL in Docker starts

When creating a MySQL container with Docker, sometimes we expect that the database and table will be automatically created and the initialization data will be automatically entered after the container is started. That is to say, after the container is started, we can directly connect to the database in the container and use the data in it.

In fact, the official image of MySQL supports this capability, automatically executing the specified SQL script or shell script when the container starts. Let's take a look at the Dockerfile of the official image of MySQL, as shown below:

ENTRYPOINT has been set, which will call the /entrypoint.sh script. We pull the mysql:8 image to the local computer and start it with docker run. Let's take a look at the content of the entrypoint.sh script. There is a section that traverses all files with .sh and .sql suffixes in a fixed directory and then executes them, as shown below:

Now that we understand the principle, let's practice it:

When building a disconf environment on docker, you need to build a mysql database and execute four sql files in sequence to initialize the database, table, and data respectively. There are two ways to do this:

• Copy the four SQL files to the /docker-entrypoint-initdb.d directory, so that the four SQLs will be automatically executed when the container is run. However, from the screenshot script, the execution order of multiple files cannot be specified. If the script for creating the database is executed later than the script for creating the table, the table creation will fail. Therefore, this method of copying SQL cannot meet our needs (however, if the four files are combined into one SQL in order, the requirements can be met);
• Make a sh file and execute sql according to our own needs. The content is as follows:

#!/bin/bash
mysql -uroot -p$MYSQL_ROOT_PASSWORD <<EOF
source $WORK_PATH/$FILE_0;
source $WORK_PATH/$FILE_1;
source $WORK_PATH/$FILE_2; 
source $WORK_PATH/$FILE_3;

It can be seen that the shell is very simple. Log in to mysql and execute the specified sql file. MYSQL_ROOT_PASSWORD, WORK_PATH, FILE_0 are all environment variables.

Let's take a look at how to write the corresponding Dockerfile, as follows:

# Docker image of disconf mysql
# VERSION 0.0.1
# Author: bolingcavalry

#The basic image uses daocloud.io/library/mysql:8
FROM daocloud.io/library/mysql:8

#Author MAINTAINER BolingCavalry <[email protected]>

#Define the working directory ENV WORK_PATH /usr/local/work

#Define the directory that will be automatically executed by the container ENV AUTO_RUN_DIR /docker-entrypoint-initdb.d

#Define the sql file name ENV FILE_0 0-init_table.sql
ENV FILE_1 1-init_data.sql
ENV FILE_2 20151225.sql
ENV FILE_3 20160701.sql

#Define the shell file name ENV INSTALL_DATA_SHELL install_data.sh

#Create a folder RUN mkdir -p $WORK_PATH

#Copy the database initialization data file to the working directory COPY ./$FILE_0 $WORK_PATH/
COPY ./$FILE_1 $WORK_PATH/
COPY ./$FILE_2 $WORK_PATH/
COPY ./$FILE_3 $WORK_PATH/

#Put the shell file to be executed in the /docker-entrypoint-initdb.d/ directory, and the container will automatically execute this shell
COPY ./$INSTALL_DATA_SHELL $AUTO_RUN_DIR/

#Add executable permissions to the executable file RUN chmod a+x $AUTO_RUN_DIR/$INSTALL_DATA_SHELL

0-init_table.sql, 1-init_data.sql, 20151225.sql, 20160701.sql are the SQL files we want to execute, which are copied to the image file when constructing the docker image;

Please clone my github for the complete content: [email protected]:zq2599/docker_disconf.git . After cloning, enter the mysql folder inside and execute the command line docker build -t disconf_mysql:0.0.1 in this folder to build the image;

Then execute docker run –name mysqldisconf -e MYSQL_ROOT_PASSWORD=123456 -idt disconf_mysql:0.0.1 to start a container, and then execute docker logs -f mysqldisconf to view the container log. As shown in the red box below, you can see that the sh file we wrote to the specified location has been executed:

Let's go to the database to see if the data is really there. Execute docker exec -it mysqldisconf /bin/bash to enter the container.

After executing mysql -uroot -p123456 to log in to MySQL, the operation is as follows:

As you can see, operations such as show databases，show tables，select * from app can prove that SQL has been automatically executed after the container is created, achieving our goal.

Summarize

The above is what I introduced to you on how to automatically execute SQL statements when MySQL in Docker starts. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!