I have been searching for this query alot, found many similar post but yet couldn’t get there.
Problem:
I have MySQL Locally Installed, and I’m a beginner in docker, I have built my first docker app which has pymysql library inside the python app. But I’m unable to docker run it and access the docker, I understand docker is isolated and yet can’t access the MySQL.
But please sugggest me how to do it??
Tried this cmd:
docker run --add-host=“localhost:192.168.0.xx” --name back-container -p 5000:5000 back-image,
But Getting this error:
pymysql.err.OperationalError: (1045, “Access denied for user ‘root’@‘host.docker.internal’ (using password: YES)”)
Network wise, a container is seen as a remote machine. They dont share the same “localhost”.
Adding a --add-host=“localhost:192.168.0.xx” flags in your docker command will add a line in the /etc/hosts file inside the container, so you’re creating an alias for physical host as “localhost” which is un true.
What you want to do is :
Change your application code to use “database” instead of “localhost” as database host
Use a --add-host=“database:192.168.0.xx”
So everything will be clearer. Then follow the 1st answer here : https://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts
Because, from your mysqld perspective, your application’s connection is coming from a remote host and thus root have to be allowed to connected from a remote host (whose IP wont be known before hand).
I gave #bind-address=127.0.0.1 under my.ini file (under mysqld),
and also ran docker run --add-host=“database:192.168.0.xx” --name back-container -p 5000:5000 back-image,
and also did; ```
GRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’;
Still getting;;;; pymysql.err.OperationalError: (2003, “Can’t connect to MySQL server on ‘localhost’ ([Errno 99] Cannot assign requested address)”)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
But about this :
(emphasis mine)
You need to change the code (or the configuration) from the application living in that back-image docker image. Many images have an environement parameter to change the database hostname
I tried ```
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘password’;
So I gave; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'; But still error.
And my Python code is:
db = pymysql.Connect("localhost","root","root_admin223","safety_" )
This is how I access the mysql.
mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'root';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. TO 'root'@'%' IDENTIFIED BY 'root'' at line 1
Did you quoted a quote? The original answer of sebt3 was syntactically correct, but the version quoted by nishad123 was not. If I quote the original, I get the right syntax:
update:
Okay I was wrong. It was long time ago, when I set this on my database Now I tried I doesn’t work so I read the docs.
update 2:
So in MySQL 8 first create the user which can access from anywhere:
create user 'root'@'%' identified by 'password';
Then set the privileges
grant all on *.* to 'root'@'%'
It is still not enough for DBeaver
Public key retrieval is not allowed
but it works from the terminal. If you go to the driver properties in DBeaver and set useSSL to false it will from from there too.
Now I tried I doesn’t work so I read the docs.