[28000][1045] Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES)

[Scxw010516]

I'm currently encountering a problem.
I'm using the mysql:latest image (which should be 9.3.0), and I'm using the following docker-compose.yml configuration to start my container. At this time, everything is working normally, and my Datagrip can connect properly.
But when I restarted MySQL with docker restart mysql, I encountered the problem [28000][1045] Access denied for user 'root'@'192.168.137.1' (using password: YES). Then, I entered the MySQL container, but I also failed [28000][1045] Access denied for user 'root'@'localhost' (using password: YES).I tried the above steps many times, but I encountered the same problem.
But when I switched to mysql:8.3.0, this problem disappeared.

Here is my docker-compose.yml:

mysql:
    image: mysql:8.3.0
    container_name: mysql
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: fUt8uxRCj#^TKoH
      TZ: Asia/Shanghai
    volumes:
      - "./mysql/conf:/etc/mysql/conf.d"
      - "./mysql/data:/var/lib/mysql"
      - "./mysql/init:/docker-entrypoint-initdb.d"
    networks:
      network:
        ipv4_address: 177.8.0.18

And my .cnf file:

[client]
default_character_set=utf8mb4
[mysql]
default_character_set=utf8mb4
[mysqld]
server-id = 1
log-bin=mysql-bin
binlog_expire_logs_seconds = 2592000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
max_connections=1000
symbolic-links=0
default-time_zone = '+8:00'

[yosifkit]

Was the mount for the mysql data ("./mysql/data:/var/lib/mysql") initially created with mysql:8.3.0? I'm not sure that upgrading from Innovation 8.3 to Innovation 9.3 is a supported upgrade path. I don't see it on https://dev.mysql.com/blog-archive/introducing-mysql-innovation-and-long-term-support-lts-versions/.

[Scxw010516]

8.3.0 is the configuration that I can run normally afterwards. Whenever there are problems, it's always 9.3.0.

[Scxw010516]

When there's a problem, I use the same image (mysql:latest) throughout the whole process : from compose (create /data) to Docker startup to successfully connecting to the database to restarting to connection failure to retrying after I delete /data.

[tianon]

I can't reproduce with your config as-is (removed only the port mapping, explicit network, and the init volume whose contents I do not have):

$ find -ls
 40794332      4 drwxr-xr-x   4 tianon   tianon       4096 Jun 24 14:08 .
 40794727      4 drwxr-xr-x   2 tianon   tianon       4096 Jun 24 14:08 ./data
 40794726      4 drwxr-xr-x   2 tianon   tianon       4096 Jun 24 14:07 ./conf
 40793695      4 -rw-r--r--   1 tianon   tianon        331 Jun 24 14:07 ./conf/foo.cnf
 40794346      4 -rw-r--r--   1 tianon   tianon        221 Jun 24 14:06 ./docker-compose.yml

$ cat docker-compose.yml
mysql:
    image: mysql:latest
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: fUt8uxRCj#^TKoH
      TZ: Asia/Shanghai
    volumes:
      - "./conf:/etc/mysql/conf.d"
      - "./data:/var/lib/mysql"

$ cat conf/foo.cnf
[client]
default_character_set=utf8mb4
[mysql]
default_character_set=utf8mb4
[mysqld]
server-id = 1
log-bin=mysql-bin
binlog_expire_logs_seconds = 2592000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
max_connections=1000
symbolic-links=0
default-time_zone = '+8:00'

$ docker-compose up -d
Creating mysql ... done

$ docker exec mysql mysql -uroot -p'fUt8uxRCj#^TKoH' -e 'SELECT 1'
mysql: [Warning] Using a password on the command line interface can be insecure.
1
1

$ docker restart mysql
mysql

$ docker exec mysql mysql -uroot -p'fUt8uxRCj#^TKoH' -e 'SELECT 1'
mysql: [Warning] Using a password on the command line interface can be insecure.
1
1

[Scxw010516]

Fortunately, I tried mysql:latest again today, with the same device, the same configuration, and the same steps, but I didn't encounter any problems. Unfortunately, I'll never know why there were problems that day.
Thank you for your patience.