Monday, April 2, 2012

How to setup a remote connection to MySQL/Error: mysql said host is not allowed to connect to this mysql server

First connect to the server where you are able to currently connect (i.e.: remote desktop and connect using localhost)

Next run the following command from the MySQL command line:

mysql> GRANT ALL ON *.* TO 'jsmith'@'192.168.1.%' IDENTIFIED BY 'PASSWORD';
mysql> commit;

What this just did is created a user named jsmith that can connect from the network 192.168.1.x.  This works well if you use DHCP and every time you connect is a different IP.

Next thing I had to do was set the password for the user because I was getting an "Access Denied" error

Run the following command:

mysql> use mysql;
Database changed
mysql> update user set password=PASSWORD("new password") where USER='jsmith';

I then tried to connect and was able to connect at that point.

This is just a testing database, but I couldn't get the remote connection for root to work.  I understand where in almost all instances you wouldn't want that.  If anyone figures out how to do that, please post the solution in the comments.  I got past the not allowed to connect, but can't get past the access denied.

Source:  Link  Link  Link  Link