Question 214316 in Mysql Question posted in
The official MySQL documentation can be found here.

Access denied for user when creating MySQL database with Docker

I am trying to create a MySQL database using Docker and need to add a new user (user=user1, password=user1). I have configured the docker-compose file and set the environment variables accordingly:

version: '3.1'

services:
   db:
    image: mysql:8.0.33
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: my_test_database
      MYSQL_USER: user1
      MYSQL_PASSWORD: user1
    volumes:
      - ./database:/var/lib/mysql

However, whenever I run the setup, I encounter the following error message:

(pymysql.err.OperationalError) (1044, "Access denied for user
‘user1’@’%’ to database ‘my_test_database’") [SQL: USE Testtable1] (Background on this error at: https://sqlalche.me/e/14/e3q8)

How can I resolve this issue?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I managed to solve this problem. It seems that the administrator (root user) has to allow privileges to another user, in this case, user1. To do so, I followed these steps:

    • Eliminate the container and up again this way:
    docker compose down
docker-compose up -d
    • Enter with the new user, and show the GRANTS assigned:
    docker-compose exec db mysql -u user1 -p
SHOW GRANTS FOR 'user1'@'%';
    • Enter again as root user (administrator to grant privilege to this user user1:
    docker-compose exec db mysql -u root -p

GRANT ALL PRIVILEGES ON my_test_database.* TO 'user1'@'%';
FLUSH PRIVILEGES;

    After these steps, user1 had all privileges to use the database.

    But although it was solved this way, I still don't understand because, based on the documentation here, it says:

    MYSQL_DATABASE This variable is optional and allows you to specify the name of a database to be created on image startup. If a user/password was supplied (see below) then that user will be granted superuser access (corresponding to GRANT ALL) to this database.

    I understand that by assigning this variable with the name of the database (as I did in the docker-compose.yml file, the user has all privileges automatically. However, it did not work in my case, and I had to solve it with the steps I explained above.


  2. 0

    Here is my Docker Compose file that is working with MySQL, perhaps there is an error because you didn’t name the database under volumes?

    version: '3.8'

services:
  db:
    image: mysql:latest
    restart: always
    environment:
      MYSQL_DATABASE: 'db'
      MYSQL_USER: 'username'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'password'
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - my-db:/var/lib/mysql
volumes:
  my-db:
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search