CODEWITHSUNDEEP
mysqldocker
胡亦朗
胡亦朗

Reputation: 437

Change mysql password in Docker container

How could I change root password in docker container since the container stop automatically once I stop the mysql service.

Should I stop the mysql container and deploy a new one?

Upvotes: 29

Views: 92946

Answers (7)

Jeff
Jeff

Reputation: 1117

The other solutions proposed here didn't work for me, but I found this blog post by Wolfgang Gassler helpful, especially for those using docker compose.

Basically, the steps are:

  1. add command: --skip-grant-tables to your MySQL (or MariaDB) service in your docker-compose.yml.
  2. restart the service (i.e. docker compose up -d <db> where <db> is the name of your docker compose service).
  3. docker compose exec -it <db> bash to get a bash prompt in the service's container.
  4. mysql -uroot from the command line inside the container to get a MYSQL prompt, then enter the following 3 commands, replacing newpassword with your new password (obviously):
USE mysql;
FLUSH PRIVILEGES;
ALTER USER 'root'@'%'  IDENTIFIED BY 'newpassword';
  1. remove the command: --skip-grant-tables from your docker-compose.yml
  2. restart the service again

Hope this helps someone else. Cheers.

Upvotes: 4

luan.jpb
luan.jpb

Reputation: 1

In case you have forgot the password another idea is just do run

docker inspect container_name

It will print a bunch of useful information, including the envvar "MYSQL_ROOT_PASSWORD".

Upvotes: 0

VonC
VonC

Reputation: 1325966

You could change it from a running container, using a docker exec session, as described in "Connecting to MySQL Server from within the Container"

Once the server is ready, you can run the mysql client within the MySQL Server container you just started and connect it to the MySQL Server.
Use the docker exec -it command to start a mysql client inside the Docker container you have started, like this:

docker exec -it mysql1 mysql -uroot -p

When asked, enter the generated root password (see the instructions above on how to find it). Because the MYSQL_ONETIME_PASSWORD option is true by default, after you started the server container with the sample command above and connected a mysql client to the server, you must reset the server root password by issuing this statement for MySQL 5.7 and above :

mysql> update user set authentication_string=password('new_password') where user='root';

or alternatively run,

mysql> SET PASSWORD FOR 'root' = PASSWORD('new_password');

For MySQL 5.7 and older versions, run,

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Substitute newpassword with the password of your choice. Once the password is reset, the server is ready for use.

Note that the above command will only change the password for 'root' connecting from 'localhost' host. You can verify this by using the command:

select * from mysql.user;

To alter the password for 'root' from all hosts, use:

ALTER USER 'root'@'%' IDENTIFIED BY 'newpassword';

Then, as described in "hub.docker.com/mysql", dont forget docker secrets:

As an alternative to passing sensitive information via environment variables, _FILE may be appended to the previously listed environment variables, causing the initialization script to load the values for those variables from files present in the container.
In particular, this can be used to load passwords from Docker secrets stored in /run/secrets/<secret_name> files.
For example:

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root -d mysql:tag

xeruf points out in the comments to "MYSQL_ROOT_PASSWORD is set but getting "Access denied for user 'root'@'localhost' (using password: YES)" in docker container", adding:

If you mount a volume, make sure to clear it when changing the USER/PASSWORD!

Upvotes: 48

joshuabeny1999
joshuabeny1999

Reputation: 29

To reset the root Password in MYQL 8.0 unfortunatley the upcoming tipp from Martin Binzari does not work anymore completly. But you can do following: (According his manual and How to reset the root password in MySQL 8.0.11?)

  1. Create a mysql-init.sql
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
  1. Mount it to /tmp folder.
  2. Stop the container with docker-compose stop mysql
  3. Run docker-compose run mysql bash
  4. Inside run command mysqld_safe --init-file=/tmp/mysql-init.sql &
  5. Wait 10s, press enter and run mysql -uroot
  6. Then you could run command ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
  7. Finally you could exit and login shoud work with new root password.

Upvotes: 2

Anushri HV
Anushri HV

Reputation: 77

You can also set the password at the time of running the container using the -e option, which sets the environment variable MYSQL_ROOT_PASSWORD

docker run -d \
  --name=mysql5.7 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -p 3306:3306 mysql/mysql-server:latest

Upvotes: 2

Marin B&#238;nzari
Marin B&#238;nzari

Reputation: 5348

In case you forgot the old password, you can reset it following a few steps. I wrote a tutorial on how to do it (you can show you support by giving a few claps): https://medium.com/@marinbinzari/reset-root-mysql-password-in-docker-d961c71285e4

TLDR: create a mysql-init.sql file with the queries to reset the password:

USE mysql;
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
FLUSH PRIVILEGES;

Mount the file in the /tmp folder, run the container (not execute so MySQLD is not started) and then start the mysqld with the init script.

mysqld_safe --init-file=/tmp/mysql-init.sql &

Try to connect, exit the docker container and start using the new password 🤯

Oh, and never forget you password again 😅

Upvotes: 5

TIGER
TIGER

Reputation: 2905

First go to docker container bash using

docker exec -it containerId bash

Then

  • To Set the new mysql password

    mysqladmin -u root -p'oldpassword' password 'newpassword'

  • To set the password empty use

    mysqladmin -u root -p'oldpassword' password ''

Make sure there is no space between -p and oldpassword

Upvotes: 4

Related Questions