How to run a query in docker container after mariadb has started?

Question

I am attempting to create a mariadb container, and want to have a SQL script start at the beginning.

Base Dockerfile

FROM mariadb:10.5.6
# Copy a "creation.sql" script to the container
COPY /sql/* /sql/

Manual Execution Works

If I wait 10-20 seconds after the container is started, then do a command line docker exec -it my_container_name mysql -e "source sql/creation.sql;" -uroot -pMyPasswordHere then the script runs fine. But I would rather have this part of the build or run processes, rather than requiring a manual step and a guess at waiting time.

RUN - Doesn't work

I tried using RUN in the Dockerfile:

RUN mysql -e "source sql/creation.sql;" -uroot -p$MYSQL_ROOT_PASSWORD`

but it fails with: Can't connect to local MySQL server through socket during the build. I assume this is because the service hasn't started yet.

CMD - Doesn't work

I also tried executing creation.sql with mysql using ENTRYPOINT and CMD, but they seems to fail during the run. An example of CMD solution that should call mysqld (matching what's in the mariadb Dockerfile) and then run the sql, but fails when running the container:

FROM mariadb:10.5.6
COPY /sql/* /sql/
CMD mysqld && mysql -e "source sql/creation.sql;" -uroot -p$MYSQL_ROOT_PASSWORD

CMD with shell script - Doesn't work

An example of a script-based solution which also fails:

FROM mariadb:10.5.6
COPY /sql/* /sql/
COPY /scripts/* /scripts/
CMD ["./scripts/start.sh"]

start.sh:

exec mysqld
echo "Running start script\n" > start.log
for i in {1..20}
do
    sleep 3
    echo "Attempt $i"
    echo "Attempt $i" &>> start.log
    mysql -e "source sql/creation.sql;" -uroot -p$MYSQL_ROOT_PASSWORD &>> start.log
done
echo "Done" &>> start.log

Is there a simple solution?

Is there any way to run a SQL script file just with the Dockerfile, or maybe a small shell script?

Answer 1

Looking at the MariaDB docs on Docker Hub, it looks like there is a process built in for executing secondary scripts:

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, .sql.gz, and .sql.xz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. .sh files without file execute permission are sourced rather than executed. You can easily populate your mariadb 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 MARIADB_DATABASE / MYSQL_DATABASE variable.

https://hub.docker.com/_/mariadb

Answer 2

You could add the command as a CMD line:

FROM mariadb:10.5.6

# Copy a "creation.sql" script to the container
COPY /sql/* /sql/

CMD mysql -e "source sql/creation.sql;" -uroot -pMyPasswordHere

You can also call CMD from a docker-compose.yml file:

version: '3'
services:
  service:
    ...
    command: 'command-to-start'

You may need to add a line in there to make it sleep for a while to ensure the process starts ok before your script runs. You could put it in a script, but if need be could include it in the CMD line as the first command followed by your script: CMD first-command-to-sleep && second-command.

If you find the CMD option is preventing MariaDB from starting, you may need to add in your own request for the process to start. A look at their Dockerfile (https://github.com/MariaDB/mariadb-docker/blob/master/10.5/Dockerfile) suggests it is:

CMD ["mysqld"]

So you would have CMD first-command & second-command & mysqld

& will make both run at the same time, whereas && will wait for the first to finish successfully before running: Can a bash script run simultaneous commands then wait for them to complete?

This will execute as a shell process though, rather than exec which has some disadvantages: https://til.codes/docker-run-vs-cmd-vs-entrypoint/

Other options include putting all that into a shell script, and calling it on launch: CMD ["start.sh"]. Here you would make sure that the last command mysqld is executed as exec mysqld which then ensures it is the main process executed and like running CMD ["mysqld"].

Or using a service like SupervisorD if you wanted to make your own service container: http://supervisord.org

Or give each process its own container. Leaving the standard MariaDB container alone, then run a container with a script to call commands or execute custom processes. This of course will all depend on what it is you are trying to do and how much interaction you need with the contents of the container. If calling a docker container command, you could mount the docker socket into your container, install docker and then be able to execute the docker exec -it my_container_name mysql -e "source sql/creation.sql;" -uroot -pMyPasswordHere command from within it: Access Docker socket within container. This may be a good option if you see there is a need to build a supervisor container that does lots of custom processes across your app.

As always, there are many ways to go about this. I would start by trying:

CMD mysql -e "source sql/creation.sql;" -uroot -pMyPasswordHere

If mariadb doesn't start because it has overridden the default CMD process, then would probably explore the start.sh solution.

I'm not familiar with MariaDB, but I would also explore whether command you are trying to run can be passed on as a config file or appended to the start command: mysqld --variable --etc.: https://hub.docker.com/_/mariadb/

Answer 3

I want to add the few other alternative ways to run queries inside the already run container

• Run from file

docker exec -i <Your_container_name> sh -c 'exec mariadb -uroot -p"$MARIADB_ROOT_PASSWORD" <Your_DB_name>' < ./your_sql_file.sql

• Run a query/ies from command line. I run a Create table and grant privileges, but you can replace it with any query that you have in mind.

docker exec -i <your_container_name> sh -c 'exec echo "CREATE DATABASE IF NOT EXISTS tp_old;GRANT ALL PRIVILEGES ON *.* TO 'some_user';" | mariadb -uroot -p"$MARIADB_ROOT_PASSWORD"'

PS. My example is for mariadb, but it should equally work for mysql as well.