How to login to MariaDB Docker Container via PhpMyAdmin?

[jchariot]

I am trying to deploy PHPMyAdmin and mariadb via docker-compose, but I ran into a docker problem.

Here is my docker-compose file:

version: '3'

networks:
  my_docker_network:

services:

  db:
    networks:
      - my_docker_network
    image: mariadb:10.3
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      - MYSQL_ROOT_PASSWORD=*****
      - MYSQL_DATABASE=*****
      - MYSQL_USER=myuser
      - MYSQL_PASSWORD=*****
    volumes:
      - ./data:/var/lib/mysql
      - ./init-db:/docker-entrypoint-initdb.d
      - ./my.cnf:/etc/mysql/my.cnf  # set bind-address           = 0.0.0.0
      - "/etc/timezone:/etc/timezone:ro"
      - "/etc/localtime:/etc/localtime:ro"
    restart: always
  pma:
    networks:
      - my_docker_network
    image: phpmyadmin/phpmyadmin
    ports:
      - ****:80
    environment:
      - PMA_HOST=db

On PhpMyAdmin, when I tried to login, I got the message:

mysqli::real_connect(): (HY000/1045): Access denied for user 'myuser'@'172.25.0.4' (using password: YES)

172.25.0.4 is IP of the docker container. So I couldn't login with this. Both root and myuser have @'172.25.0.4' so this makes me unable to login. What must be done in this case if I am to login?

[grooverdan]

The access denied error is a good indicate that at least your network connection is correct.

The 172.25.0.4 as part of the error message is the server advising you where the connection is coming from so that when you look at the users you look for the one that matches that address range.

Where the same MYSQL_USER,PASSWORD in this compose file when the ./data was empty? Does the password container special charters that may have confused the initialization process? By default user grants are created with 'myuser'@'%' meaning all hosts.

To resolve:

docker exec -ti db /bin/bash

See if mysql -u root -p with the $MYSQL_ROOT_PASSWORD can access the server.

Look at select user,host from mysql.user to see what users are created.

Use SHOW CREATE USER myuser@'%' to see the user defination. SHOW GRANTS FOR myuser@'%' to see if there are permissions on the database created.

Use SET PASSWORD to set a password for an existing user.

Use CREATE USER to make a new user if it doesn't exist, then GRANTALL ON database.* to myuser@'%' to ensure that that user was all the access.

[jchariot]

See if mysql -u root -p with the $MYSQL_ROOT_PASSWORD can access the server.

Nope. I got this:

myuser@ubuntu:~$ docker exec -ti docker-compose-for-my-system_db_1 /bin/bash
root@ce86b40d4531:/# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@ce86b40d4531:/#

So I couldn't access this.

[grooverdan]

Option from https://github.com/MariaDB/mariadb-docker/issues/362#issuecomment-806246634 (and use this for instigation).

An initfile.sql that contains (to cover all bases):

CREATE USER IF NOT EXISTS root@localhost IDENTIFIED BY 'thisismyrootpassword';
SET PASSWORD FOR root@localhost = PASSWORD('thisismyrootpassword');
GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION;
CREATE USER IF NOT EXISTS root@'%' IDENTIFIED BY 'thisismyrootpassword';
SET PASSWORD FOR root@'%' = PASSWORD('thisismyrootpassword');
GRANT ALL ON *.* TO root@'%' WITH GRANT OPTION;
CREATE USER IF NOT EXISTS myuser@'%' IDENTIFIED BY 'thisismyuserpassword';
SET PASSWORD FOR myuser@'%' = PASSWORD('thisismyuserpassword');
CREATE DATABASE IF NOT EXISTS databasename;
GRANT ALL ON databasename.* TO myuser@'%';

[NicoThien]

I had this problem too. I don't know where the mistake is. My solution: I have not entered any user names or passwords in the "docker-compose.yml". Only after installing the container did I assign the password for "root".

docker exec -it mariadb /bin/bash mysqladmin -u root password my_root_password

After that the access via "PhpMyAdmin" worked.

[Razorback360]

Same exact issue here. Any solutions?

[grooverdan]

Exactly the same solution. If you didn't create the users/passwords when the database was initialized, create them manually. If you don't have any user to create new users/passwords, use the initfile.sql above.