How to execute MySQL command from the host to container running MySQL server?

Question

I have followed the instruction in https://registry.hub.docker.com/_/mysql/ to pull an image and running a container in which it runs a MySQL server.

The container is running in the background and I would like to run some commands.

Which is the best way to connect to the container and execute this command from command line?

Thanks.

Augsburg

Answer 1

You can connect to your mysql container and run your commands using:

docker exec -it mysql bash -l

(Where mysql is the name you gave the container)

Keep in mind that anything you do will not persist to the next time your run a container from the same image.

Spectacles

rpc error: code = 2 desc = oci runtime error: exec failed: container_linux.go:247: starting container process caused "exec: \"-it\": executable file not found in $PATH" getting this erro with your solution. – Jubilant

Why do you say that anything done will not persist? This depends on other parameters (mainly where the actual database data is - is it mounted to host using volume?) and is not related to docker exec. – Helse

@eyalzba, he doesn't mount anything in his docker command. If he added -v ./mysql:/var/lib/mysql or similar, it would persist - but he hasn't. Remember, this is using plain-old docker, not docker-compose (so what you see is all that has been defined). :) – Niue

@XtraSimplicity, you are saying exactly the same thing as I said. I only added that I don't know if he ran with "-v" or without. – Helse

this one worked perfectly docker exec -it {{CONTAINER ID}} mysql -u {{USERNAME}} -p {{PASSWORD}} – Sash

Answer 2

docker exec -i some_mysql_container mysql -uroot -ppassword  <<< "select database();"

Stellarator

What does <<< mean? Why 3 of them not 2? – Alikee

'<<<' instructs the shell to take whatever follows it as stdin, similar to piping from echo. – Diadiabase

Thank you very much. I was looking at how to create an alias to login into mysql in just one command and this answer helped me. As explained by @Andreas Volkmann, I had to use the "-e" option before I execute queries. The "<<<" option didn't work in my case. – Iatrochemistry

@JamesSelvakumar the "<<<" did not work for me either. I used the -e argument instead to run a query. – Scoreboard

This answer worked brilliantly for me. I wanted to run some set up commands for a fresh mysql docker instance from a bash script. Perfect solution! – Lamar

Answer 3

To connect to the MySQL database using MySQL command line client.

1. I connect to the bash into the running MySQL container:

   $ docker exec -t -i container_mysql_name /bin/bash

   -i is the shortcut for --interactive option. This options is used for keep STDIN open even if not attached

   -t is the shortcut for --tty option, used to allocate a pseudo-TTY

2. I run MySQL client from bash MySQL container:

   $ mysql -uroot -proot

   -u is shortcut for --user=name option, used to define user for login if not current user.

   -p is shortcut for -password[=name] option, used to define password to use when connecting to server. If password is not given it's asked from the tty.

3. Disco!

Asgard

You can combine these steps like this: docker exec -t -i container_mysql_name /bin/bash -c "mysql -uroot -proot" – Modern

Answer 4

In my case the <<< solution did not work.

Instead I used -e.

Example:

docker exec ${CONTAINER_NAME} mysql -u ${USER_NAME} -p${PASSWORD} -e "drop schema test; create schema test;"

Tishtisha

I was trying to create an alias to login into mysql in just one command and this particular solution helped me. Thank you very much. – Iatrochemistry

Rather than executing a hardcoded SQL query how do you run a .sql file instead? – Derickderide

