Mysql – When connecting to thesql on ec2, what should I be using

amazon ec2hostnameMySQL

I'm having issues connecting to my local mysql server currently, but I need help in being able to connect to mysql from other instances also.

Background: I have a java service that is installed on a single instance, along with mysql. Other instances will also have this java service that need to connect to mysql.

Currently my /etc/hostname shows:

ip-xx-xx-xx-xxx

What should I be using to connect? I tried localhost just to see if it could connect, and it didn't work. I tried just the local ip also, and that didn't work either.

If I just try and connect to mysql locally this works:

mysql -u root -p123

But this fails:

mysql -h localhost -u root -p123

(So it fails whenever I add the -h switch, localhost, 127.0.0.1 or the internal ip addresses all fail with the error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

I tried just the local ip, that didn't work, and the string 'ip-xx.xx.xx.xxx' and that didn't work either.

I'm clearly doing something wrong, what should I do?

Best Answer

Listening (TCP) ports can be found with sudo netstat -tlnp.

The reason for the name instead of the IP address is that EC2 supplies reverse DNS lookups and your IP address resolves to that name. MySQL can use reverse DNS lookups in authentication but this is not recommended as anyone who owns an IP address can set the reverse lookup to whatever they like, circumventing the source protection.

this error: 'root'@'ip-xx-xx-xx-xxx.ec2.internal'

That looks like half an error message, the first half probably said something like ERROR 1045 (28000): Access denied for user and the last part said (using password: NO).

The error message tells us two things:

  1. MySQL is running and is listening on that IP address and the default port.
  2. Either the username or password you are using is wrong. Possibly both.

If you haven't yet logged in or changed the password for MySQL, the default is for there to be a root user with no password. Leave off the -p option or, when it asks for your password, just hit Enter.

Related Topic