I would like to access MySQL remotely on an Amazon EC2 CentOS instance (I assume it is CentOS because I use yum to update installed applications and libraries).
I believe that iptables is set properly, that my.cnf is set properly, and that the MySQL user is properly set.
I list all relevant settings in the sections that follow.
Contents of iptables:
# Generated by iptables-save v1.4.18 on Tue Mar 3 18:02:27 2015
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [2620:387248]
[0:0] -A INPUT -i lo -j ACCEPT
[9189:1027277] -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
[216:12376] -A INPUT -p tcp -m tcp --dport 22 -j ACCEPT
[0:0] -A INPUT -s 24.91.66.190/32 -p tcp -i eth0 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
COMMIT
# Completed on Tue Mar 3 18:02:27 2015
Contents of my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
bind-address=0.0.0.0
port=3306
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Relevant MySQL setup:
mysql> select user, host from mysql.user;
+-------------+--------------+
| user | host |
+-------------+--------------+
| root | 127.0.0.1 |
| dan | 24.91.66.190 |
| root | ::1 |
| dan | localhost |
| glassbiller | localhost |
| root | localhost |
+-------------+--------------+
6 rows in set (0.00 sec)
and
mysql> select user, host, db, select_priv, insert_priv from mysql.db;
+-------------+--------------+------------------+-------------+-------------+
| user | host | db | select_priv | insert_priv |
+-------------+--------------+------------------+-------------+-------------+
| dan | 24.91.66.190 | glassbiller_test | Y | Y |
| glassbiller | localhost | glassbiller\_% | Y | Y |
| dan | localhost | glassbiller_test | Y | Y |
+-------------+--------------+------------------+-------------+-------------+
3 rows in set (0.00 sec)
Details of server and client MySQL tool:
From wget -qO- http://instance-data/latest/meta-data/public-ipv4 (see https://stackoverflow.com/a/7536318/368896) I have confirmed the IP address that I must access from my remote machine, and I am using this.
I have restarted both iptables (sudo service iptables restart) and mysql (sudo service mysqld restart). In fact, at one point I restarted the server.
The password set for user dan in MySQL is the same for both the 'localhost' and the '24.91.66.190' entries.
I obtained the IP address from which I am trying to access the server (24.91.66.190) via the simple URL http://whatismyip.com, which I typed into Chrome running on my client Windows machine.
I am attempting to access the remote instance from a Windows machine using the program SQLYog.
Of note: When I SSH into this EC2 instance, I use
ssh -i myprivatekey.pem ec2-user@x.x.x.x (i.e, I use a public/private key login, with no password - I'm not sure if this is relevant). I use a Windows program called 'SecureCRT' to log on to my SSH session - I don't use PuTTy.
When I attempt to access the MySQL database from my Windows machine at the indicated IP (24.91.66.190), with username dan and the password set up for user dan at that IP on MySQL on the remote server, the connection simply times out.
Having investigated both some documentation for both iptables and mysql, as well as reviewing dozens of forum postings on the topic, but without finding any possible clues that I have not already tried, I am now posting this question on ServerFault.
Given that I have undertaken all of the above steps, and still cannot access the MySQL instance remotely, what are suggestions for following steps I might take to track down the problem?
One last detail: In the iptables listing, above, I had changed from INPUT DROP [3;120] to :INPUT ACCEPT [0;0] - in case that made a difference (it did not). I plan to change it back as soon as this problem is resolved.
