connection failed:Access denied for user 'root_admin'@'%' to database 'mydb' and remote access to the database.

[resparkman1]

Hello I am new to heliohost, I just deployed my php website and mysql database, and created user for the database, then replace the credentials in my codes so that my website can access the database properly. However I got this error; connection failed:Access denied for user 'root_admin'@'%' to database 'mydb' , even though  I already checked that all the credentials are correct. Any help would be greatly appreciated, and would reply if you need any further information. I am also planning to remotely access the mysql database via python code on my local machine using the mysql.connector python library to read and write records. This is the code I use;

import mysql.connector
connection = mysql.connector.connect(
    host="johnny.heliohost.org",
    user="root_admin",
    password="****",
    database="mydb"
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM accounts")

results = cursor.fetchall()




I have viewed answers about this and tried to follow  them but those answers seems a little outdated, thus, can you also tell me how can I remotely access my mysql database via python code on my local machine?

[wolstech]

I'm assuming you actually changed the username in the code? Your SQL users will all start with resparkman1_ , so the username shown above would never work.

Host name for remote mysql is Johnny.heliohost.org, port 3306.

[resparkman1]

14 minutes ago, wolstech said:

I'm assuming you actually changed the username in the code? Your SQL users will all start with resparkman1_ , so the username shown above would never work.

Host name for remote mysql is Johnny.heliohost.org, port 3306.

Thank you so much it now works, regarding the remote access to the database. I tried following one of the discussions here (that is dated 5 years ago) but I cannot seem to find in the plesk control panel where I can set the remote access panel to allow my IP address to remotely connect to my database. 

[wolstech]

You set the allowed IPs when you create/edit the user. If you go to your database list, and click the username attached to the database, at the very bottom of the user properties below the permissions checkboxes there's an option for Access Control where you set this up.

It looks like this:

 

[resparkman1]

1 hour ago, wolstech said:

You set the allowed IPs when you create/edit the user. If you go to your database list, and click the username attached to the database, at the very bottom of the user properties below the permissions checkboxes there's an option for Access Control where you set this up.

It looks like this:

 

Thanks a lot for answering, another question is that, is this remote access from my local machine to the mysql datbase encrypted? If no, are there ways where I can get and incorporate SSL/TLS certificate to the code to make the connection or network traffic between my Python code and the MySQL database encrypted ?

[wolstech]

Plesk does not support encrypted MySQL connections.

[resparkman1]

 

On 3/23/2023 at 7:40 PM, wolstech said:

You set the allowed IPs when you create/edit the user. If you go to your database list, and click the username attached to the database, at the very bottom of the user properties below the permissions checkboxes there's an option for Access Control where you set this up.

It looks like this:

 

I just want to clarify, if I select the option 'Allow remote connections from any host' does that mean I can now access the mysql databse from any machine or IP address with the right credentials?

[wolstech]

Correct. If you select that option the IP of the remote machine won't matter and any device can access it using those credentials.