3

I'm trying to break down the task of doing AWS debugging on rubymine into smaller chunks. I would like to connect to the mysql server running on AWS. So I did the following:

first: Establish an ssh tunnel to forward all localhost requests to port 3307 to the port 3306 on AWS:

ssh -l ubuntu -i 'path/to/private/key/privateKey.cer' -L 3307:aws.port:3306 aws.port -N -v -v

second: connect to mysql on port 3307

mysql -h 127.0.0.1 -P 3307 -u root -p

problem: it fails with the following error on my host machine:

ERROR 1130 (HY000): Host '178.135.138.61' is not allowed to connect to this MySQL server

and the log on AWS outputs this:

debug1: Connection to port 3307 forwarding to 54.193.1.19 port 3306 requested.
debug2: fd 7 setting TCP_NODELAY
debug1: channel 2: new [direct-tcpip]
channel 2: open failed: connect failed: Connection refused
debug2: channel 2: zombie
debug2: channel 2: garbage collecting
debug1: channel 2: free: direct-tcpip: listening port 3307 for 54.193.1.19 port 3306, connect from 127.0.0.1 port 64938, nchannels 3

notes:

  • I made sure that the security group of the aws server i'm connecting to allows ssh connections on port 22
  • I made sure that /etc/hosts.deny on AWS doesn't have localhost or 127.0.0.1 listed.

ideas?

Community
  • 1
abbood
  • 1,127
  • 4
  • 13
  • 21
  • Your mysql config doesn't allow tcp connections from 178.135.138.61. Fix that. – Jenny D Dec 23 '13 at 07:40
  • @JennyD so do I fix that by putting that ip address into /etc/hosts.allow? I thought I had universal ssh access into my aws.. this is what my security group [looks like](http://postimg.org/image/pgba3vrfd/) – abbood Dec 23 '13 at 07:49
  • @JennyD i edited `/etc/mysql/my.cnf` and updated `bind-address` to `0.0.0.0`.. but still no luck.. ideas? – abbood Dec 23 '13 at 08:31

1 Answers1

9

The error message 

ERROR 1130 (HY000): Host '178.135.138.61' is not allowed to connect to this MySQL server

is coming from MySQL. To allow remote access for user root then you need to specifically allow this for the particular database, on the mysql server

mysql -u root -p
mysql> grant all privileges on somedatabase.* to 'root'@'178.135.138.61' identified by 'somepassword';
mysql> flush privileges;

That will allow the username root to connect to mysql from 178.135.138.61 with all privileges on somedatabase.

You should probably read through the MySQL Security documentation in particular the Access Control and User Management chapters.

user9517
  • 115,471
  • 20
  • 215
  • 297
  • is the `identified by 'somepassword'` part necessary? and if so.. how would i full command to connect to that database look like? i'm getting confused between all the passwords – abbood Dec 23 '13 at 08:40
  • 1
    @abbood: educate yourself by reading the documentation. – user9517 Dec 23 '13 at 08:45
  • @lain can't argue with you about that.. i'll def check it out (btw it worked! thanks!) – abbood Dec 23 '13 at 09:01
  • @lain is there an omission above, or is this a behavior I'm unfamiliar with? Isn't this implicitly creating a user '178.135.138.61'@'%' rather than 'root'@'178.135.138.61' as written? – Michael - sqlbot Dec 23 '13 at 12:18
  • @Michael-sqlbot: You're right - fixed thanks. – user9517 Dec 23 '13 at 12:29