++ cat docker-compose.yml
services:
db:
image: mysql
environment:
MYSQL_DATABASE: mydb
MYSQL_ROOT_PASSWORD: root
++ docker-compose run db mysql -h db -u root -p mydb < query
Creating oc6gh3permanent_db_run ... done
Enter password: ERROR 1045 (28000): Access denied for user 'root'@'172.31.0.3' (using password: YES)
ERROR: 1
Why?
The strange thing is that, if the input file (< query) is not used, it works.
++ docker-compose run db mysql -h db -u root -p mydb
Creating oc6gh3permanent_db_run ... done
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
The reason why you are encountering an access denied error when trying to execute a SQL query using mysql command inside the container is that you are not specifying the MySQL password correctly.
In your Docker Compose file, you’ve set the MYSQL_ROOT_PASSWORD environment variable to root, which means that the root user password for MySQL is set to root. However, when you run the mysql command inside the container, you’re passing the -p option followed by mydb, which is not the correct password for the root user.
To fix this issue, you need to pass the correct password to the mysql command.
++ docker-compose run db mysql -h db -u root -p=root mydb
Creating oc6gh3permanent_db_run ... done
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'172.31.0.3' (using password: YES)
ERROR: 1
That is not the password. -p is a flag to ask for password interactively. mydb is the database to connect to by default.
What kind of query did you use? Without the query you just connect to the database and do nothing. Have you tried to execute the same query after successfully connecting to the database?
Without containers you could create an account in mysql that would be able to connect to the database from a specific IP address. As far as I know, the default user is 'root'@'localhost', which means you can only access from localhost (the container’s localhost thorugh mysql’s unix socket). To allow other IP addresses you need an account like 'root'@'172.31.0.3'. In case of containers it is not practical, as the IP address can change any time. IF you don’t forward external ports to the container, you can create a user which is allowed to access from anywhere: 'root'@'%'. You can then create an external docker network called “database” and add that network to containers that needs to access the database. You can still restrict which database the container can connect to.
If you are laughing at posts trying to help, then good luck next time.
By the way your new post made me realize that you basically sent the query to mysql as a password when -p asked for password. The part of my message about the account names still true, it just doesn’t apply to you this time.
