docker - root 계정접속 실패

- docker에 mysql8을 설치하고

MYSQL_DATABASE=mysql8
MYSQL_ROOT_PASSWORD=rootPasswd
MYSQL_USER=testUser
MYSQL_PASSWORD=userPasswd

해당 env처럼 ROOT_PASSWORD를 설정했으나 접속이 안됐다.

DB의 인코딩 문제일줄 알았지만 아니었고

해결 방법은 volume을 삭제해야 적용되는 것이었다.

 

 

---------- 해결못함 ----------

1. docker volume 삭제

docker volume prune

2. database service volume 삭제

docker volume rm mysql8

3. 컨테이너 다운, volume 제거

docker-compose down --volumes

---------- 해결못함 ----------

 

 

---------- 해결 방법 ----------

mysql-init.sql 파일을 만들어서

docker mysql이 실행시 해당 query가 실행되게 해서 root 패스워드 변경 성공!!

 

Reset root MySQL password in Docker

Bummer 🤬

Recently I had to restore a MySQL backup from a client. The backup was the Docker and MySQL files. Unfortunately the password was unknown and I had to reset it.

On the internet I found a lot of tutorials on how to do it if the MySQL server is on your local machine. Unfortunately, none helped me with the dockerized version. So here I compiled the steps to do it.

So, I had the following (simplified) docker-compose.yml file:

services:
  mysql:
    image: mysql:5.6
    ports:
      - '3306:3306'
    volumes:
      - ./storage/docker/mysql:/var/lib/mysql

We need to create a mysql-init.sql file with the SQL queries to reset the root password. Tip: this file can contain all kind of queries. Note: if you are using MySQL 5.6 or older, you need to replace `authentication_string` with `Password`.

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

Now, change the docker-compose.yml to mount the file in the docker container.

services:
  mysql:
    # ...
    volumes:
      # ...
      - ./mysql-init.sql:/tmp/mysql-init.sql

To reset the root user’s password it is necessary to run the following commands.

# stop the container if it is running
docker-compose stop mysql# enter the docker container without starting mysql server
docker-compose run mysql bash# start the MySQL server with the init file previously created
mysqld_safe --init-file=/tmp/mysql-init.sql &# wait 10 seconds for the server to finish starting up# test if the password has been changed
mysql -u root -p
# type the new password

 

참고 URL : https://marin-binzari.medium.com/reset-root-mysql-password-in-docker-d961c71285e4