-1

I am getting this error. I have deployed my Laravel app on a vps with docker.

SQLSTATE[HY000] [1045] Access denied for user 'root'@'some_ip_address' (using password: YES)

My database configuration in docker-compose.yml file:

  database:
    image: public.ecr.aws/docker/library/mysql:5.7.41
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: ${DB_DATABASE}
      MYSQL_ROOT_PASSWORD: F450pH3Af*v9kGfLzfYxgSAlH1V@PnZ*pC4
    volumes:
      - db-data:/var/lib/mysql

My .env file:

DB_CONNECTION=mysql
DB_HOST=database
DB_PORT=3306
DB_DATABASE=some_db_name
DB_USERNAME=root
DB_PASSWORD=secret

I should mention that everything runs perfectly in my local development environment.

What can I do to get rid of this error? Thank you very much.

I have tried granting privileges to the root user but that also did not solve my problem.

AramDonyaee
  • 1
  • `root` user is normally setup to only be allowed to be used from the localhost PC. That reduces the likelyhood of someone breaking in using the SuperUser – RiggsFolly Mar 21 '23 at 15:16
  • @RiggsFolly Thats right, then what you are suggesting to solve this problem? – AramDonyaee Mar 21 '23 at 15:18
  • I would suggest creating a Site User account, you should never use root for anything other than emergencies. Set that to be allowed to access from your other IP Addres – RiggsFolly Mar 21 '23 at 15:18
  • I have created a non-root user with sudo privileges in my VPS OS. I am running all of the docker related command logged in as that non-root user. However, my question is about docker and the mysql service resides in a container. I am just confused! – AramDonyaee Mar 21 '23 at 15:24
  • Yes, the new user I was suggesting would have been a MySQL User account and not an OS user – RiggsFolly Mar 21 '23 at 15:32
  • Thank you for your kind clarification. I will try doing that. – AramDonyaee Mar 21 '23 at 15:37
  • Does this answer your question? [MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)](https://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw) – miken32 Mar 21 '23 at 17:12
  • @miken32, Thank you for your suggestion, but that link did not answer my question unfortunately. However, I removed the volume associated with the container with docker-compose down -v command and now I am confronting with a connection refused error. – AramDonyaee Mar 21 '23 at 19:51

1 Answers1

0

I see two possible solutions here

First solution would be looking at the DB_PASSWORD=secret on your .env file. Since you posted your actual MYSQL_ROOT_PASSWORD here, I am assuming you are not using the same value in your .env file. Otherwise upgrade to mysql 8.0 ( this one simply because it's better ) and then set MYSQL_USER and MYSQL_PASSWORD and use those values for your connection instead of the root user credentials. Also listen on 127.0.0.1:3306 instead of 0.0.0.0:3306 if you are not required to connect externally to that database.

This is an example configuration that works for me:

  database:
    image: mysql:8.0
    restart: unless-stopped
    volumes:
      - db-data:/var/lib/mysql
    environment:
      - "MYSQL_ROOT_PASSWORD=rootpassword"
      - "MYSQL_USER=dbuser"
      - "MYSQL_PASSWORD=dbpassword"
      - "MYSQL_DATABASE=dbname"
    ports:
      - "${HOST_IP:-127.0.0.1}:${MYSQL_PORT:-3306}:3306"
ThatDevOpsGuy
  • 1
  • 1
  • Thank you very much! I have used a configuration like the one you are suggesting before: MYSQL_DATABASE: ${DB_DATABASE} MYSQL_ROOT_USER: ${DB_USERNAME} MYSQL_PASSWORD: ${DB_PASSWORD} MYSQL_ROOT_PASSWORD: ${DB_PASSWORD} but that didn't work! Do you think that is because I am using mysql 5.7? – AramDonyaee Mar 21 '23 at 15:34
  • The error message does not indicate that it is related to the mysql version It could be that the root user does not allow remote logins. If that is the case, it could be linked to the mysql version of that image registry public.ecr.aws/docker/library/mysql – ThatDevOpsGuy Mar 22 '23 at 11:57