connect to other e2c instance running mysql

Question

I'm trying to set up a connection from one e2c (amazon) instance towards another e2c instance.

I've:

1. Adapted the security group as follows: MYSQL/Aurora TCP 3306 client-ip/32 (with client-ip the literal ip address of my client instance
2. bind-address is set to 0.0.0.0 in my.cnf (that already took me some effort to find out)
3. iptables isn't running (or is at least not holding back anything)
4. I've rebooted the server after changing the security group
5. see list below for content of my user database
6. mysqld is restarted
7. after each grant I did flush privileges (on the server)

This command does work:

root@server:~# mysql -u root -p

this doesn't:

ubuntu@client:~$ mysql -u root -p -h server-ip mysql (with the literal ip address)

with following exception:

ERROR 1045 (28000): Access denied for user 'root'@'ec2-client-ip.eu-west-1.compute.amazonaws.com' (using password: YES)

output of:

select Host, User from user;

+---------------------------------------------------+------------------+
| Host                                              | User             |
+---------------------------------------------------+------------------+
| %                                                 | name             |
| 127.0.0.1                                         | root             |
| client-ip.ec2.internal                            | root             |
| client-ip                                         | phpmyadmin       |
| client-ip                                         | root             |
| ::1                                               | root             |
| ec2-client-ip.eu-west-1.compute.amazonaws.com     | phpmyadmin       |
| ec2-client-ip.eu-west-1.compute.amazonaws.com     | root             |
| ip-172-30-0-191                                   | root             |
| localhost                                         | debian-sys-maint |
| localhost                                         | liftoff          |
| localhost                                         | phpmyadmin       |
| localhost                                         | root             |
+---------------------------------------------------+------------------+

Don't worry, this isn't production (at all) just some playing around...

I really don't know what I'm overlooking here, maybe it's just something stupid, I have no idea why this isn't working.

telnet server-ip 3306 

gives me the gibberish you would expect from a mysql server;

any help is greatly appreciated...

both instances are ubuntu, one Ubuntu 14.04.5 LTS (with mysql server 5.6) the client is Ubuntu 16.04.1 LTS with mysql client 5.7; I really hope this is not the problem...

Answer 1

Ok, apparently if you use:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'52-211-52-226.ec2.internal' WITH GRANT OPTION;

without the IDENTIFIED BY clause it doesn't set a password:

+------------------+-------------------------------------------+
| User             | Password                                  |
+------------------+-------------------------------------------+
| root             | *52906BB5BDD20058CC5D0540DF250D4CC034166F |
| root             | *52906BB5BDD20058CC5D0540DF250D4CC034166F |
| root             | *52906BB5BDD20058CC5D0540DF250D4CC034166F |
| root             | *52906BB5BDD20058CC5D0540DF250D4CC034166F |
| debian-sys-maint | *962FC5E27FD78F93284B9F0A4CE344B9748CEEFD |
| phpmyadmin       | *52906BB5BDD20058CC5D0540DF250D4CC034166F |
| liftoff          | *27EF2C2B8282406BA7B56DC96E8BE27C9988FBBB |
| phpmyadmin       |                                           |
| root             |                                           |
| phpmyadmin       |                                           |
| root             |                                           |
| name             | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             |                                           |
| root             |                                           |
+------------------+-------------------------------------------+    

...

mysql -u root -h 52.209.36.229 -p

didn't work but

mysql -u root -h 52.209.36.229

did...

sorry to have wasted your time :-/

S.

Answer 2

Looks like you need to flush privilges

mysql> flush privileges;

That is to say without knowing to what database you granted privileges. In the command you wrote you connect default to mysql database.

Usually you do

mysql> grant all privileges on my_db.* to 'user'@'host' deintified by 'secret';
mysql> /* for older versions */ flush privileges
shell$ mysql -u user -p -h mysql_host my_db