79

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.

Jerry Chong
  • 7,954
  • 4
  • 45
  • 40
Mazzy
  • 13,354
  • 43
  • 126
  • 207

10 Answers10

110

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.

Abdullah Jibaly
  • 53,220
  • 42
  • 124
  • 197
  • 2
    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. – Madhavi Jouhari May 17 '17 at 17:41
  • 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. – eyalzba Jan 27 '19 at 11:33
  • @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). :) – XtraSimplicity Mar 28 '19 at 20:39
  • @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. – eyalzba Apr 01 '19 at 08:05
  • this one worked perfectly docker exec -it {{CONTAINER ID}} mysql -u {{USERNAME}} -p {{PASSWORD}} – Amit Dwivedi Jun 09 '21 at 08:42
54
docker exec -i some_mysql_container mysql -uroot -ppassword  <<< "select database();"
Laurent Picquet
  • 1,147
  • 11
  • 12
  • 4
    What does `<<<` mean? Why 3 of them not 2? – vladkras Jul 08 '17 at 17:36
  • 9
    '<<<' instructs the shell to take whatever follows it as stdin, similar to piping from echo. – Ayushya Aug 21 '17 at 17:34
  • 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. – James Selvakumar May 05 '20 at 01:57
  • @JamesSelvakumar the "<<<" did not work for me either. I used the -e argument instead to run a query. – nonethewiser Jul 28 '22 at 18:59
  • 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! – Joel Gray Nov 01 '22 at 20:07
40

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!

Nolwennig
  • 1,613
  • 24
  • 29
  • 13
    You can combine these steps like this: `docker exec -t -i container_mysql_name /bin/bash -c "mysql -uroot -proot"` – ocarlsen Nov 23 '18 at 03:12
25

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;"

avolkmann
  • 2,962
  • 2
  • 19
  • 27
  • 1
    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. – James Selvakumar May 05 '20 at 01:54
  • 2
    Rather than executing a hardcoded SQL query how do you run a .sql file instead? – Parag Kadam Aug 16 '20 at 16:05
  • 1
    @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) – Dwhitz Aug 31 '22 at 11:03
24

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
Jerry Chong
  • 7,954
  • 4
  • 45
  • 40
3

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;"
shizhen
  • 12,251
  • 9
  • 52
  • 88
William Pispico
  • 31
  • 2
2

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
Gustaf Naeser
  • 21
  • 1
2

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

ivor
  • 21
  • 2
0

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

Mucyo Christian
  • 11
  • 3
0

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>
Ghost_Coderz
  • 1
  • 1