@ParagKadam you can do it as follow: docker exec -i mySqlContainer mysql -uroot -pmypassword < file.sql (maybe it's too late, but can be helpful for someone else) – Galcha

Answer 5

For @Abdullah Jibaly solution, after tested in MySQL 5.7, it would only entered into bash terminal prompt, whereby you still need to enter mysql command second time.

In order to directly enter into MySQL command line client after run MySQL container with one line of command, just run the following:

docker exec -it container_mysql_name mysql -u username -p

Surrebutter

Answer 6

Its possible with docker run, start a new container just to execute your mysql statement. This approach helped me to workaround the access denied problem when you try to run a statement with docker exec using localhost to connect to mysql

$ docker run -it --rm mysql mysql -h172.17.0.2 -uroot -pmy-secret-pw -e "show databases;"

Pebbly

I don't believe this will work because the mysql... command overrides the image's default command mysqld. Therefore mysql won't be running in the container and can't respond to connections. – Reiff

Answer 7

I use the following to create a command that will sort out at least a couple of cases with databases outside or inside the container (with -h and -P) and supporting -e:

cat > ~/bin/mysql <<'EOF'
#/bin/bash

MARGS=()
MPORT="3306"

while test $# != 0; do
  if [[ $1 == -h ]]; then MHOST=$2; shift;
  elif [[ $1 == -h* ]]; then MHOST=${1#"-h"};
  elif [[ $1 == -e ]]; then MEXEC=$2; shift;
  elif [[ $1 == -e* ]]; then MEXEC=${1#"-e"};
  elif [[ $1 == --execute=* ]]; then MEXEC=${1#"--execute="};
  elif [[ $1 == -P ]]; then MPORT=$2; shift;
  elif [[ $1 == -P* ]]; then MPORT=${1#"-P"};
  else MARGS="$MARGS $1"
  fi

  shift;
done

if [ -z  "${MHOST+x}" ]; then
   MHOST=localhost
fi

if [ $(docker inspect --format '{{ .State.Status }}' mysql) == "running" ]; then
 if [ ! -z "${MHOST+x}" ]; then
    if [ "$MHOST" == "localhost" -o "$MHOST" == "127.0.0.1" ]; then
      CPORT=$(docker port mysql 3306/tcp)
      if [ ${CPORT#"0.0.0.0:"} == $MPORT ]; then
        #echo "aiming for container port ($MPORT -> $CPORT)";
        MHOST=$(docker inspect --format '{{ .NetworkSettings.IPAddress }}' mysql);
      else
        MHOST=$(ifconfig | sed -En 's/127.0.0.1//;s/.*inet (addr:)?(([0-9]*\.){3}[0-9]*).*/\2/p' | head -1);
      fi
    fi
  fi
fi

if [ -z "$MEXEC" ]; then
   docker run --link mysql:mysql -i --rm mysql mysql "-h" $MHOST "-P" $MPORT $MARGS
else
   docker run --link mysql:mysql -i --rm mysql mysql "-h" $MHOST "-P" $MPORT $MARGS <<< $MEXEC
fi
EOF
chmod +x ~/bin/mysql

Uncommunicative

Answer 8

i didn't find any of these solutions to be effective for my use case: needing to store the returned data from the SQL to a bash variable.

i ended up with the following syntax when making the call from inside a bash script running on the host computer (outside the docker mysql server), basically use 'echo' to forward the SQL statement to stdin on the docker exec command.

modify the following to specify the mysql container name and proper mysql user and password for your use case:

#!/bin/bash
mysqlCMD="docker exec -i _mysql-container-name_ mysql -uroot -proot "
sqlCMD="select count(*) from DBnames where name = 'sampleDB'"
count=`echo $sqlCMD | $mysqlCMD | grep -v count`

# count variable now contains the result of the SQL statement

for whatever reason, when i used the -e option, and then provided that string within the back-quotes, the interpreter modified the quotation marks resulting in SQL syntax failure.

richard

Adhibit

Answer 9

docker exec -it <mysql_container_name> mysql -u <user> -p

here -it means interactive + tty, basically allowing you to interact MySQL running inside the docker container. A heads up is if your container name is mysql then your command is

docker exec -it mysql mysql -u <user> -p

The first mysql is the container name and the second mysql invokes MySQL client, sometimes people forget the second one, which will cause this error:

OCI runtime exec failed: exec failed: unable to start container process: exec: "-u": executable file not found in $PATH: unknown

Kabob

Answer 10

I used the following command. However in case your image is defined in docker compose.

sudo docker compose exec -it companydb mysql -u company -p company

companydb: The service pulling mysql image and also identified as container_name

Concrete

Answer 11

Creating a MySQL Docker Container Image :

Command :

docker run --name <cotainer-name> -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

The -e is used to set the environmental variables of the Docker container image.
If you are not sure about which mysql image tab to use, use mysql:latest

Running a MySQL Queries through MySQL Client on Docker Container Image :

Command :

1. docker exec -it <cotainer-name> bash -l
2. mysql -n<username> -p<password>

Jumna