mysql: ask for password interactively when using docker exec

Question

Usually you would run the MySQL client to import an SQL file like this:

mysql -uexample_user -p example_database < filename.sql 

It will then ask for the password and after the correct password was provided it will execute the SQL statements in filename.sql. This interactive password prompt reduces the risk of the password ending up in the shell history.

When MySQL is running inside a Docker container one could assume that you could do the same using this command:

docker exec container_name mysql -uexample_user -p example_database < filename.sql 

But this is not the case. MySQL will not ask for a password when using this command.

What is the correct docker command to allow MySQL to ask for the password?

Answer 1

I did some testing, it seems doing it this way is not possible.

Generally, you can use the argument -it to start an interactive terminal with a pseudo-tty to get the password prompt from mysql, but this argument combination does not allow you to direct files into the command.

To use directed files you need to omit the -t argument and only use -i. Of course in that case you need to specify the password directly in the command. If you don't want the password to be visible or logged you can use a variable to "hide" it.

docker exec -i container_name mysql -uexample_user \ -p"$MYSQL_PASSWORD" example_database < filename.sql 

This is also the suggested way by the official MySQL Docker image documentation (see the section Restoring data from dump files at the end.

Answer 2

(I'm not familiar with docker, but ...)

The conventional way of getting a password into mysql without it being on the command-line is by using an additional Options File containing the password and referencing this file when invoking mysql:

$ docker exec container_name \ cat /home/fred/pwd.cnf [client] username=example_user password=MySqlPassword $ docker exec container_name \ mysql --defaults-file=/home/fred/pwd.cnf example_database < filename.sql 

See: https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html

I'm assuming you can do something like this because the SQL file you're reading in must also be "within" the container.