Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 853
Luke Avatar asked Oct 19 '25 13:10

Luke


2 Answers

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

like image 135
Maggie Avatar answered Oct 22 '25 04:10

Maggie


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.

like image 30
Kostanos Avatar answered Oct 22 '25 02:10

Kostanos



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!