1

I'm trying to allow remote access to a MySQL Workbench database I created for somebody to have full access to. I created the user and granted them full access, however, when I try to log in as that user from a different laptop, it says access denied (yes, I checked if the password was right).

I then saw on Google that you need to adjust the binding_address in the my.cnf file from MySQL but I can't seem to find where it's located (if it even exists). I saw other posts about this on here and tried all of the solutions but still had no luck.

If anybody has any advice on how to access or create a my.cnf to allow remote access for a MySQL database on mac Big Sur that would be great.

As of now, the host for the root user is 'localhost' (not sure if I should change this). The last time I changed this to my IP address, I was locked out of the database for some reason and had to restart everything and re-build the entire database. Below is the syntax I used to create the user I'm trying to have access the database:

The * resembles the rest of my IP address.

CREATE USER 'user'@'67.81.**.*' IDENTIFIED BY 'password3!';
GRANT ALL PRIVILEGES ON fsk TO 'user'@'67.81.**.*' WITH GRANT OPTION;
FLUSH PRIVILEGES;

After creating the user, I went into "Users and Privileges" in MySQL Workbench and manually checked all of the boxes to grant the user full access. Then, when I try and log in to the database from a different laptop, I get the following error:

"Failed to Connect to MySQL at 67.81.**.*:3306 with user user.
Unable to connect to localhost"

informatik01
  • 16,038
  • 10
  • 74
  • 104
IronMan18
  • 57
  • 6
  • Is the problem that the MySQL server cannot be reached at all like a connection cannot be established or is the problem that the MySQL server can be reached but you get an error message from the MySQL server? – Progman Mar 27 '21 at 14:47
  • Well, the problem is that when I try and log on from a different laptop and user after that user has been created on my laptop (where the database is), it says Access denied for 'user'@'localhost'. When I try and recreate the user to have my IP address instead of 'localhost' the same issue persists. – IronMan18 Mar 27 '21 at 15:15
  • When you can reach the MySQL server and it is responding with an "Access denied" error message than the issue is not about any configuration file. Instead the login are just invalid. Please [edit] your question to include the statements you used to add the new use to the MySQL database, explain in detail how you try to access the MySQL server from a different host and write the exact error message you get. When you try a login from a different host the error message shouldn't be in the form `'user'@'localhost'`, since it can't be "localhost". – Progman Mar 27 '21 at 15:19
  • Ok, I edited the question with the process in which I went about creating the user and logging in as them from their laptop. Interestingly, now I'm not getting Access Denied but instead I"m getting Unable to connect to localhost. – IronMan18 Mar 27 '21 at 15:34
  • Please check https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location and https://dev.mysql.com/doc/refman/8.0/en/option-files.html on where to look for the configuration files. Then [edit] your question to add the content of the MySQL server configuration file. It looks like it is a connection issue after all and not a problem with permissions or user authentication. – Progman Mar 27 '21 at 15:39

2 Answers2

1

To find out where my.cnf is, try:

mysql --help | more

somewhere near the top you will see (with probably different path's ):

Default options are read from the following files in the given order:
/usr/local/etc/my.cnf ~/.my.cnf

Or, in Mysql Workbench, on the Administration, Server Status page.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Hi Luuk, thank you for your advice. I tried the first option in the terminal and got three different paths to my.cnf. However, when running ls (path to my.cnf) there was no directory found for any of them - making me believe they don't exist. Similarly, I didn't find anything about my.cnf on the Server Status page. – IronMan18 Mar 27 '21 at 15:14
  • The first one that does exist is the one you need. If none existing one is found you can use a value from that list to create a new `my.cnf`. – Luuk Mar 27 '21 at 15:24
0

First check if it's a workbench or connection problem.

  1. In your windows run 'cmd' to open a terminal

Try both

2a) mysql -u user -p -h 67.81.**.* -P 3306

2b) mysql -u user -p -h > localhost -P 3306

  1. If the connection is good you will get a password prompt, see if you can connect with correct password.

If your connection is denied, just grant the permission

mysql >GRANT ALL ON [DatabaseName].* TO 'user'@'67.81.**.*' IDENTIFIED BY '[PASSWORD]';

If your connection is accepted here, it's like a workbench configuration problem.

Kaumadie Kariyawasam
  • 1,232
  • 3
  • 17
  • 35