Cannot connect remotely to EC2 MySQL installation

Question

End of my tether, hours of variations...

I am trying to access installed MySQL running on an EC2 instances. Lots of searching (this seemed closest to my issues Connect to mysql on Amazon EC2 from a remote server), and I'm obviously missing something silly or mis-understanding.

EC2 Ubuntu, mysqld running fine, local web/app server working fine all connecting. I can mysql from local. I need external access to this dev machine as using iron.io which needs to reach the database from external.

I have

• ec2 security gruops all configured allowing 0.0.0.0
• I can ssh/http reach the server no problem
• configured mysqld
  • bind address=0.0.0.0 (have tried variations below)
  • granted access for my non-root user (myuser) from % e.g. GRANT ALL ON . to myuser@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
• ensured my ubuntu instance iptables are not configured e.g. i'm only using ec2 security group to protect the instance)

I have tried

• ssh into the server
• telnet to the public ip on the mysqld port 3306 to make sure its listening - see notes below
• remove bind address in /etc/mysql/my.cnf
• checking it is not a sock v port error: always connecting via port (locally checked e.g. mysql -h - localhost -u root -p --port=3306)
• bind address 0.0.0.0
• bind address EC2 local ip e.g. 172.
• bind address EC2 external ip e.g. 51.x
• mysql user grant privileges on %
• mysql user grant privileges on my personal external ip
• create another user, and only grant priviledgs to a specific database GRANT ALL PRIVILEGES ON mydb.* TO 'myuser2'@'%' IDENTIFIED BY 'password';
• repeated for %, my personal external ip
• adding root@'%' to the list and tried that user (based on this suggestion: Can't Connect to MySQL instance Remotely that is running on EC2 Instance (Not RDS))

(Always restarting server after)

Nothing works, the test always

$ mysql --host=54.x.x.x --port=3306 --user=myuser -p Enter password: ERROR 1045 (28000): Access denied for user 'myuser'@'54.x.x.x' (using password: YES)

I have noticed however that

1. SELECT * from information_schema.user_privileges; has myser with IS_GRANTABLE set to N: but I don't think this is a limiting factor (Why is GRANT not working in MySQL?)

2. netstat -a shows a different port & a socket listening!?

   The listening port in the netstat below is not 3306 BUT i've telneted externally to that port and it shows mysql IS listening

Can anyone suggest any step I've missed?

Extracts of db priviledges, my.cnf below

# user_privileges
# e.g. SELECT * from information_schema.user_privileges;
+--------------------------------+---------------+-------------------------+--------------+
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE          |  q1 |
+--------------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'             | def           | SELECT                  | YES          |
...
| 'myuser'@'%'                 | def           | SELECT                  | NO           |
| 'myuser'@'%'                 | def           | INSERT                  | NO           |
| 'myuser'@'%'                 | def           | UPDATE                  | NO           |


# 
]$ netstat -a | grep 'mysql'
tcp        0      0 *:mysql                 *:*                     LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     61212    /var/run/mysqld/mysqld.sock


$ telnet 54.x.x.x 3306
Trying 54.x.x.x...
Connected to ec2-54-x-x-x.us-west-2.compute.amazonaws.com.
Escape character is '^]'.
[
5.5.44-0ubuntu0.14.04.1+c<>d^+W?1*!e\{wdp&hZmysql_native_password


# my.cnf
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = localhost
# donts seem to work
bind-address            = 0.0.0.0
# does not work
# ec2 external ip
#bind-address            = 54.x.x.x
# ec2 internal ip
#bind-address            = 172.x.x.x

Answer 1

the best tool i found was sqlyog, (perhaps mysql workbench too), which would actually tell the hostname you are coming in from on the failed connect.

The reason I like it is that it keeps security tight, showing something like Access denied for user "fred123@hdm38.newyork.comcastbusiness.net". Note, that hostname, I just made up, but it is important, whatever yours is coming in. Otherwise it is wildcard time, something I won't do. You might choose to.

that is important because that hostname (hdm38.newyork.comcastbusiness.net) is the one i use during mysql create user as opposed to using wildcards like %

I let that connect above fail, but I note what the hostname is above. You will see it below.

A quick checklist

1) your remote user is connecting through an account that was created with appropriate user,host entries (look at output from select user,host from mysql.user order by 1,2)

CREATE USER 'santa'@'hdm38.newyork.comcastbusiness.net' IDENTIFIED BY 'mypassword';

With that above command we now have a new user that has a chance to get in. Cannot do anything. Cannot change to a db. Basically they can just do things like select now();

2) you have performed grants with flush privileges (at least the former)

GRANT ALL PRIVILEGES ON mydb123.* TO 'santa'@'hdm38.newyork.comcastbusiness.net';

Our user with the above command now can do anything in the mydb123 database/schema. Explore that above Grant command in the manual for fine-tuning access to the bare minimum for the users you create.

If you are new to mysql security, do not include the WITH GRANT OPTION until you research it.

There are those that would say have that hostname above to be '%' for steps 1 and 2. That means santa can connect from any host. The choice is yours. Start tight, loosen up once you are getting somewhere and have done the research on it.

3) you have modified my.cnf and changed bind-address away from 127.0.0.1 or localhost, in favor of 0.0.0.0

If bind-address is not 0.0.0.0, you are only connecting with ssh

4) you have modified my.cnf and have a rem'd out line #skip-networking. Even if you have to create the line just to rem it out, do it.

3/4 changes require mysql daemon restart

5) firewall issues. So for EC2, you need your AWS Security Group that is active for the instance to include the opening up of port 3306

Answer 2

I kept running into this same issue with mysqld 5.6.37 on Amazon Linux and the solution was to add the line bind-address = 0.0.0.0 to /etc/my.cnf below [mysqld] but above [mysqld_safe]

Answer 3

Thank you Drew,

Changing the bind-address to 0.0.0.0 in my.cnf solved the issue for me. I have installed virtualmin, so don't have to go through all that MySQL user creation and granting permission part, done that with the help of GUI. But I wasn't able to connect to the MySQL server remotely.

I have another VPS and it has no issues connecting remotely with the MySQL server, and I was not able to find the solution to connect to this EC2 instance's MySQL server remotely. I was looking for a solution for past few days, and when I followed your instructions and changed the bind-address, and restarted the MySQL server, it all worked fine for me.

Thanks again.