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
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
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?)
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
解决方案
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
这篇关于无法远程连接到 EC2 MySQL 安装的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
