Access denied for user 'root'@'localhost' with mariadb 10.4.8 docker container using docker compose and issue while attaching external volume
It will affect the new user and keys even if you create fresh container.
Remove mount location, as it will pick a user name and password from this location, also will not run your DB init script too.
volumes:
- dbvolume:/var/lib/mysql
Also you do not need
create database appdata;
As database already created at
MYSQL_ROOT_PASSWORD: root123
MYSQL_ROOT_USER: root
MYSQL_DATABASE: appdata
this step.
update:
remove the user root, as root already defined. you can try with
version: '3.7'
services:
mysql:
environment:
MYSQL_ROOT_PASSWORD: root123
MYSQL_DATABASE: appdata
image: mariadb
Or the second user should be different
version: '3.7'
services:
mysql:
environment:
MYSQL_ROOT_PASSWORD: root123
MYSQL_DATABASE: appdata
MYSQL_USER: test
MYSQL_PASSWORD: root123
image: mariadb
you can try with
docker exec -it container_name bash -c "mysql -u test -proot123"
or
docker exec -it mysql bash -c "mysql -u root -proot123"
If still issue, remove the DB image, pull a new one.
Or try image tag 10.1
If I remove container-volume then It is importing .sql script and running well and good.
if mount the location then the init script will not run as the container is expecting that there is already DB or try to remove named volume and create new one.
So mount the location, import the DB using MySQL command and use the mount location for next time.
Rajesh Hatwar
I am Java developer eager to learn hibernate and spring technology more.
Updated on December 24, 2020Comments
-
Rajesh Hatwar over 3 years
I am new to Docker, I was trying to crate docker container of mariadb for my application but when I start running mariadb container it shows Access denied for user 'root'@'localhost' (using password: YES) dockerfile
Following is the docker compose I am using.
version: '3' services: mysql: image: mariadb container_name: mariadb volumes: - dbvolume:/var/lib/mysql - ./AppDatabase.sql:/docker-entrypoint-initdb.d/AppDatabase.sql environment: MYSQL_ROOT_PASSWORD: root123 MYSQL_ROOT_USER: root MYSQL_USER: root MYSQL_PASSWORD: root123 MYSQL_DATABASE: appdata ports: - "3333:3306" volumes: dbvolume:
After trying for multiple times by referring few links I was able to connect my application to docker container but it failed to import AppDatabase.sql script at the time of creating docker container.
But now by using same docker compose file I am not able to connect mariadb to my application and I think even it's not importing SQL script to the database (based on logs I have observed).
Following is the docker log generated while running docker compose:
$ docker logs 3fde358ff015 2019-09-24 17:40:37 0 [Note] mysqld (mysqld 10.4.8-MariaDB-1:10.4.8+maria~bionic) starting as process 1 ... 2019-09-24 17:40:37 0 [Note] InnoDB: Using Linux native AIO 2019-09-24 17:40:37 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2019-09-24 17:40:37 0 [Note] InnoDB: Uses event mutexes 2019-09-24 17:40:37 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2019-09-24 17:40:37 0 [Note] InnoDB: Number of pools: 1 2019-09-24 17:40:37 0 [Note] InnoDB: Using SSE2 crc32 instructions 2019-09-24 17:40:37 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2019-09-24 17:40:37 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M 2019-09-24 17:40:37 0 [Note] InnoDB: Completed initialization of buffer pool 2019-09-24 17:40:37 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2019-09-24 17:40:37 0 [Note] InnoDB: Upgrading redo log: 2*50331648 bytes; LSN=21810033 2019-09-24 17:40:38 0 [Note] InnoDB: Starting to delete and rewrite log files. 2019-09-24 17:40:38 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes 2019-09-24 17:40:38 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes 2019-09-24 17:40:38 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2019-09-24 17:40:38 0 [Note] InnoDB: New log files created, LSN=21810033 2019-09-24 17:40:38 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2019-09-24 17:40:38 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2019-09-24 17:40:38 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2019-09-24 17:40:38 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2019-09-24 17:40:38 0 [Note] InnoDB: Waiting for purge to start 2019-09-24 17:40:38 0 [Note] InnoDB: 10.4.8 started; log sequence number 21810033; transaction id 14620 2019-09-24 17:40:38 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2019-09-24 17:40:38 0 [Note] Plugin 'FEEDBACK' is disabled. 2019-09-24 17:40:38 0 [Note] Server socket created on IP: '::'. 2019-09-24 17:40:38 0 [Warning] 'proxies_priv' entry '@% root@c980daa43351' ignored in --skip-name-resolve mode. 2019-09-24 17:40:38 0 [Note] InnoDB: Buffer pool(s) load completed at 190924 17:40:38 2019-09-24 17:40:38 0 [Note] Reading of all Master_info entries succeeded 2019-09-24 17:40:38 0 [Note] Added new Master_info '' to hash table 2019-09-24 17:40:38 0 [Note] mysqld: ready for connections. Version: '10.4.8-MariaDB-1:10.4.8+maria~bionic' socket: '/var/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
SQL Script I am trying to import:
create database appdata; use appdata; CREATE TABLE `appdatadetails` ( `Name` varchar(8) NOT NULL, `appIndex` int(11) NOT NULL, `connector` varchar(16) DEFAULT NULL, `intName` varchar(12) DEFAULT NULL, `intIndex` int(11) DEFAULT NULL, PRIMARY KEY (`Name`,`appIndex`) )
Please help me to understand what I am doing wrong, I have tried all possible solution posted on different blogs.
Update:
Latest Update:
I was able to up and running mariadb docker image with 10.1. But if I attach volume then still I am facing issue.
Docker Compose:
version: '3' services: mysql: image: mariadb:10.1 container_name: mariadb volumes: - container-volume:/var/lib/mysql - ./AppDatabase.sql:/docker-entrypoint-initdb.d/AppDatabase.sql environment: MYSQL_ROOT_PASSWORD: root123 MYSQL_DATABASE: appdata ports: - "3333:3306" volumes: container-volume:
And the log error message, If I attach container-volume volume.
Creating mariadb ... done Attaching to mariadb mariadb | 2019-09-25 6:56:26 140542855440384 [Note] mysqld (mysqld 10.1.41-MariaDB-1~bionic) starting as process 1 ... mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Using mutexes to ref count buffer pool pages mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: The InnoDB memory heap is disabled mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Compressed tables use zlib 1.2.11 mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Using Linux native AIO mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Using SSE crc32 instructions mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Initializing buffer pool, size = 256.0M mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Completed initialization of buffer pool mariadb | 2019-09-25 6:56:26 140542855440384 [Note] InnoDB: Highest supported file format is Barracuda. mariadb | InnoDB: No valid checkpoint found. mariadb | InnoDB: A downgrade from MariaDB 10.2.2 or later is not supported. mariadb | InnoDB: If this error appears when you are creating an InnoDB database, mariadb | InnoDB: the problem may be that during an earlier attempt you managed mariadb | InnoDB: to create the InnoDB data files, but log file creation failed. mariadb | InnoDB: If that is the case, please refer to mariadb | InnoDB: http://dev.mysql.com/doc/refman/5.6/en/error-creating-innodb.html mariadb | 2019-09-25 6:56:26 140542855440384 [ERROR] Plugin 'InnoDB' init function returned error. mariadb | 2019-09-25 6:56:26 140542855440384 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. mariadb | 2019-09-25 6:56:26 140542855440384 [Note] Plugin 'FEEDBACK' is disabled. mariadb | 2019-09-25 6:56:26 140542855440384 [ERROR] Unknown/unsupported storage engine: InnoDB mariadb | 2019-09-25 6:56:26 140542855440384 [ERROR] Aborting mariadb | mariadb exited with code 1
If I remove container-volume then It is importing .sql script and running well and good.
Updated with working script: Before I was using mariadb 10.4.8 or latest and facing issue(s) to access DB and attaching external volume.
Now I have downgraded (As suggested by @Adiii) and tried. It runs perfectlly and we no need to specify external: true in volumes service
version: '3' services: mysql: image: mariadb:10.1 container_name: mariadb volumes: - ./dbvolume:/var/lib/mysql - ./AppDatabase.sql:/docker-entrypoint-initdb.d/AppDatabase.sql environment: MYSQL_ROOT_PASSWORD: root123 MYSQL_DATABASE: appdata ports: - "3333:3306"
-
Rajesh Hatwar over 4 yearsThanks for your replay, but still I am not able to access mariadb container. I am keep getting ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
-
Adiii over 4 yearsagainst which user you are trying to login? try to debug
docker exec -it mysql bash -c "mysql -u root -proot"
-
Rajesh Hatwar over 4 yearsI have updated my post with screen shot, I have created a folder and crated docker compose yml file and executed docker-compose up and tried to access the container but it didn't help. I have used your second your script.
-
Adiii over 4 yearsTry to remove and i tested it, did you run thy debug script
-
Adiii over 4 yearsusing password yes is very clear, something wrong the with password. you can try provided both example. one should work
-
Rajesh Hatwar over 4 yearsI have tried by pulling different image but problem still persist. One thing I have oberved is, if I run docker exec -it mariadb bash -c "mysql -u test" without password it's going inside the DB!. But with password it is showing Access denied for user 'test'@'localhost' (using password: YES)
-
Adiii over 4 yearsLet us continue this discussion in chat.
-
Adiii over 4 yearstry to run with docker run command, seems like ENV not setting,
docker run --name some-mariadb -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mariadb:latest